Help with some simple code

[Angeline]

Hi There,
I was given the following code to ensure that whenever a workbook is opened
it will always go to Sheet1 and A1. This was great:

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Range("A1").Select
End Sub

I'm now requiring something similar to ensure that if any other sheets are
selected once the workbook is opened, the command [Range("A1").Select]
occurs.So, could someone help me with the first part of the code. I assume
you would go into the code viewer for each sheet and add something like:
Sheet1_Open()
Sheets("Sheet1").Select
Range("A1").Select
End Sub

I think I tried this but a compile error when I opened up the workbook.
Hope someone can help (something tells me I could put some code in a module
so that I wan't have to repeat it for each sheet. However, happy to just get
the basic code to put in if that is not viable)

Hope someone can help

 

[Bob Phillips]

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Range("A1").Select
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Angeline]

Hi Bob,
Thanks for this. I tried it however and it did not seem to work. I inserted
the code into the VB Editor for each sheet and saved. To test it I scrolled
down on a number of the sheets, saved & closed. Upon opening up the workbook
and clicking the said sheets, they remained scrolled down as I had left them.
Could I be doing something wrong?

Thanks

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Range("A1").Select
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hi There,
I was given the following code to ensure that whenever a workbook is opened
it will always go to Sheet1 and A1. This was great:

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Range("A1").Select
End Sub

I'm now requiring something similar to ensure that if any other sheets are
selected once the workbook is opened, the command [Range("A1").Select]
occurs.So, could someone help me with the first part of the code. I assume
you would go into the code viewer for each sheet and add something like:
Sheet1_Open()
Sheets("Sheet1").Select
Range("A1").Select
End Sub

I think I tried this but a compile error when I opened up the workbook.
Hope someone can help (something tells me I could put some code in a module
so that I wan't have to repeat it for each sheet. However, happy to just get
the basic code to put in if that is not viable)

Hope someone can help

 

[JMB]

Bob's code should go in the ThisWorkbook module, not each sheet module.

Hi Bob,
Thanks for this. I tried it however and it did not seem to work. I inserted
the code into the VB Editor for each sheet and saved. To test it I scrolled
down on a number of the sheets, saved & closed. Upon opening up the workbook
and clicking the said sheets, they remained scrolled down as I had left them.
Could I be doing something wrong?

Thanks

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Range("A1").Select
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hi There,
I was given the following code to ensure that whenever a workbook is opened
it will always go to Sheet1 and A1. This was great:

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Range("A1").Select
End Sub

I'm now requiring something similar to ensure that if any other sheets are
selected once the workbook is opened, the command [Range("A1").Select]
occurs.So, could someone help me with the first part of the code. I assume
you would go into the code viewer for each sheet and add something like:
Sheet1_Open()
Sheets("Sheet1").Select
Range("A1").Select
End Sub

I think I tried this but a compile error when I opened up the workbook.
Hope someone can help (something tells me I could put some code in a module
so that I wan't have to repeat it for each sheet. However, happy to just get
the basic code to put in if that is not viable)

Hope someone can help

 

[Bob Phillips]

Add the code to the same module as your Workbook_Open code.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hi Bob,
Thanks for this. I tried it however and it did not seem to work. I inserted
the code into the VB Editor for each sheet and saved. To test it I scrolled
down on a number of the sheets, saved & closed. Upon opening up the workbook
and clicking the said sheets, they remained scrolled down as I had left them.
Could I be doing something wrong?

Thanks

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Range("A1").Select
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hi There,
I was given the following code to ensure that whenever a workbook is opened
it will always go to Sheet1 and A1. This was great:

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Range("A1").Select
End Sub

I'm now requiring something similar to ensure that if any other sheets are
selected once the workbook is opened, the command [Range("A1").Select]
occurs.So, could someone help me with the first part of the code. I assume
you would go into the code viewer for each sheet and add something like:
Sheet1_Open()
Sheets("Sheet1").Select
Range("A1").Select
End Sub

I think I tried this but a compile error when I opened up the workbook.
Hope someone can help (something tells me I could put some code in a module
so that I wan't have to repeat it for each sheet. However, happy to

just
get

the basic code to put in if that is not viable)

Hope someone can help