Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a comboboxhandler
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a comboboxhandler
Hi,
I think you copied the code from microsoft's site http://msdn.microsoft.com/library/de...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" , haber iletisinde şunları ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a comboboxhandler
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" wrote: Hi, I think you copied the code from microsoft's site http://msdn.microsoft.com/library/de...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" , haber iletisinde şunları ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a comboboxhandler
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" , haber iletisinde şunları ... 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" wrote: Hi, I think you copied the code from microsoft's site http://msdn.microsoft.com/library/de...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" , haber iletisinde şunları ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a macro to create excel line graph with coloured pointers anddata lables | Charts and Charting in Excel | |||
How to create adress list so can mail merge and create labels? | Excel Discussion (Misc queries) | |||
How to create a form to insert a hyerlink.VBA code to create a for | Excel Discussion (Misc queries) | |||
Create dictionary of terms, create first time user site | New Users to Excel | |||
need to create a formula to create a timesheet but haven't a clue | Excel Discussion (Misc queries) |