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


--
lehainam
------------------------------------------------------------------------
lehainam's Profile: http://www.excelforum.com/member.php...o&userid=18615
View this thread: http://www.excelforum.com/showthread...hreadid=387052

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 DataSort 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" wrote:


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


--
lehainam
------------------------------------------------------------------------
lehainam's Profile: http://www.excelforum.com/member.php...o&userid=18615
View this thread: http://www.excelforum.com/showthread...hreadid=387052

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=f alse

But for Excel Menu it works,

Commandbars(1).controls(1.controls(1).disabled=fal se

Could you help me!

Thanks,

Na

--
lehaina
-----------------------------------------------------------------------
lehainam's Profile: http://www.excelforum.com/member.php...fo&userid=1861
View this thread: http://www.excelforum.com/showthread.php?threadid=38705

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!).En abled= True

and

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

--
HTH

Bob Phillips

"lehainam" wrote in
message ...

Dear Rowan,

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

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

Commandbars(1).controls(13).controls(1).disabled=f alse

But for Excel Menu it works,

Commandbars(1).controls(1.controls(1).disabled=fal se

Could you help me!

Thanks,

Nam


--
lehainam
------------------------------------------------------------------------
lehainam's Profile:
http://www.excelforum.com/member.php...o&userid=18615
View this thread: http://www.excelforum.com/showthread...hreadid=387052