Make macro available to all sheets
 

How do I make my macro run no matter what sheet I'm on in the workbook. I
don't want to put it in my personal.xls. Rather I just want it specific to
this one workbook, but able to run from any sheet. I tried naming it 'Public
Sub Add_New_Person' and placed it in the ThisWorkbook module but it won't run
when I'm in a sheet in the workbook.

Thanks.

Make macro available to all sheets
 

Put the code in a standard module. In the Visual Basic Editor (VBE)...
Insert (menu) | Module
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Bill_S"
wrote in message
How do I make my macro run no matter what sheet I'm on in the workbook. I
don't want to put it in my personal.xls. Rather I just want it specific to
this one workbook, but able to run from any sheet. I tried naming it 'Public
Sub Add_New_Person' and placed it in the ThisWorkbook module but it won't run
when I'm in a sheet in the workbook.
Thanks.

Make macro available to all sheets
 

Your code should be in a standard code module (the same place as a recorded
macro ends up). Your public definition should make it accessable for any
sheet. The only issue is that your code needs to reference the active sheet
at all times. Post your code so we can take a look at it...
--
HTH...

Jim Thomlinson


"Bill_S" wrote:

How do I make my macro run no matter what sheet I'm on in the workbook. I
don't want to put it in my personal.xls. Rather I just want it specific to
this one workbook, but able to run from any sheet. I tried naming it 'Public
Sub Add_New_Person' and placed it in the ThisWorkbook module but it won't run
when I'm in a sheet in the workbook.

Thanks.

Make macro available to all sheets
 

Maybe you could modify your macros so that they check where you are before
continuing:


Option Explicit
sub testme()
if activeworkbook.fullname < thisworkbook.fullname then
msgbox "Not available in this workbook!"
exit sub
End if

'real code here
End Sub

ThisWorkbook is the workbook that owns the code. Activeworkbook is the workbook
that's active <bg.




Bill_S wrote:

How do I make my macro run no matter what sheet I'm on in the workbook. I
don't want to put it in my personal.xls. Rather I just want it specific to
this one workbook, but able to run from any sheet. I tried naming it 'Public
Sub Add_New_Person' and placed it in the ThisWorkbook module but it won't run
when I'm in a sheet in the workbook.

Thanks.

--

Dave Peterson