Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

So where exactly do I put this macro code then??

Excel How To

So where exactly do I put this macro code then??

by  KenWright  Posted    (Edited  )
If you've been given some code, but are unsure as to where to put it, then try this step by step guide, and it can be applied to almost any macro you see (Not all eg Event macros, but probably most):-

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane you need to search for your workbook's name, and when you find it you may need to click on the + to expand it. Within that you should see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook

If you have named your sheets then those names will appear in the brackets above as opposed to what you see at the moment in my note.

Right click on the where it says VBAProject(Your_Filename) and choose 'Insert Module' and it will now look like this

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook
Modules
Module1

Double click the Module1 bit and then paste in whatever code you have been given, starting with the Sub xyz() bit and finishing at the End Sub bit, eg:-
Code: 
Sub xyz()

    Dim ....blah blah blah
    Dim ....blah blah blah
        Code to do stuff

End Sub
Then hit File / Close and return to Microsoft Excel and save the file. Now just do Tools / Macro / Macros / xyz


If you then wanted to get rid of the macro for any reason, then do the following:-

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane you need to search for your workbook's name, and when you find it you may need to click on the + to
expand it. Within that you should see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
etc..........................
ThisWorkbook
Modules
Module1

Right click on the Module1 and select remove. When prompted with a question re exporting, just hit no. Then hit File / Close and return to Microsoft Excel and save the file.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top