Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set Active Cell to A1 on Open Q
I have the code below which moves all sheets active cell to A1, but
how do I tweak to also do so when I have hidden worksheets? Currently it debugs as I have some sheets hidden Private Sub Workbook_Open() Application.ScreenUpdating = False Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets sh.Select Application.Goto Reference:=sh.Range("a1"), Scroll:=True Next sh ThisWorkbook.Sheets("Month").Select Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set Active Cell to A1 on Open Q
change the inside of the for/next loop to this:
if sh.visible then application.goto sh.range("A1"),true end if On Feb 16, 10:13*am, Seanie wrote: I have the code below which moves all sheets active cell to A1, but how do I tweak to also do so when I have hidden worksheets? Currently it debugs as I have some sheets hidden Private Sub Workbook_Open() Application.ScreenUpdating = False Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets * sh.Select * Application.Goto Reference:=sh.Range("a1"), Scroll:=True Next sh ThisWorkbook.Sheets("Month").Select Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set Active Cell to A1 on Open Q
on 2/16/2011, Seanie supposed :
I have the code below which moves all sheets active cell to A1, but how do I tweak to also do so when I have hidden worksheets? Currently it debugs as I have some sheets hidden Private Sub Workbook_Open() Application.ScreenUpdating = False Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets sh.Select Application.Goto Reference:=sh.Range("a1"), Scroll:=True Next sh ThisWorkbook.Sheets("Month").Select Application.ScreenUpdating = True End Sub Don't select the sheets. Private Sub Workbook_Open() Dim sh As Worksheet With Application .ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets If sh.Visible Then .Goto Reference:=sh.Range("a1"), Scroll:=True Else sh.Visible = xlSheetVisible .Goto Reference:=sh.Range("a1"), Scroll:=True sh.Visible = xlSheetHidden End If Next sh .ScreenUpdating = True End With '//Application ThisWorkbook.Sheets("Month").Select End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set Active Cell to A1 on Open Q
On Feb 16, 11:00*am, GS wrote:
on 2/16/2011, Seanie supposed : I have the code below which moves all sheets active cell to A1, but how do I tweak to also do so when I have hidden worksheets? Currently it debugs as I have some sheets hidden Private Sub Workbook_Open() Application.ScreenUpdating = False Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets * sh.Select * Application.Goto Reference:=sh.Range("a1"), Scroll:=True Next sh ThisWorkbook.Sheets("Month").Select Application.ScreenUpdating = True End Sub Don't select the sheets. * Private Sub Workbook_Open() * * Dim sh As Worksheet * * With Application * * *.ScreenUpdating = False * * * For Each sh In ThisWorkbook.Worksheets * * * * If sh.Visible Then * * * * * .Goto Reference:=sh.Range("a1"), Scroll:=True * * * * Else * * * * * sh.Visible = xlSheetVisible * * * * * .Goto Reference:=sh.Range("a1"), Scroll:=True * * * * * sh.Visible = xlSheetHidden * * * * End If * * * Next sh * * * .ScreenUpdating = True * * End With '//Application * * ThisWorkbook.Sheets("Month").Select * End Sub -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When I open a workbook, cell protection is not fully active | Excel Worksheet Functions | |||
When open move active cell | Excel Programming | |||
Why does a new Excel Workbook open with IV1 as the active cell? | Excel Discussion (Misc queries) | |||
Active cell as a reference to open a worksheet | Excel Worksheet Functions | |||
Open validationlist when cell is active | Excel Programming |