Sheet selected, automatic return to top of sheet
 

Hi all.

Is there a formula that...When a sheet is selected via a macro button, you
are automatically returned to the top of the selected sheet...or, when you
open your workbook the next day all sheets are returned to the top.

If this is possible I would need sheets returned to Row 8, as Rows 1-7 are
frozen

Thanks in advance
Mike

Sheet selected, automatic return to top of sheet
 

You need these two event sub for it:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
ws.Range("A8").Select
Next ws
Worksheets(1).Activate
End Sub

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


Post if you need help to install them!

--
Regards!
Stefi



€žMIKEY€� ezt Ã*rta:

Hi all.

Is there a formula that...When a sheet is selected via a macro button, you
are automatically returned to the top of the selected sheet...or, when you
open your workbook the next day all sheets are returned to the top.

If this is possible I would need sheets returned to Row 8, as Rows 1-7 are
frozen

Thanks in advance
Mike

Sheet selected, automatic return to top of sheet
 

I went to install in WorkBook and i get ..........Compile error:
Ambiguous name
detected:Workbook_Open

"Stefi" wrote:

You need these two event sub for it:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
ws.Range("A8").Select
Next ws
Worksheets(1).Activate
End Sub

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


Post if you need help to install them!

--
Regards!
Stefi



€žMIKEY€� ezt Ã*rta:

Hi all.

Is there a formula that...When a sheet is selected via a macro button, you
are automatically returned to the top of the selected sheet...or, when you
open your workbook the next day all sheets are returned to the top.

If this is possible I would need sheets returned to Row 8, as Rows 1-7 are
frozen

Thanks in advance
Mike

Sheet selected, automatic return to top of sheet
 

You only get one Workbook_Open event per workbook.

You'll have to merge the procedures into one.

If you need help, you should post both procedures in your follow-up.

MIKEY wrote:

I went to install in WorkBook and i get ..........Compile error:
Ambiguous name
detected:Workbook_Open

"Stefi" wrote:

You need these two event sub for it:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
ws.Range("A8").Select
Next ws
Worksheets(1).Activate
End Sub

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


Post if you need help to install them!

--
Regards!
Stefi



€žMIKEY€� ezt Ã*rta:

Hi all.

Is there a formula that...When a sheet is selected via a macro button, you
are automatically returned to the top of the selected sheet...or, when you
open your workbook the next day all sheets are returned to the top.

If this is possible I would need sheets returned to Row 8, as Rows 1-7 are
frozen

Thanks in advance
Mike

--

Dave Peterson

Sheet selected, automatic return to top of sheet
 

Thanks Stefi

I used the first formula and all my sheets return to the beginning. I have
some sheets that are Frozen in screen view and they don't move. How do I
omit these from the formula.

There are 35 sheets I would like to omit and I guess I will need to list
these. The sheets are not in numerical order

Regards
Mike

"Stefi" wrote:

You need these two event sub for it:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
ws.Range("A8").Select
Next ws
Worksheets(1).Activate
End Sub

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


Post if you need help to install them!

--
Regards!
Stefi



€žMIKEY€� ezt Ã*rta:

Hi all.

Is there a formula that...When a sheet is selected via a macro button, you
are automatically returned to the top of the selected sheet...or, when you
open your workbook the next day all sheets are returned to the top.

If this is possible I would need sheets returned to Row 8, as Rows 1-7 are
frozen

Thanks in advance
Mike

Sheet selected, automatic return to top of sheet
 

Sorry, I couldn't find out what you mean on "Frozen in screen view", please
clarify it. Isn't there any common properties of sheets to be omitted?

--
Regards!
Stefi



€žMIKEY€� ezt Ã*rta:

Thanks Stefi

I used the first formula and all my sheets return to the beginning. I have
some sheets that are Frozen in screen view and they don't move. How do I
omit these from the formula.

There are 35 sheets I would like to omit and I guess I will need to list
these. The sheets are not in numerical order

Regards
Mike

"Stefi" wrote:

You need these two event sub for it:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
ws.Range("A8").Select
Next ws
Worksheets(1).Activate
End Sub

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


Post if you need help to install them!

--
Regards!
Stefi



€žMIKEY€� ezt Ã*rta:

Hi all.

Is there a formula that...When a sheet is selected via a macro button, you
are automatically returned to the top of the selected sheet...or, when you
open your workbook the next day all sheets are returned to the top.

If this is possible I would need sheets returned to Row 8, as Rows 1-7 are
frozen

Thanks in advance
Mike

Sheet selected, automatic return to top of sheet
 

You can't scroll down or across i.e. freeze pane
Common properties for sheets to be omitted...they are 'help' sheets for the
users

Cheers Mike
..

"Stefi" wrote:

Sorry, I couldn't find out what you mean on "Frozen in screen view", please
clarify it. Isn't there any common properties of sheets to be omitted?

--
Regards!
Stefi



€žMIKEY€� ezt Ã*rta:

Thanks Stefi

I used the first formula and all my sheets return to the beginning. I have
some sheets that are Frozen in screen view and they don't move. How do I
omit these from the formula.

There are 35 sheets I would like to omit and I guess I will need to list
these. The sheets are not in numerical order

Regards
Mike

"Stefi" wrote:

You need these two event sub for it:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
ws.Range("A8").Select
Next ws
Worksheets(1).Activate
End Sub

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


Post if you need help to install them!

--
Regards!
Stefi



€žMIKEY€� ezt Ã*rta:

Hi all.

Is there a formula that...When a sheet is selected via a macro button, you
are automatically returned to the top of the selected sheet...or, when you
open your workbook the next day all sheets are returned to the top.

If this is possible I would need sheets returned to Row 8, as Rows 1-7 are
frozen

Thanks in advance
Mike

Sheet selected, automatic return to top of sheet
 

Try these:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
If Not ActiveWindow.FreezePanes Then _
ws.Range("A8").Select
Next ws
Worksheets(1).Activate
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Not ActiveWindow.FreezePanes Then _
Sh.Range("A8").Select
End Sub



--
Regards!
Stefi



€žMIKEY€� ezt Ã*rta:

You can't scroll down or across i.e. freeze pane
Common properties for sheets to be omitted...they are 'help' sheets for the
users

Cheers Mike
.

"Stefi" wrote:

Sorry, I couldn't find out what you mean on "Frozen in screen view", please
clarify it. Isn't there any common properties of sheets to be omitted?

--
Regards!
Stefi



€žMIKEY€� ezt Ã*rta:

Thanks Stefi

I used the first formula and all my sheets return to the beginning. I have
some sheets that are Frozen in screen view and they don't move. How do I
omit these from the formula.

There are 35 sheets I would like to omit and I guess I will need to list
these. The sheets are not in numerical order

Regards
Mike

"Stefi" wrote:

You need these two event sub for it:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
ws.Range("A8").Select
Next ws
Worksheets(1).Activate
End Sub

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


Post if you need help to install them!

--
Regards!
Stefi



€žMIKEY€� ezt Ã*rta:

Hi all.

Is there a formula that...When a sheet is selected via a macro button, you
are automatically returned to the top of the selected sheet...or, when you
open your workbook the next day all sheets are returned to the top.

If this is possible I would need sheets returned to Row 8, as Rows 1-7 are
frozen

Thanks in advance
Mike