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)

"Whicks" wrote in message
...
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?

Try executing this...

Sheets(1).Select

Rick


"Whicks" wrote in message
...
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?

"Whicks" wrote in message
...

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.

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

"Rick Rothstein (MVP - VB)" wrote in
message ...
Try executing this...

Sheets(1).Select

Rick


"Whicks" wrote in message
...
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?

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

Thank You!

On Apr 24, 3:25*pm, "Jeff Johnson" wrote:
"Whicks" wrote in message

...

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.

And I take it you're doing something to prevent the user from re-arranging
the sheets...?

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.

On Apr 24, 3:33*pm, Whicks wrote:
On Apr 24, 3:25*pm, "Jeff Johnson" wrote:

"Whicks" wrote in message

...

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.

And I take it you're doing something to prevent the user from re-arranging
the sheets...?

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.

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


"Peter T" <peter_t@discussions wrote in message
...
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

"Rick Rothstein (MVP - VB)" wrote in
message ...
Try executing this...

Sheets(1).Select

Rick


"Whicks" wrote in message
...
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?

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)

"Whicks" wrote in message
...
On Apr 24, 3:33 pm, Whicks wrote:
On Apr 24, 3:25 pm, "Jeff Johnson" wrote:

"Whicks" wrote in message

...

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.

And I take it you're doing something to prevent the user from
re-arranging
the sheets...?

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.

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

"Whicks" wrote in message
...

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

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?