Re: combobox prob
06.12.2002 18:07:29
leo
Public Sub ComboMenueBox() Dim oBar As CommandBar
Dim oCbo As CommandBarComboBox
Set oBar = Application.CommandBars("MyCommandBar")
On Error Resume Next
oBar.Controls("Dienstpläne").Delete
On Error GoTo 0
With oCbo
Dim i As Integer
Dim wks As Worksheet
Call ComboMenueBox_loeschen
Set oCbo = Application.CommandBars("MyCommandBar") _
.Controls.Add(msoControlComboBox)
With oCbo
.Caption = "Dienstpläne"
.Width = 100
For Each wks In Worksheets
If wks.Name <> "Dienstplan" Then oCbo.AddItem wks.Name
.OnAction = "BlattAuswahl"
Next wks
.text = "Wählen..."
End With
End With
End Sub
_____
Public Sub BlattAuswahl()
On Error GoTo fehler
Sheets(Application.CommandBars("MyCommandBar") _
.Controls("Dienstpläne").text).Select
fehler:
If Sheets(Application.CommandBars("MyCommandBar") _
.Controls("Dienstpläne").text) = ActiveSheet.Name Then
Exit Sub
End If
MsgBox ("Dienstplan nicht auffindbar !")
Call MenuErstellen
End Sub