Select Leftmost Spreadsheet

  • Thread starter Thread starter Whicks
  • Start date Start date
W

Whicks

When a user saves a workbook, you can not trust the user to save while
residing in a preferred sheet. Nor can you trust that the sheets will
always have the same name as the previous workbook. I need some code
that will move my VB process to the first sheet, or leftmost, sheet in
the workbook.

Something along the lines of;

Activesheet.Next.Select or Activesheet.Previous.Select

but the problem is, I don't know how many sheets could possiblely be
in the workbook or what sheet the cursor was on when the workbook was
last saved.

Any suggestions?
 
Worksheets(1).Activate

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
When a user saves a workbook, you can not trust the user to save while
residing in a preferred sheet. Nor can you trust that the sheets will
always have the same name as the previous workbook. I need some code
that will move my VB process to the first sheet, or leftmost, sheet in
the workbook.
Click to expand...

And I take it you're doing something to prevent the user from re-arranging
the sheets...?
 
Bob & Rick,
I seem to have something a bit odd going on here. Was about to post same as
you guys, thought I'd better check for typos and it failed, but not due to a
typo

Worksheets(1).Activate ' fail
Sheets(1).Select' fail
ActiveWorkbook.Worksheets(1).Select ' fail
Debug.Print Err.Number ' 40036
Debug.Print Err.Description
' Application-defined or object-defined error
' same error on all above
Debug.Print Worksheets.Count ' 5

But this worked
Dim ws as Worksheet
Set ws = Worksheets(1)
ws.Activate

I then tried the code that failed on a some other wb's and all three lines
worked, as expected.

FWIW I've been running some tests filling the book with up to 2 million
cells of dummy data. Currently it's unsaved, ie the wb that won't accept the
sheet activate code.

Any thoughts ?

Regards,
Peter T
 
OMG! I was tremendously over thinking this! I was writing something
that would count the sheets and so on...

Thank You!
 
And I take it you're doing something to prevent the user from re-arranging
the sheets...?
Click to expand...

No. This is for a user who pulls down a global report from "X"
system. Then said person wants to isolate "Z" population. I am
providing a macro that will breakdown the report using access. I just
want the user to not have to worry about what page they save on. Just
save in this folder and hit go. If it's universal, it will sell.
 
No.  This is for a user who pulls down a global report from "X"
system.  Then said person wants to isolate "Z" population.  I am
providing a macro that will breakdown the report using access.  I just
want the user to not have to worry about what page they save on.  Just
save in this folder and hit go.  If it's universal, it will sell.
Click to expand...

Between your post Jeff and then me thinking about the "If it's
universal, it will sell.", I finally see your point.

Hmmmm...how do you suppose I ensure each sheet is correct? Without
protecting the workbook.

1) We can not trust the sheets to be named correctly
2) We can not trust the sheets to be in the proper location
3) Each sheet contains like data
 
OK understood. In the Sheet1 module I had an API declared like this -

Declare Function etc ' ie public

After commenting it or changing it to
Private Declare Function etc

the other rudimentary code worked fine, ie simply Worksheets(1). etc

I know very well not to declare a function as public in an object module,
only came about due to helping in another recent thread today.

It's now clear why the code failed, curious though that it worked after
doing this -

Dim ws as Worksheet
set ws = Worksheets(1)

Regards,
Peter T
 
You can work with the sheet codename, the user won't change that, maybe have
an index as part of the name, and then sort them on opening/closing.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

No. This is for a user who pulls down a global report from "X"
system. Then said person wants to isolate "Z" population. I am
providing a macro that will breakdown the report using access. I just
want the user to not have to worry about what page they save on. Just
save in this folder and hit go. If it's universal, it will sell.
Click to expand...

Between your post Jeff and then me thinking about the "If it's
universal, it will sell.", I finally see your point.

Hmmmm...how do you suppose I ensure each sheet is correct? Without
protecting the workbook.

1) We can not trust the sheets to be named correctly
2) We can not trust the sheets to be in the proper location
3) Each sheet contains like data
 
Hmmmm...how do you suppose I ensure each sheet is correct? Without
protecting the workbook.
Click to expand...
1) We can not trust the sheets to be named correctly
2) We can not trust the sheets to be in the proper location
3) Each sheet contains like data
Click to expand...

Honestly, I still don't understand what you're trying to do. Can you give me
a real-world, detailed scenario instead of using generic terms?
 
Back
Top