Macro needs to be global

[DJFudd]

I currently have a macro:

Please Login or Register  to view this content.

that I need to make global, currently it only works on the sheet that it was created in. I'm hoping it's just a matter of tweaking the existing code.

I am a long time user of Excxel but have just recently branched out to try more advanced options and this one has me stumped.

Thanks

[WasWodge]

Can you explain a bit more as your code isn't specific to one sheet?
Have you put it in the Worksheet module rather than a standard module?

[DJFudd]

I have a spreadsheet with about 80 tabs (or what I called sheets). I created a macro on one of tjose tabs. If I look at it on Developer/Macro tab I see that it is called sheet7.HideRow. I can only use the macro on sheet7. I need to be able to use the macro on many different sheets (aprox. 40) not just sheet7.

[DJFudd]

WasWodge...

I am not sure what you mean by Worksheet module as opposed to standard module. But to further illustrate, I have a second macro that shows in the Macro project window as Module1, not attached to any particular sheet.

[DJFudd]

WasWodge...



Thanks. Sometimes one just has to ask a question and to get sent you on a different tangent. I think I was making it too difficult.

I ended up copying the source out of my existing macro, creating a new blank macro, and pasting the copied code into the new macro. Although this won't teach may any new coding techniques, it worked. So now my macro looks the same but it is shows in the project windo as Module2.

I would still be interested in knowing how to actually change the original problem if you care to answer.

[WasWodge]

I would still be interested in knowing how to actually change the original problem if you care to answer.

The original question was how to make the Macro "Global". As I stated in my first post the macro you posted wasn't sheet specific, in other words it was already global by your definition.
The problem was by the sound of it that you had it in a worksheet module rather than a standard (regular) module which is where it should be (Module2 is a regular module).

I'll try and explain the difference.
There are 3 main modules you place code in (I wont't go into class or userform modules).

The 3 modules are

Regular (standard) module
As a default most code you should put in a standard module.

One way you can put code in a regular module is as follows

Open the workbook in which you want to add the code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing either write the code or if you have copied code choose Edit | Paste (or right click paste)

This code normally starts with a line similar to

Please Login or Register  to view this content.

The 2 big exceptions to where you place code are

Worksheet modules
This module is for holding code that is specifically for triggering a code to run by carrying out actions to the sheet i.e. changing the sheet, changing cells, double-clicking the cells e.t.c. and you can tell it because it normally starts something like

Please Login or Register  to view this content.

rather than just

Please Login or Register  to view this content.

You can enter this code by right clicking the Sheet tab, then selecting View Code and pasting it in the window that appears.
In general this code only applies to the sheet that holds the macro.

Workbook modules
This holds workbook level code. This code is similar to the sheet code but instead of triggering the code at a Sheet change it is used when you need the code to run after an action on the entire workbook.
The most common times you will see it used is when you want the macro to run when the Workbook is opened i.e. something like

Please Login or Register  to view this content.

or when you close the Workbook i.e.

Please Login or Register  to view this content.

You enter WorkBook code by going into the VB Editor finding in the project window the workbook you want then right clicking ThisWorkBook, next select View Code and paste your code in the window that appears.

As a general rule you use a regular module by default unless you have a specific need for using one of the other modules.
You can change the name of a regular module if you desire and you can put more than one macro in a regular module (that is personal choice of how you prefer to organize your code)

I hope I have explained it clear enough (and hopefully in a way that I won't be shouted at by other members )
Just in case I haven't take a look at the link below which might make it clearer.
http://www.excelguru.ca/content.php?...Place-VBA-Code

[DJFudd]

WasWodge...

TTU. That certainly clarifies it for me. My original script did not have a sub command at all, so I don't know how it worked, but I guess that is why it was attached at the shhet level.

Thanks for your help, not sure how to mark as solved but it is in my estimation.

[WasWodge]

ark a thread as solved go to your first post then you will see thread tools. It is in a drop down there