How can enable and disable menuitems based on the type of sheet.

[shishi]

Hi,

First of all I thank Tom, Bernie and Norman for responding to my
posts in this group. Your responses helped me to wrap up the small
project that I am doing in excel. As a last task I am now doing a menu
for this project.I have created the menu and added it to the excel
file. The menu appears right before the help menu. The menu gets added
to the workbook when we open it. There are three sheets in this
workbook. But all the menuitems will not operate on all the three
sheets. Some work on first, some work on second, some work on third.
How can I disable the menuitems based on the sheet name. Thanks in
advance for all the help.

Shishi

 

[Rowan]

Use the sheet activate / deactivate events. This example disables the
Data>Sort command. This is worksheet code: right click the sheet tab, select
view code and paste the code. Do this for each of the three sheets changing
the menu and control names so that the relevant menu items are disabled for
each sheet.

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

 

[shishi]

Hi,

Thank you Rowan for the reply. It works. great...However I have
couple of complications here.

1. Right now I have the Makemenu() procedure as below.
Private Sub Workbook_Open()
Call MakeMenu
End Sub
So it makes the menu whenever I open the workbook. The workbook opens
with last open sheet active. I have six items in the menu. Two of them
should be deactivated in Sheet2 and one should be deactivated in
Sheet1. But at first when I open the workbook regardless of which
sheet I am on, I see that all the menu items are activated. After that,
if I click on the Sheet1 or Sheet2 the menu show up with itmes
activated or deactivated appropriately. So I guess I need to force the
workbook to open with one sheet active at all times. Is there a way I
can do that?

2. The second complication is as below. The workbook has three sheets.
We have taken care of the Sheet1 and Sheet2. But the third sheet will
be generated by collecting tables from the Sheet2. Now in Sheet3 I
should be able to have the menu appear. Right now it appears. Also it
appears with menu settings for the Sheet1 or Sheet2, depending on the
sheet that we were on before coming to the Sheet3.

Right now I have a Sub with name GenerateCodeSheet() as a module
that generates the Sheet3. I have created the Sub like
Worksheet_Activate() as part of the same module. I call
Worksheet_Activate() at the end of the GenerateCodeSheet(). But this
way I can have the Worksheet_Activate() run only when I create it. But
after that if I click on Sheet1 or Sheet2 and then come back to Sheet3,
the menu settings will be of either Sheet1 or Sheet2 respectively. I
guess I need to have the GenerateCodeSheet() modified in such a way
that it will write the Worksheet_Activate() code to the Sheet3 window.
Any suggestions or solutions.

Thanks in advance for everyone who help me.

Shishi

 

[shishi]

Hi all,

I have solved the first problem by changing the code as below.

Private Sub Workbook_Open()
Call MakeMenu
Sheets("Sheet1").Activate
End Sub

But still trying to figure out a solution for the second problem.

Thanks,
shishi

 

[Rowan]

See Chip Pearson's notes on coding to the Visual Basic Editor. This should
allow you to add the worksheet activate/deactivate events to the newly
created sheet.

Make sure you set a reference to Microsoft Visual Basic for Applications
Extensibility (paragraph 5) and check your VB security (paragraph 7).

There is a section title "Creating an Event Procedure" which is what you
want to do.

www.cpearson.com/excel/vbe.htm

Regards
Rowan

 

[shishi]

Rowan,

Thank you very much for your reply. I truly appreciate your help.

shishi