specify workbook in VBA

[depcdivr]

I am running into a minor inconvience and I am sure that there is a simple fix to is.

I am running a userform from a workbook that will most likely be left open for extended periods of time. If the userform is left open and the user switches to a different workbook the form errors because it may be looking for a sheet that does not exits.

I use the following code to define the sheet but how can I specify workbook that it is to look in?

Set ws = Worksheets("Training Data")

 

[taurean]

The userform is modal. Is there any reason that it should be modal? The non-modal userform will not let you access excel while you are working on it.

 

[depcdivr]

I set the userform to modal because otherwise people will not use it. THey like to be able to switch back and forth between programs at will. If I prevent them from doing this the program will not get used.

 

[taurean]

If the name of the workbook where this userform is located is fixed then

Set ws = Workbooks("[I]MyMacroBK[/I]").Worksheets("Training Data")

or if the name is something that people change to suit then use some variable like:

Dim sName as String
sName = ThisWorkBook.Name
Set ws = Workbooks(sName).Worksheets("Training Data")