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 |
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 |
All times are GMT +1. The time now is 10:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com