Running a VB module on new spreadsheets

Posted by Dave S on January 24, 2002 10:14 AM

I just wrote a module I need to use on a certain spreadsheet that is sent to me every month. The person that sends me the spreadsheets will not have access to the module and the spreadsheets always have different names. What is the best way to run the module from the spreadsheet (I've just been importing the module into the spreadsheet and hitting play, but the people that need to use it are not very technical and would be freaked out by this).

Thanks!

Dave

Posted by Russell Hauf on January 24, 2002 11:35 AM

I would give the "freaked out" people access to your workbook that you have your module in. Can they open 2 workbooks at once? If so, have them open both workbooks, change to the workbook that needs to be changed (have the macro run on), and run the code. For example,


Sub RunOnAnotherBook() 

If ActiveWorkbook.Name = "WorkbookMyCodeIsIn.xls" Then 
        MsgBox "The Workbook that needs to be changed must " & _ 
                "be the active workbook", , "WRONG WORKBOOK IS ACTIVE" 
Exit Sub 
Else 
' CALL YOUR MACRO HERE
End If 

End Sub

This is just one of many ways this could be done.

Hope this helps,

Russell

Posted by Dave S on January 24, 2002 11:50 AM

Thanks Russell--

I like that idea. It would be absolutely perfect if there was a box that popped up that allowed the user to choose which active workbook to run the macro on (in case they have several workbooks open). I guess I could pop up a form with a drop down list showing active workbooks...

If ActiveWorkbook.Name = "WorkbookMyCodeIsIn.xls" Then MsgBox "The Workbook that needs to be changed must " & _ "be the active workbook", , "WRONG WORKBOOK IS ACTIVE" Exit Sub Else ' CALL YOUR MACRO HERE End If