Scroll to cell value

[tjktm]

My spreadsheet is set up to scroll from a button to a certain row number. That row contains specific information. However, when new rows are added or deleted, the button scrolls to rows above or below the correct row. I have the following code now:

Sub View_9()
' View_Total Macro
ActiveWindow.ScrollRow = 298

This code works great as long as no rows are added or deleted.

Need code to scroll to a variable cell with a specific value in column A. The specific value will be "R.1" , "R.2", etc. . Those values could be changed if they complicate the code.

Thanks for any help

TJ

 

[JoeMo]

You can try this from an active-x command button on the sheet your specific value is on.

Private Sub CommandButton1_Click()
Dim Fnd As Variant, fndRng As Range
Fnd = InputBox("What value in col A do you want to scroll to?")
If Fnd = "" Then Exit Sub  'cancel was clicked
Set fndRng = Columns("A").Find(Fnd)
If Not fndRng Is Nothing Then
    ActiveWindow.ScrollRow = fndRng.Row
Else
    MsgBox "Can't find " & Fnd & " in column A"
End If
End Sub

 

[tjktm]

Thanks. but is there a way to do this so i don't have to input the value? I think my description above was misleading. The value will stay constant on the row to scroll to. When i press my command button labeled "R.8" , i need it to scroll to the row that has "R.8" in Column A.

is that possible?

 

[Ken Cowen]

Hard to beat Joe's code for what you originally requested. The following tweak should take care of your refined request.

Private Sub CommandButton1_Click()
Dim Fnd As Variant, fndRng As Range
Fnd = CommandButton1.caption
If Fnd = "" Then Exit Sub 'cancel was clicked
Set fndRng = Columns("A").Find(Fnd)
If Not fndRng Is Nothing Then
ActiveWindow.ScrollRow = fndRng.Row
Else
MsgBox "Can't find " & Fnd & " in column A"
End If
End Sub

 

[Ken Cowen]

If you have a lot of commandbuttons that have this same functionality, you should consider adding them to a class, so that they can all use the same code.

 

[tjktm]

Thank you. it works perfect. I do have 16 of those command buttons that need to do the same thing but i'm not familiar with adding them to a class. Thanks for the heads up, I'll look around for some info on this forum.

Thanks again!

 

[Fluff]

What type of Buttons are you using?
ActiveX, Form controls, or something else?

 

[tjktm]

Form control but was going to change them to ActiveX as Joe suggested previously.

 

[Fluff]

If the buttons are labelled with the value you want to find, you can use this code on all buttons

Sub ScrollToRow()
Dim Fnd As Variant, fndRng As Range
Fnd = ActiveSheet.Shapes(Application.Caller).AlternativeText
If Fnd = "" Then Exit Sub  'cancel was clicked
Set fndRng = Columns("A").Find(Fnd)
If Not fndRng Is Nothing Then
    ActiveWindow.ScrollRow = fndRng.Row
Else
    MsgBox "Can't find " & Fnd & " in column A"
End If
End Sub

 

[tjktm]

Thanks but each button is labeled with a number. 1 through 16