Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error with combobox control on standard toolbar
i have code which creates a combobox control on standard toolbar on
microsoft excel. i have a bug in this code ... dono where i have gone wrong ..... hope anyone can help me with this ... Option Explicit Sub AddComboNavigation() Dim cBar As CommandBar Dim c As CommandBarComboBox Dim i As Integer ‘ Set reference to standard toolbar Set cBar = Application.CommandBars(“standard”) cBar.Reset ‘Add Combobox Control Set c = cBar.Controls.Add(msoControlComboBox, 1) With c .Clear For i = 1 To ThisWorkbook.Sheets.Count .AddItem ThisWorkbook.Sheets(i).Name, 1 Next i .Caption = “Sheet Navigator” .DescriptionText = “This is the area where you can place description area” .Enabled = True .Visible = True .DropDownLines = 5 .ListIndex = 0 .OnAction = “Activate_Sheet” End With End Sub Private Sub Activate_Sheet() ‘on error resume next Dim x As String Dim c As CommandBarComboBox Set c = Application.CommandBars(“standard”).Controls(“Shee t Navigator”) If c.ListIndex 0 Then Sheets(c.ListCount – c.ListIndex + 1).Activate End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error with combobox control on standard toolbar
See comments in code below Option Explicit Sub AddComboNavigation() Dim cBar As CommandBar Dim c As CommandBarComboBox Dim i As Integer ' Set reference to standard toolbar Set cBar = Application.CommandBars("standard") cBar.Reset 'Add Combobox Control 'added before Set c = cBar.Controls.Add(Type:=msoControlComboBox, ID:=1, befo=1) With c .Clear For i = 1 To ThisWorkbook.Sheets.Count 'changes 1 to i .AddItem ThisWorkbook.Sheets(i).Name, i Next i .Caption = "Sheet Navigator" .DescriptionText = "This is the area where you can place " & _ "Description area" .Enabled = True .Visible = True 'add line .DropDownLines = Sheets.Count .ListIndex = 0 'add line .DropDownWidth = 75 .OnAction = "Activate_Sheet" End With End Sub Private Sub Activate_Sheet() 'on error resume next Dim x As String Dim c As CommandBarComboBox Set c = Application.CommandBars("standard").Controls("Shee t Navigator") If c.ListIndex < 0 Then 'changed parameter to select sheet Sheets(c.ListIndex).Activate End If End Sub See comments in code below Option Explicit Sub AddComboNavigation() Dim cBar As CommandBar Dim c As CommandBarComboBox Dim i As Integer ' Set reference to standard toolbar Set cBar = Application.CommandBars("standard") cBar.Reset 'Add Combobox Control 'added before Set c = cBar.Controls.Add(Type:=msoControlComboBox, ID:=1, befo=1) With c .Clear For i = 1 To ThisWorkbook.Sheets.Count 'changes 1 to i .AddItem ThisWorkbook.Sheets(i).Name, i Next i .Caption = "Sheet Navigator" .DescriptionText = "This is the area where you can place " & _ "Description area" .Enabled = True .Visible = True 'add line .DropDownLines = Sheets.Count .ListIndex = 0 'add line .DropDownWidth = 75 .OnAction = "Activate_Sheet" End With End Sub Private Sub Activate_Sheet() 'on error resume next Dim x As String Dim c As CommandBarComboBox Set c = Application.CommandBars("standard").Controls("Shee t Navigator") If c.ListIndex < 0 Then 'changed parameter to select sheet Sheets(c.ListIndex).Activate End If End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=164699 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assign Error message to Standard toolbar icon | Excel Discussion (Misc queries) | |||
Standard Toolbar | Excel Discussion (Misc queries) | |||
Changes to standard toolbar | Excel Discussion (Misc queries) | |||
toolbar for "standard tickmarks" | Excel Worksheet Functions | |||
Hiding the Standard Toolbar | Excel Discussion (Misc queries) |