Correct Workbook and Sheet Referencing

Jellybean Cowboy

New Member
Joined
May 27, 2004
Messages
10
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Jellybean Cowboy

New Member
Joined
May 27, 2004
Messages
10
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

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
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

New Member
Joined
May 27, 2004
Messages
10
Thankyou for the welcome!

:eek:

I didn't realise there was such a command.

:oops:

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!
 

Forum statistics

Threads
1,147,694
Messages
5,742,671
Members
423,746
Latest member
Joaogomes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top