Select pull down menu
 

I have a workbook that has something like 20 sheets so i've created
pull down menu from the menu bar that will display and hide sheet
according to how i've grouped them. Displaying the sheets works fine
The thing that I don't know how to do is to hide the sheets. I woul
like the user to be able to view multiple groups of sheets at a time
Ideally, I would like my pull down menu to be like the one under Vie
where there is a checkbox for Formula Bar and Status Bar. below is m
code for the pull down menu

With newMenu
.Caption = "Bars2004"
Set ctrlButton = .Controls.Add(Type:=msoControlButton
ID:=1)
With ctrlButton
.Caption = "bla"
.Style = msoButtonCaption
.OnAction = "viewDS"
End With
Set ctrlButton = .Controls.Add(Type:=msoControlButton
ID:=1)
With ctrlButton
.Caption = "blabla"
.Style = msoButtonCaption
.OnAction = "viewEG"
End With
Set ctrlButton = .Controls.Add(Type:=msoControlButton
ID:=1)
With ctrlButton
.Caption = "blablabla"
.Style = msoButtonCaption
.OnAction = "viewAK"
End With
End Wit

--
Message posted from http://www.ExcelForum.com

Select pull down menu
 

Sub myMenu
Dim oCB As CommandBar
Dim oCtl As CommandBarControl
Dim newMenu As CommandBarControl
Dim ctrlButton As CommandBarControl

On Error Resume Next
Application.CommandBars("Worksheet Menu
Bar").Controls("Tools").Controls("Bars2004").Delet e
On Error GoTo 0

Set oCB = Application.CommandBars("Worksheet Menu Bar")
Set oCtl = oCB.Controls("Tools")

Set newMenu = oCtl.Controls.Add(Type:=msoControlPopup, temporary:=True)
For Each sh In ActiveWorkbook.Worksheets
With newMenu
.Caption = "Bars2004"
Set ctrlButton = .Controls.Add(Type:=msoControlButton, ID:=1)
With ctrlButton
.Caption = sh.Name
.Style = msoButtonCaption
.OnAction = "ViewHideWs"
End With
End With
Next sh

End Sub

Private Sub ViewHideWs()

With Application.CommandBars.ActionControl
If .State = msoButtonUp Then
ActiveWorkbook.Worksheets(.Caption).Visible = xlSheetHidden
.State = msoButtonDown
Else
ActiveWorkbook.Worksheets(.Caption).Visible = xlSheetVisible
.State = msoButtonUp
End If
End With

End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"majikman " wrote in message
...
I have a workbook that has something like 20 sheets so i've created a
pull down menu from the menu bar that will display and hide sheets
according to how i've grouped them. Displaying the sheets works fine.
The thing that I don't know how to do is to hide the sheets. I would
like the user to be able to view multiple groups of sheets at a time.
Ideally, I would like my pull down menu to be like the one under View
where there is a checkbox for Formula Bar and Status Bar. below is my
code for the pull down menu

With newMenu
Caption = "Bars2004"
Set ctrlButton = .Controls.Add(Type:=msoControlButton,
ID:=1)
With ctrlButton
Caption = "bla"
Style = msoButtonCaption
OnAction = "viewDS"
End With
Set ctrlButton = .Controls.Add(Type:=msoControlButton,
ID:=1)
With ctrlButton
Caption = "blabla"
Style = msoButtonCaption
OnAction = "viewEG"
End With
Set ctrlButton = .Controls.Add(Type:=msoControlButton,
ID:=1)
With ctrlButton
Caption = "blablabla"
Style = msoButtonCaption
OnAction = "viewAK"
End With
End With


---
Message posted from http://www.ExcelForum.com/

Select pull down menu
 

I have a workbook that has something like 20 sheets so i've created a
pull down menu from the menu bar that will display and hide sheets
according to how i've grouped them. Displaying the sheets works fine.
The thing that I don't know how to do is to hide the sheets. I would
like the user to be able to view multiple groups of sheets at a time.
Ideally, I would like my pull down menu to be like the one under View
where there is a checkbox for Formula Bar and Status Bar. below is my
code for the pull down menu

If I understood your question correctly then try this:

To show groups of sheets you can use the Custom Views.
Unhide all sheets of a group and hide the others.
Then create in the menu View | Custom Views a new view.
Do the same for the other groups.

Write the names of the Custom Views beside the Tag property of the
respective ControlButtons. Only one macro is needed for all Custom Views.

Sub MyViewMenu()
Dim newMenu As CommandBarPopup

On Error Resume Next
Application.CommandBars(1).Controls("Bars2004").De lete
On Error GoTo 0
Set newMenu = Application.CommandBars(1).Controls.Add( _
Type:=msoControlPopup, Temporary:=True)
With newMenu
.Caption = "Bars2004"
With .Controls.Add
.Caption = "bla_DS"
.OnAction = "MyCustomViews"
.Tag = "DS" ' name of the Custom View
End With
With .Controls.Add
.Caption = "blabla_EG"
.OnAction = "MyCustomViews"
.Tag = "EG"
End With
With .Controls.Add
.Caption = "blablabla_AK"
.OnAction = "MyCustomViews"
.Tag = "AK"
End With
End With
End Sub

Sub MyCustomViews()
ActiveWorkbook.CustomViews(Application.CommandBars .ActionControl.Tag).Show
With Application.CommandBars(1).Controls("Bars2004")
.Controls(1).State = msoButtonUp
.Controls(2).State = msoButtonUp
.Controls(3).State = msoButtonUp
.Controls(Application.CommandBars.ActionControl.Ca ption) _
.State = msoButtonDown
End With
End Sub

--
Regards

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)