Excel VBA: Go to Different Column in Same Row but Do Not Scroll Cell to Top of Screen

[KC E]

Hello,

I have code (see below) behind a toggle button on my spreadsheet that, when clicked the 1st time, it will go to column AL (the Comments column) on the same row as the selected cell (it will also change the caption of the toggle to say. '<<<Go Back to Node A').

When clicked the 2nd time, it will go to back to column H (the Node A column) on the same row as the selected cell (it will also change the caption of the toggle back to say, 'Go to Comments/Date>>>').

My problem is that it shifts or scrolls the spreadsheet where the selected cell is at the top (I have the 1st 3 rows and the 1st 9 columns (after Node B column) frozen via Freeze Panes). Ex: I scroll to a cell in Column H Row 18 where it appears in the middle of the screen. I hit the toggle button and Excel selects the cell in Column AL of the same Row 18, but Excel scrolls Row 18 (with the selected cell) to the top of the page and I want it to stay in the middle. I need it to stay in the middle for a reason.

I've attached a sample spreadsheet, if that will help (the code is under the workbook name, under Module 1). I would appreciate any help b/c I can't find (or maybe I'm not phrasing the question wrong) via Google.

Thank you!
(using Excel 2013)

Private Sub ToggleButton1_Click()

Dim r As Long

r = ActiveCell.Row

If ToggleButton1.Caption = "Go to Comments/Date>>>" Then
    Application.Goto Reference:=Worksheets("2017 Schedule").Range("AL" & r), Scroll:=True
    ToggleButton1.Caption = "<<<Go back to Node A"
Else
    Application.Goto Reference:=Worksheets("2017 Schedule").Range("H" & r), Scroll:=True
    ToggleButton1.Caption = "Go to Comments/Date>>>"
End If
End Sub

 

[Chihiro]

Change code to something like...

Private Sub ToggleButton1_Click()

Dim r As Long

r = ActiveCell.Row

If ToggleButton1.Caption = "Go to Comments/Date>>>" Then
    Application.Goto reference:=Worksheets("2017 Schedule").Range("AL" & r), Scroll:=True
    With ActiveWindow
        .SmallScroll UP:=.VisibleRange.Rows.Count / 2
    End With
    ToggleButton1.Caption = "<<<Go back to Node A"
Else
    Application.Goto reference:=Worksheets("2017 Schedule").Range("H" & r), Scroll:=True
    With ActiveWindow
        .SmallScroll UP:=.VisibleRange.Rows.Count / 2
    End With
    ToggleButton1.Caption = "Go to Comments/Date>>>"
End If
End Sub

 

[Debaser]

You could use the ScrollColumn:

If ToggleButton1.Caption = "Go to Comments/Date>>>" Then
    ActiveWindow.ScrollColumn = Range("AL1").Column
    ToggleButton1.Caption = "<<<Go back to Node A"
Else
    ActiveWindow.ScrollColumn = Range("H1").Column
    ToggleButton1.Caption = "Go to Comments/Date>>>"
End If

 

[KC E]

You could use the ScrollColumn:

If ToggleButton1.Caption = "Go to Comments/Date>>>" Then
    ActiveWindow.ScrollColumn = Range("AL1").Column
    ToggleButton1.Caption = "<<<Go back to Node A"
Else
    ActiveWindow.ScrollColumn = Range("H1").Column
    ToggleButton1.Caption = "Go to Comments/Date>>>"
End If

This works perfectly. Thank you (both) for your help!! I really appreciate it.

 

[KC E]

Change code to something like...

Private Sub ToggleButton1_Click()

Dim r As Long

r = ActiveCell.Row

If ToggleButton1.Caption = "Go to Comments/Date>>>" Then
    Application.Goto reference:=Worksheets("2017 Schedule").Range("AL" & r), Scroll:=True
    With ActiveWindow
        .SmallScroll UP:=.VisibleRange.Rows.Count / 2
    End With
    ToggleButton1.Caption = "<<<Go back to Node A"
Else
    Application.Goto reference:=Worksheets("2017 Schedule").Range("H" & r), Scroll:=True
    With ActiveWindow
        .SmallScroll UP:=.VisibleRange.Rows.Count / 2
    End With
    ToggleButton1.Caption = "Go to Comments/Date>>>"
End If
End Sub

The same reply goes for your answer: This works perfectly. Thank you (both) for your help!! I very much appreciate your time and help.