Vba q: changing vba code to use variables

Question

QUESTION: Looking for a bit of assistance modifying some old code which I typed up a while ago in a hurry. When referencing the file name I have used

Windows("very long and annoying filename.xls").Activate

This appears 60+ times in a number of modules. Obviously if I want to change the file name I must now change the 60+ lines of code to suit as well.

Is there a way I can change these line of code to use a variable for the file name, so that for future times I need just change the variable instead.

Here is what I have attempted from code found on various forums, however it doesn't run.

'Workbook level/Project level variable Public sheetName As Workbook 'Also tried As String but didn't work.

'in Sub Auto_Open() sheetName = "very long and annoying FILE name.xls"

'in a number of modules

   Windows(sheetName).Activate

Any information is much appreciated.

Phil

ANSWER: Phil,

Public sheetName as String

Sub Auto_OPen()

Dim bk as Workbook
sheetName = "MyWorkbook.xls")
set bk = Workbooks(sheetName)
msgbox "My workbook's name is " & sheetName & vbNewLine & _
 "it is located at " & bk.Path

End Sub

Now sheetname is an odd variable to use for a workbook name. Nonetheless, as long as you can do

Sub Tester1() Windows("MyWorkbook.xls").Activate End Sub

then

Public sheetName as String

Sub Tester()

sheetName = "MyWorkbook.xls" windows(sheetName).Activate

End Sub

should work.

Notice you can't do something like

sheetname = "C:\Myfolder\Myworkbooks.xls" windows(sheetname).Activate

windows only takes the workbook name as an argument - not including the path.

So it looks like you are headed in the right direction. The approach I have outlined should work and is a slight modification of what you show.

---------- FOLLOW-UP ----------

QUESTION: Worked perfectly great.

I do have another question, not sure if I need to post as a completely new question.

I have anworkbook which is used as a 'home base' and contains a userform with buttons which in turn open up other workbooks.

I am wondering if it is possible to have a macro or some sort of vba code that is executed when any openfile is closed that checks to see if this 'home base' file is open, and if it is not open, it opens it. I can imagine this is possible with a if statement which is run when workbook is closed, checking to see if the home base file is open and if false, it opens it up. However this may require putting the code in 40+ workbooks and wouldn't work when just a new default Book1 is opened/created. Is there some sort of excel-wide way of doing this? Or would it potentially require a 3rd party add on?

Any information would be much appreciated.

Phil

Answer

Phil,

what you describe would most closely align with application level events. Just as there is a workbook level beforeclose event, likewise there is an application level beforeclose event which fires whenever a workbook is being closed. In fact it will hold a variable reference to the workbook which is about to be closed. You can put code in this event to check if that is your workbook and set cancel = true to cancel the closing of the workbook.

You are probably aware that all code must be in a workbook. Therefore application level events are not predefined and therefore not a readily available as workbook or sheet level events. You have to write code that creates or instantiates these events for them to be active. This takes several steps and the method is detailed by Chip Pearson at his site (or see thevba help for Application level events).

http://www.cpearson.com/excel/AppEvent.aspx

is the link to Chip Pearson's instructions for instantiating these events. You can put such code in the personal.xls (.xlsm for2007 and later) that instantiate the events and hold the code you want executed for these events.

Hope that answers your question.

--