How to create a comboboxhandler

[Gulli]

Hi,
in MsExcel Help I found a description to create a ComboBoxHandler (
searching for "change event". But code is not functional. ComboBoxHandler is
an unknown Type :

Private ctlComboBoxHandler As New ComboBoxHandler
Sub AddComboBox()

Set HostApp = Application

Dim newBar As Office.CommandBar
Set newBar = HostApp.CommandBars.Add(Name:="Test CommandBar",
Temporary:=True)
Dim newCombo As Office.CommandBarComboBox
Set newCombo = newBar.Controls.Add(msoControlComboBox)
With newCombo
.AddItem "First Class", 1
.AddItem "Business Class", 2
.AddItem "Coach Class", 3
.AddItem "Standby", 4
.DropDownLines = 5
.DropDownWidth = 75
.ListHeaderCount = 0
End With
ctlComboBoxHandler.SyncBox newCombo
newBar.Visible = True


End Sub

Does anybody know, which library is containing that control ?

Thanks in advance
Gulli

 

[Haldun Alay]

Hi,

I think you copied the code from microsoft's site http://msdn.microsoft.com/library/d...en-us/vbaof11/html/ofevtChange_HV05250825.asp

You need to create a class module named ComboBoxHandler and copy the following code to that class module.

Private WithEvents ComboBoxEvent As Office.CommandBarComboBox
Public Sub SyncBox(box As Office.CommandBarComboBox)
Set ComboBoxEvent = box
If Not box Is Nothing Then
MsgBox "Synced " & box.Caption & " ComboBox events."
End If

End Sub

Private Sub Class_Terminate()
Set ComboBoxEvent = Nothing
End Sub

Private Sub ComboBoxEvent_Change(ByVal Ctrl As Office.CommandBarComboBox)
Dim stComboText As String

stComboText = Ctrl.Text

Select Case stComboText
Case "First Class"
FirstClass
Case "Business Class"
BusinessClass
Case "Coach Class"
CoachClass
Case "Standby"
Standby
End Select

End Sub
Private Sub FirstClass()
MsgBox "You selected First Class reservations"
End Sub
Private Sub BusinessClass()
MsgBox "You selected Business Class reservations"
End Sub
Private Sub CoachClass()
MsgBox "You selected Coach Class reservations"
End Sub
Private Sub Standby()
MsgBox "You chose to fly standby"
End Sub

and following code to a module

Private ctlComboBoxHandler As New ComboBoxHandler
Sub AddComboBox()

Set HostApp = Application

Dim newBar As Office.CommandBar
Set newBar = HostApp.CommandBars.Add(Name:="Test CommandBar", Temporary:=True)
Dim newCombo As Office.CommandBarComboBox
Set newCombo = newBar.Controls.Add(msoControlComboBox)
With newCombo
.AddItem "First Class", 1
.AddItem "Business Class", 2
.AddItem "Coach Class", 3
.AddItem "Standby", 4
.DropDownLines = 5
.DropDownWidth = 75
.ListHeaderCount = 0
End With
ctlComboBoxHandler.SyncBox newCombo
newBar.Visible = True


End Sub



--
Haldun Alay
"Gulli" <[email protected]>, haber iletisinde ÅŸunları yazdı:[email protected]...
Hi,
in MsExcel Help I found a description to create a ComboBoxHandler (
searching for "change event". But code is not functional. ComboBoxHandler is
an unknown Type :

Private ctlComboBoxHandler As New ComboBoxHandler
Sub AddComboBox()

Set HostApp = Application

Dim newBar As Office.CommandBar
Set newBar = HostApp.CommandBars.Add(Name:="Test CommandBar",
Temporary:=True)
Dim newCombo As Office.CommandBarComboBox
Set newCombo = newBar.Controls.Add(msoControlComboBox)
With newCombo
.AddItem "First Class", 1
.AddItem "Business Class", 2
.AddItem "Coach Class", 3
.AddItem "Standby", 4
.DropDownLines = 5
.DropDownWidth = 75
.ListHeaderCount = 0
End With
ctlComboBoxHandler.SyncBox newCombo
newBar.Visible = True


End Sub

Does anybody know, which library is containing that control ?

Thanks in advance
Gulli

 

[Gulli]

Hi Huldun,

many thanks for quick response. I didn't recognize that class modules are to
be named as their class they provide. I cannot find any way to rename the
automatically created class module "Klasse1". Do you have any advice, how to
rename it ?

thanks in advance

Gulli

 

[Haldun Alay]

Hi,

In VBA Editor,press CTRL-R to activate Project Explorer and locate the class module Klasse1 then press F4. Properties window will be activated. You can change the name class module from that window.

--
Haldun Alay
"Gulli" <[email protected]>, haber iletisinde ÅŸunları yazdı:[email protected]...
Hi Huldun,

many thanks for quick response. I didn't recognize that class modules are to
be named as their class they provide. I cannot find any way to rename the
automatically created class module "Klasse1". Do you have any advice, how to
rename it ?

thanks in advance

Gulli