-
commandbar combobox
hello
i wanted to add to my standard toolbar a combo box containing a list of all sheets in the activeworkbook.
[VBA]
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
[/VBA]
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
moshe
-
this is my new version
[VBA]
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
[/VBA]
i want to get to the sheets just by one click on the combobox.
can some help?
thanks
moshe
-
anothe version:
[VBA]
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
[/VBA]
how do i let the combobox size autofit the text.how do i make the combobox activate a sheet i select?
thanks
moshe
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules