Correct Workbook and Sheet Referencing

[Jellybean Cowboy]

Hi there,

I'm having problems with quite a complex workbook that I have created. I've designed a "font-end" based on forms and when the workbook opens the "welcome" form is visible but the workbook gets minimised so you can't see any of the rubbish contained within it.
This works fine when there are no other workbooks open but constantly falls over when there are. I am trying to make some changes to the code to allow you to use this "program" while other workbooks are open. I'm sure this is a problem with workbook/worksheet referencing but I cannot seem to fix things. Could anyone offer some guidance as to the code I should be using for ensuring selecting, hiding, maximising, and minimising of sheets is handled correctly under these conditions?

Many thanks in advance,

Jon.

 

[Jellybean Cowboy]

Err, ok, let me ask this question another way...

...can I ensure that Excel is always referencing a particular workbook when using code like this:

Unload fmReturn

ActiveWindow.WindowState = xlMinimized

ActiveWorkbook.Sheets("Welcome").Visible = True
ActiveWorkbook.Sheets("Welcome").Select

Application.ScreenUpdating = False

'Hides all worksheets
For Each Sht In Worksheets
sname = Sht.Name
If sname = "Welcome" Then
GoTo Skip:
Else
Worksheets(sname).Visible = False
End If
Skip:
Next Sht

As it stands currently, Excel tries to carry out these operations on the other workbook that is open. If I use "workbook("").activate" beforehand everything is fine. But I don't want to use this command because I will have to hard code it into many different parts of the workbook - making things difficult if I want to change the filename.

Any ideas?

 

[Glaswegian]

Welcome to the Board!

Are you running the code in the same workbook where your form is? If that's the case then use ThisWorkbook in place of ActiveWorkbook - i.e. the workbook where the current code is running. Or are you trying to run that code on another workbook?

Regards

 

[Jellybean Cowboy]

Thankyou for the welcome!



I didn't realise there was such a command.



I am trying to run the code from within the workbook that the form is. I'll give this a shot - sounds like it might sort out my problems...

Thankyou very much!

 

[Glaswegian]

You're welcome.

Post back if you have any problems.

 

[Jellybean Cowboy]

AHA! That's tickled the little beast into submission!

Beautiful.

Thanks again Glaswegian.