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

[Rowan]

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" 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
>
>

[lehainam]

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=false

But for Excel Menu it works,

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

Could you help me!

Thanks,

Nam

[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

--
HTH

Bob Phillips

"lehainam" <[email protected]> wrote in
message news:[email protected]...
>
> 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=false
>
> But for Excel Menu it works,
>
> Commandbars(1).controls(1.controls(1).disabled=false
>
> 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
>