Error with combobox control on standard toolbar

[vicky]

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(“Sheet
Navigator”)
If c.ListIndex 0 Then
Sheets(c.ListCount – c.ListIndex + 1).Activate
End If
End Sub

 

[joel]

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, before:=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
Navigator")

If c.ListIndex <> 0 Then
'changed parameter to select sheet
Sheets(c.ListIndex).Activate
End If
End Su

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, before:=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
Navigator")

If c.ListIndex <> 0 Then
'changed parameter to select sheet
Sheets(c.ListIndex).Activate
End If
End Su