Enabling/Disabling/Hiding/Unhiding Newly Created Menus and Submenu

[Type of Sheet displayed]

I have asked this question somewhat less detailed than before but did not
successfully get a working answer. The problem is basic. I've created a
menu on the menu bar (My Menu) that is specific to the application I am
creating. Thus:

.... code above this and variable declarations. Loop through to setup the
menu on both the worksheet and chart sheets.

For MenuLoopCounter = 1 To 2


'----------------------------------------------------------------------------------------------------------------
' Add the MyMenu to the Menu Bar
Set MyMenu = CommandBars(MenuLoopCounter).Controls.Add _
(Type:=msoControlPopup, Temporary:=True)

'---------------------------------------------------------------
' Add the MyMenu title (a caption) to the Menu Bar
MyMenu.Caption = "MyMenu"


'-------------------------------------------------------------------------
' Add MyMenu List Item for the Estimate Worksheet
Set MyMenuItem = MyMenu.Controls.Add(Type:=msoControlButton)
With MyMenuItem
.Caption = "Estimate"
.OnAction = "Macro1"
End With

.... more code here.


' .....................................................
' Add MyMenu List Item
' .....................................................
Set MyMenuItem = MyMenu.Controls.Add(Type:=msoControlButton)
With MyMenuItem
.Caption = "Submenu 1"
.OnAction = "Submenu1macro"
.Tag = "Sub1"
.Enabled = False
End With

.... even more code.

Now, I want to enable the Submenu 1 just when Sheet 4 is active and not any
other time. Or better yet, just show this submenu item when Sheet 4 is
active. Any help would be most appreciated. I am sure it is my not fully
qualifying the object.

Sincerely,

Bryan

 

[Die_Another_Day]

Use the "SheetActivate" Event under the "ThisWorkbook" Object of your
file.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Sheet4" Then
'Code to make menu Visible
Else
'Code to make menu hidden
End If

Charles

End Sub

 

[acampbell012]

Bryan,

I responded to your other thread. I think I understand what you are
looking for. Here is some code from one of my workbooks:

In the ThisWorkbook module, this removes submenu item ("Send Update")
when the worksheet is deactivated:


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
With Sheets("Time_Entry")
Application.CommandBars("Worksheet Menu Bar").Controls("Tasks"). _
Controls("Send Update").Visible = False
End With
End Sub

In the Sheet module, this makes it visible when the sheet is
reactivated:

Private Sub Worksheet_Activate()
With Sheets("Time_Entry")
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Tasks"). _
Controls("Send Update").Visible = True
End With
End Sub

Alan

 

[Type of Sheet displayed]

Alan,

I had to make some modifications to the idea but the code works. I found
that putting the code into "ThisWorkbook" caused issues on opening the
workbook. The calls to Private Sub Workbook_SheetDeactivate(ByVal Sh As
Object) caused an error. It appears Excel runs all of the code that is true
before executing code in other modules. So, before the code to put the menu
and submenus is called to build it Excel was attempting to ruing
Workbook_SheetDeactivate because the sheet is actually deactivated on startup
and only a "Title" sheet is active. So, "boom" went the code. However, I
did put a sub-piece of your idea into the code that I use to activate and
deactivate the sheets (work and chart). I'm building a Dictator type
application for estimating software development efforts. So, your help this
time around was great!!!!

Many thanks,

Bryan