PDA

View Full Version : commandbar combobox



lior03
11-06-2008, 05:00 AM
hello

i wanted to add to my standard toolbar a combo box containing a list of all sheets in the activeworkbook.

Sub addcombo()
On Error Resume Next
Dim newitem As CommandBarControl
Set newitem = CommandBars("standard").Controls.Add(before:=6, Type:=3)
With newitem
End With
End Sub


how do i tell excel to loop througe the w.b to get the list.i want the list to present 3 result at the time.
thanks

lior03
11-06-2008, 05:38 AM
this is my new version


Sub comboxer()
Dim i As Integer
Dim newitem As CommandBarControl
Set newitem = CommandBars("standard").Controls.Add(Type:=3, before:=3)
With newitem
.Caption = "list of sheets"
.Width = 35
For i = 1 To Sheets.Count
.AddItem i
Sheets(i).Select
Next
End With
End Sub


i want to get to the sheets just by one click on the combobox.
can some help?
thanks

lior03
11-06-2008, 06:03 AM
anothe version:

On Error Resume Next
Dim i As Integer
Dim newitem As CommandBarControl
CommandBars("standard").Controls("list of sheets").Delete
Set newitem = CommandBars("standard").Controls.Add(Type:=3, before:=3)
With newitem
.Caption = "list of sheets"
.Width = 125
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
.AddItem (ws.Name)
ws.Activate
End If
Next
End With


how do i let the combobox size autofit the text.how do i make the combobox activate a sheet i select?
thanks