Running a macro from the spreadsheet

[Roger Withnell]

I have built a macro in VB for a particular spreadsheet, opening VB from
within Excel. The macro is in the Forms folder and double clicking the
button (that starts the macro) on the form opens the code window.

I can now run the macro successfully within the VB window, using the Run
Sub/Userform button. How do I initiate this macro from the spreadsheet
Tools>Macro>Macros window? It is not listed there currently.

I would like to email this macro to my partner to use. Does it have to be
emailed with the spreadsheet in which it was built? I would rather it were
possible to run it with any designated spreadsheet.

Thanking you in anticipation.

 

[papou]

Hi Roger

Create a Sub in a standard module and then call your form from this sub.
eg:
Sub LaunchMyForm()
Load USerForm1
UserForm1.Show
End Sub

HTH
Cordially
Pascal

 

[Joel]

I macro that runs from a button is located on one of the VBA sheet windows.
code that runs from the Tool - Macro button are located in Modules. The
simpliest way to havve the code run from both envirnoments is to move the
code from the Sheet window to a module window. Add a new module to VBA and
copy the code from the sheet window to the modeule leaving the Sub statement
and End Sub statement. the add a call stement from the sheet macro to the
module macro


sheet window
Sub commandbutton_click()
call Commoncode()
end sub

module window
Sub Commoncode()
'insert the code that was originally in the sheet window
end sub

 

[Roger Withnell]

Thanks. Worked a treat.

The macro is associated with a specific spreadsheet. Is it possible to save
the macro in a separate file so that it can be used with any designated
spreadsheet.

Thanking you in anticipation.

 

[Joel]

You could put the macro into your personal.xls file. Do a search on your C:
dirve for the directory XLSTART (varies with different versions of offfice)
and create an Excel file called personal.xls. Add macro to this workbook.
then everytime you open excel this macro can be called.