Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable custom menu item based on visible cells
I have a custom excel menu that is paired with a scheduling
worksheet. My worksheet has two sections: the actual schedule A1:O16 and the payroll information P1:T16. I have two menu items one that will show the payroll information and one that will hide it. I would like to have just one menu item that will change to Hide or Show depending on the visibility of the payroll section. Below is the code. Is it possible? ***Module 1*** Sub AddMenus() Dim cMenu1 As CommandBarControl Dim cbMainMenuBar As CommandBar Dim iHelpMenu As Integer Dim cbcCutomMenu As CommandBarControl Dim imenuindex As Integer On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("&Schedule Tools").Delete On Error GoTo 0 Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar") iHelpMenu = cbMainMenuBar.Controls("Help").Index Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Befo=iHelpMenu) cbcCutomMenu.Caption = "&Schedule Tools" With cbcCutomMenu.Controls.Add(Type:=msoControlButton) .Caption = "Show Detail" .OnAction = "ShowDetail" End With With cbcCutomMenu.Controls.Add(Type:=msoControlButton) .Caption = "Hide Detail" .OnAction = "HideDetail" End With ***Module 2*** Sub ShowDetail() Application.ScreenUpdating = False ActiveSheet.Unprotect Rows("47:48").Select Selection.EntireRow.Hidden = False Columns("P:R").Select Selection.EntireColumn.Hidden = False 'Fit Sheet to Screen Range("a:s").Select ActiveWindow.Zoom = True Range("a2").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Application.ScreenUpdating = False End Sub Sub HideDetail() Application.ScreenUpdating = False ActiveSheet.Unprotect Rows("47:48").Select Selection.EntireRow.Hidden = True Columns("P:R").Select Selection.EntireColumn.Hidden = True 'Fit Sheet to Screen Range("a:s").Select ActiveWindow.Zoom = True Range("a2").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Application.ScreenUpdating = False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable custom menu item based on visible cells
Something along the lines of... '-- Sub ShowAndHideDetails If ActionControl.Caption = "Hide" Then 'Code to hide the rows ActionControl.Caption = "Show" Else 'Code to show the rows ActionControl.Caption = "Hide" End If End Sub -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "stewart" wrote in message I have a custom excel menu that is paired with a scheduling worksheet. My worksheet has two sections: the actual schedule A1:O16 and the payroll information P1:T16. I have two menu items one that will show the payroll information and one that will hide it. I would like to have just one menu item that will change to Hide or Show depending on the visibility of the payroll section. Below is the code. Is it possible? ***Module 1*** Sub AddMenus() Dim cMenu1 As CommandBarControl Dim cbMainMenuBar As CommandBar Dim iHelpMenu As Integer Dim cbcCutomMenu As CommandBarControl Dim imenuindex As Integer On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("&Schedule Tools").Delete On Error GoTo 0 Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar") iHelpMenu = cbMainMenuBar.Controls("Help").Index Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Befo=iHelpMenu) cbcCutomMenu.Caption = "&Schedule Tools" With cbcCutomMenu.Controls.Add(Type:=msoControlButton) .Caption = "Show Detail" .OnAction = "ShowDetail" End With With cbcCutomMenu.Controls.Add(Type:=msoControlButton) .Caption = "Hide Detail" .OnAction = "HideDetail" End With ***Module 2*** Sub ShowDetail() Application.ScreenUpdating = False ActiveSheet.Unprotect Rows("47:48").Select Selection.EntireRow.Hidden = False Columns("P:R").Select Selection.EntireColumn.Hidden = False 'Fit Sheet to Screen Range("a:s").Select ActiveWindow.Zoom = True Range("a2").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Application.ScreenUpdating = False End Sub Sub HideDetail() Application.ScreenUpdating = False ActiveSheet.Unprotect Rows("47:48").Select Selection.EntireRow.Hidden = True Columns("P:R").Select Selection.EntireColumn.Hidden = True 'Fit Sheet to Screen Range("a:s").Select ActiveWindow.Zoom = True Range("a2").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Application.ScreenUpdating = False End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Sub Menu Item to Current Custom Menu | Excel Programming | |||
Disable protection menu item | Excel Programming | |||
How to disable the "Insert Copied Cells" context menu item | Excel Programming | |||
Disable Menu Item | Excel Programming | |||
Disable File-Save menu item in macro? | Excel Programming |