Macro to enable or visible on specific worksheet only

E

Edgar

Hi,
I have a macro on personal workbook and have it visible
on the toolbars.

I would like have it made enable and visible only when I
open a specific worksheet and not otherwise.

I am using excel 2002

pls advice
 
R

Rob van Gelder

You could have code which activates when a workbook opens.
The code would add the macro as a button to the toolbar.

A condition within the code would check to see if the workbook is a certain
name, which would determine whether to add to the toolbar.

I have toolbar button examples on my website.
 
B

Bob Phillips

In your personal.xls, in Workbook_Open , hide the toolbar

Application.Commandbars("Standard").Controls("myButton").Visible = False

and then in the Workbook_Activate event for you workbook, set it to true.

Tidy up with setting it to false in the workbook_Deactivate event, and i the
personal.xls Workbook_BeforeClose event.
 
E

Edgar

Hi,
I was not clear before i think.

I have a worksheet named "My data". There is only 1
worksheet in the xls file.
But the macro is in the Personal.xls file, which is
available to any other worksheet opened.

I want the macro to be visible only when this file(My
Data) is opened. And it should be disabled when this file
is closed.

this way I don't get to see the macro, when I am working
on other worksheets

thanks.
-----Original Message-----
In your personal.xls, in Workbook_Open , hide the toolbar

Application.Commandbars("Standard").Controls ("myButton").Visible = False

and then in the Workbook_Activate event for you workbook, set it to true.

Tidy up with setting it to false in the
Click to expand...
workbook_Deactivate event, and i the
 
Top