macros/vba code copy in other workbooks

[vra]

I made one sheet with buttons & vba code to hide rows & columns but
macros do not get copied with the sheet when i move the sheet to other workbooks .
What can we do ?

 

[dave3009]

How are you moving the sheet?

 

[vra]

excel 2007, i move sheet by right clk on sheet tab & move or copy into other workbook.
i saved the shhet in 97-2003 compatible mode coz then only macros work.
what do u mean by code tag??

 

[dave3009]

If the macros are stored in the spreadsheet module then they will copy over. If they are in a stand alone module and have been assigned to the controls then they will not copy over.

 

[vra]

how i shld know that macros are stored in spreadsheet module?
on button click only they operate.
but now what should i do , coz without buttons it would be difficult to work on the workbooks.// sheets

 

[dave3009]

Press Alt+F11, this will open the VBE. On the left you will find your project, if you click in the sheet modules (marked Sheet1 etc) you can see if the macros are in there.

If you ran the macro recorder and assigned the macros to a button then they will be in a standalone module (marked Module1 etc).

If you want the macros to go with the sheet then you will need to create Active X controls and write the macros in the subs for those controls which you would store in the sheet module.

 

[vra]

how to create activex controls? & are they safe? what is writing macros in subs for controls. ? Will my computer be safe if i do this all? I dont know much about all these what u wrote

 

[dave3009]

Your computer will be as safe using active x controls as it is assigning macros to form controls. The language is the same it's just where it's stored.

Clearly having your code would make this a little easier but lets take a simple example. On a button click I want to select range A1:C10, colour it blue. So if we power up the macro recorder we get something like this in a new module.

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1:C10").Select
    Selection.Interior.ColorIndex = 5
End Sub

Tidied up some it can look like this

Sub Macro1()
    Range("A1:C10").Interior.ColorIndex = 5
End Sub

Rather than create a form button and assign the macro to it we can create an active x button. For this you need access to the Developers tab (Office Button>>Excel Options>>Popular>>place a check in the 'show developer tab in Ribbon'). Now on the developer tab click insert>>active x>>Command Button. Now double click it to enter into the VB sheet module and you'll have a Private Sub defined for the button, so we just copy the content of the macro we created into that button code...like so

Private Sub CommandButton1_Click()
      Range("A1:C10").Interior.ColorIndex = 5
End Sub

Click the button and the action is performed. Copy out the sheet and the action will still be performed in the new workbook.

 

[vra]

THANKS ALOT . i SHALL TRY THIS & GET BACK TO U ..
THANKS ALOT