If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|