Excel Menu help!

[lehainam]

Dear all,

I created a Menu and many submenues on that Menu. However, some
submenues are only for specified worksheets. It means different
submenues are used for different worksheets but all all submenues are
on a Menu in Excel.

Could any one help me to hide or unhide some submenues when user select
a certain worksheet.

Thank you very much

Nam

 

[Guest]

Rather than hiding and unhiding you can just disable the menu items which are
not appropriate for a particular sheet. They will appear greyed out on the
menu. Do this with worksheet event code by pasting the following on the
sheet's code page. This example disables the Data>Sort command.

Private Sub Worksheet_Activate()
CommandBars(1).Controls("Data").Controls("Sort...").Enabled = False
End Sub


Private Sub Worksheet_Deactivate()
CommandBars(1).Controls("Data").Controls("Sort...").Enabled = True
End Sub

Hope this helps
Rowan

 

[lehainam]

Dear Rowan,

Thanks for your advice. However it does not work on my Menu. It onl
works on standard Menu of Excel.

For example: If the index of my menu is 13 and i want to disable 1s
sub menu of my menu, it does not work

Commandbars(1).controls(13).controls(1).disabled=false

But for Excel Menu it works,

Commandbars(1).controls(1.controls(1).disabled=false

Could you help me!

Thanks,

Na

 

[Bob Phillips]

Use names not index, it is more resilient as index could change, and there
is no Disabled property, just Enabled.

Commandbars("Worksheet Menu
Bar").controls("myMenu").controls("mySubMenu1!).Enabled= True

and

Commandbars("Worksheet Menu
Bar").controls("myMenu").controls("mySubMenu1!).Enabled= False