Opening Files at "Persnl Sht" Every Time File Is O

Dave Wheeler

New Member
Joined
Jul 24, 2007
Messages
30
Have not been at VBA long so am prety much "junior" I suppose
What I have so far is folder for each Client! - maximum of 4 files in each folder and could all be open at same time
Have "central" VBA well set out control page with Client Files option and command buttons but files open at "last page saved"
Added worksheet select which opens perfect - after the "macro" yes/no open box opens with "last page saved"
Working with "office 2000 SB" and have tried various ways
workbook open / beforeclose / beforesave - matbe not 2000 objects
Just want client to see file opening with his name nicely shown on worksheet
Would really appreciate suggestions to get file open at client worksheet page from start
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Dave & Welcome to the Board!

You can activate a particular sheet after opening the workbook like:

Code:
Set wb = Workbooks.Open("C:\MyFile.xls")
wb.Worksheets(1).Activate

As long as you know what sheet (eg the first, second, third etc) you want to open on, you can use this method.

Does this answer your question?
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
use the workbook_open event to specify the sheet to activate

Code:
Option Explicit

Private Sub Workbook_Open()
Sheet1.Activate 'change to the sheet you want
End Sub
 

Dave Wheeler

New Member
Joined
Jul 24, 2007
Messages
30
Thanks for Speedy Response

Don't seem to be "winning" with the workbook_open solution but the worksheets opened 1st time
It's the Excel Msgbox "link" yes/no box that's the major stumble and I can't deactivate the "alarms" because I have to press the "yes" button
Have links to VAT inputs/outputs which are "essentials"
Problem is the opening sequence which concerns
Client will be seated watching while files are opened - and cannot but
notice confusing pageloads of figures visible while file is opening
Once Msgbox "yes/no" buttons are taken care of - yes - perfect sheet with Client data and impressive "blog"
As I say, "beforesave/Beforeclose" events etc don't seem to work on my Office2000 SB
Would be nicw to write something to save on Client sheet before I close so that file would open accordingly next time
Have tried various time/date adventures in cell A1 - but to no avail
Anybody with suggestions most welcome
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Dave

There is a Workbook_BeforeSave event - so you could plonk this in all your client workbooks (it works in exactly the same way as Workbook_Open, except before save naturally). You could activate the required sheet in there:

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("YourClientSheetNameHere").Activate
End Sub

This goes in the ThisWorkbook module of the Workbook.
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
Dave

There is a Workbook_BeforeSave event - so you could plonk this in all your client workbooks (it works in exactly the same way as Workbook_Open, except before save naturally). You could activate the required sheet in there:

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("YourClientSheetNameHere").Activate
End Sub

This goes in the ThisWorkbook module of the Workbook.

but that would return the user to the sheet each time the workbook was saved, not a good idea if someone is saving periodically as they should.

I would stick to the Open or Close events.
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
Client will be seated watching while files are opened - and cannot but
notice confusing pageloads of figures visible while file is opening

Code:
Application.ScreenUpdating = False
'your code here'
Application.ScreenUpdating = True
 

Dave Wheeler

New Member
Joined
Jul 24, 2007
Messages
30
Open Page now "Closed"

If I'd told George Bush about how wonderful you chaps are out there....
He would have no doubt responded with an "awesome"
Thank you, and thank you again
For a first time "board - asker-for-helper", I just can't believe how quickly you solved my problem
And Neil, when I saw your "updating" solution, I somehow knew that was it.....and it was......"open file - problem closed"
Thank you
Comment Noted
But its just so pleasing to be able to be "nice and tidy" at home, at work ......and on your PC
 

Dave Wheeler

New Member
Joined
Jul 24, 2007
Messages
30
Open Page now "Closed"

If I'd told George Bush about how wonderful you chaps are out there....
He would have no doubt responded with an "awesome"
Thank you, and thank you again
For a first time "board - asker-for-helper", I just can't believe how quickly you solved my problem
And Neil, when I saw your "updating" solution, I somehow knew that was it.....and it was......"open file - problem closed"
Thank you
Comment Noted
But its just so pleasing to be able to be "nice and tidy" at home, at work ......and on your PC
 

Forum statistics

Threads
1,181,053
Messages
5,927,848
Members
436,573
Latest member
CMR237

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