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

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