Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to select cells without a certain value and select a menu it | Excel Worksheet Functions | |||
use pull down menu to select a picture | New Users to Excel | |||
Pull Down Menu Bar | Excel Discussion (Misc queries) | |||
use pull down menu to select a picture | New Users to Excel | |||
pull down menu | Excel Programming |