Open Workbook to Specific Worksheet?

[Steven Hilgendorf]

Question,

We have a shared workbook that we use to keep track of purchase order
numbers. The workbook contains yearly worksheet tabs (i.e. 2007, 2006,
2005, etc.) The problem is that when some users open the workbook they
have to navigate to the current worksheet (2007) at the end of the list
to enter the next purchase order.

It does not matter if the user saves the workbook in the current
worksheet (2007) at the end of the list, say cell C1024 of the 2007 tab.
The next time they open the workbook it defaults back to a different
worksheet in a different location. Different users startup in different
locations and worksheets.

How can the users save the file so that when they open it they are in
the current year worksheet at the end of the list?

Thank you,

Steven

 

[Gord Dibben]

Steven

Private Sub Workbook_Open()
Sheets("2007").Activate
ActiveSheet.Range("C1024").Select
End Sub

With your workbook open, right-click on the Excel logo left of "File" if your
window is maximized or on the title bar Excel logo if not maximized.

Select "View Code" and Thisworkbook module will open.

Paste the code into that module

Close that window. Save your workbook and close it.

Each time you open it, it will open to the worksheet you have named no matter
where you were when you last saved/closed.


Gord Dibben MS Excel MVP

 

[Guest]

You could create a worksheet_open macro. I'm assuming that the "current
year" is the last worksheet in the list. If that's true, put this in it. If
it;s not true, you'll need to do something else.

Private Sub Workbook_Open()
Dim aWB As Workbook
Set aWB = ActiveWorkbook

Worksheets(aWB.Worksheets.Count).Activate

End Sub

HTH,
Barb Reinhardt

 

[Steven Hilgendorf]

Thank you Gord and Barb for the tips. Gord I am unable to paste the
script into the VB screen. In fact 'view code' does not open with
ThisWorbook (code) page? Could the file be protected somehow? If so,
how can fix this.

The person that created the spreadsheet no longer works for the company
and I have taken over her responsibilities.

Thanks again,

Steven

 

[Gord Dibben]

Could be the VB Editor Windows are minimized somehow but you can get through
another way.

With your workbook open hit Alt + F11 to open VBE

CTRL + r to open project explorer.

Select your workbook/project from the list and hit the + sign to expand.

Hit the + sign for Microsoft Excel Objects to expand.

Double-click on Thisworkbook to open.

Paste the code into that module.


Gord

 

[Steven Hilgendorf]

Hello Gord, thanks for the additional help. I'm still not able to paste
this script and I don't get Thisworkbook. If I choose properties of
VBAProject(po.xls), I get "project locked" "project is unviewable"?

What does this mean? Is there a way to unlock the project and make it
viewable, so I can enter the script?

Thanks again for your continued support,

Steven

 

[Dave Peterson]

Someone locked the project for this workbook. You'll need to supply the
password to open the project.

If you're not the developer, then you'll need to talk to that person to get the
password.

 

[Gord Dibben]

As the information leaks out we get closer to a fix<g>

See Dave P's response.

You may be able to crack in using the info at John McGimpsey's site.

http://www.mcgimpsey.com/excel/fileandvbapwords.html


Gord

 

[Steven Hilgendorf]

Thanks Gord and Dave. Where do you enter the password? It never
prompts for one? We have a short list of passwords that the former
employee used, so I could try these. I will also check out the URL you
provided.

Thanks again,

Steven

 

[Dave Peterson]

The only time I've seen the "project is unviewable" warning message is when I
was using xl2k at one location and xl97 at another.

If I protected the project in xl2k (or higher) and tried to open it in xl97,
then I would get that message.

I had to go to the newer version, unlock the project, save the workbook with the
project unprotected, then I could do my changes in xl97.

My point is that xl97 can't open projects protected in later versions.

Is that your situation?

Actually, there is another time when I get that message. If the workbook is
shared, I'll get it when I try to access the project.

Is your workbook shared? If it is, then back to excel and unshare it
(tools|Share workbook).

=====
My guess is on the xl97 stuff since you did get that first message "project is
locked". I don't get that with a shared workbook.

 

[Gord Dibben]

Good point Dave.

I believe projects from 2003 get the same message when opening in earlier
versions.


Gord

 

[Steven Hilgendorf]

Hello Dave & Gord. We are using 2003 and I beleive they upgraded from
'97, where the original file would have been created. I tried the
password program at the site you quoted/provided earlier, without
success. I don't know what else to do to correct this. Any other
suggestions, besides recreating the file in a clean workbook?

Thanks again for your efforts,

Steven

 

[Dave Peterson]

The problem isn't opening the project (using xl2003) that was protected in xl97,
it's when you try to open the project that was protected in xl2003 (using xl97).

Depending on how envolved the workbook is, you may be able to just select all
the sheets and then Edit|move or copy sheets and copy them to a new workbook.

Then save from there.

If that doesn't work, and if it's a small file, I may have something to remove
the password.

Please zip it and send it to me (if you can).

No promises, though. I've seen it work and I've seen it fail.

 

[Steven Hilgendorf]

Hello Dave and Gord:

I wanted to follow up and let you know I rebuilt the password problem-ed
spreadsheet successfully. It took some time, but I managed to turn the
sluggish 5MB+ problem into a clean, 1.2MB, snappy file, that behaves
when saved ans reopened.

Thanks again for all your help and support. I really appreciate it,

Steven

 

[Dave Peterson]

Good to hear that you got it fixed. And rebuilding that workbook sounds like it
was the right thing to do.