vb Command seems simple but...

[Guest]

What is a vbCommand for Ctrl + Home? I want to scroll up and left on all
worksheets in a Private Sub Workbook_Open when there are various Freeze Panes
settings, so I cannot select a particular cell in all sheets.

Thanks in advance,

Sam

 

[Guest]

One way:

Sub scrl()
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
End Sub

 

[Mark Lincoln]

The previous suggestion doesn't select the cell in the unfrozen area
as does Ctrl-Home. I assume that's what you want.

If you are looping through the worksheets by index number, you could
name the cell in each sheet to which you want to scroll (something
like GoHere1, GoHere2, etc), use your counter to append the sheet
index number to "GoHere" in a string variable and use Select to go to
each in turn.

Dim ULcell as String

[...]

ULcell = "GoHere" & CStr(<put your counter variable here>)
Range(ULcell).Select

Of course, this fails miserably if the sheets are shuffled around.

There may be a simpler way, but I haven't found it.

Mark Lincoln

 

[Rick Rothstein \(MVP - VB\)]

What is a vbCommand for Ctrl + Home? I want to scroll up and left on all

worksheets in a Private Sub Workbook_Open when there are various Freeze
Panes
settings, so I cannot select a particular cell in all sheets.

I think this Workbook Open code will do what you want...

Private Sub Workbook_Open()
Dim WS As Worksheet
For Each WS In Worksheets
WS.Activate
With ActiveWindow
WS.Cells(.Panes(.Panes.Count).ScrollRow, _
.Panes(.Panes.Count).ScrollColumn).Activate
End With
Next
Worksheets(1).Activate
End Sub


Rick

 

[NickHK]

Try this:

Private Sub CommandButton2_Click()

With ActiveWindow.ActivePane
.LargeScroll , 256, , 256
Cells(.ScrollRow, .ScrollColumn).Select
End With

End Sub

However, I recall there are problem if rows/columns are hidden just under/to
the right of the split(s)
Also, the effect will depend on whether panes are frozen or not.

NickHK