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

 

[Fluff]

Ok, my suggestion was based on your post#3

When i press my command button labeled "R.8" , i need it to scroll to the row that has "R.8" in Column A.

do you want the button called 1 to take you to R.1 & the button called 2 to take you to R.2 etc?

 

[tjktm]

yes. exactly.

 

[Fluff]

In that case try

Sub ScrollToRow()
   Dim Fnd As Variant, fndRng As Range
   
   Fnd = "R." & 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]

Thank you

 

[Fluff]

You're welcome

 

[Ken Cowen]

This is a good description of how to use a single code for multiple buttons on a userform.

http://spreadsheetpage.com/index.php/site/tip/handle_multiple_userform_buttons_with_one_subroutine/

You need to modify it a bit to use activeX buttons on a worksheet. Something like this, maybe in workbook activate or some other way to trigger it:

Private Sub Worksheet_Activate()


    Dim ButtonCount As Integer
    Dim objX As Object


    ButtonCount = 0
    
    For Each objX In ActiveSheet.OLEObjects
    
        If TypeName(objX.Object) = "CommandButton" Then
             
            If objX.Name <> "OKButton" Then 'Skip the OKButton
                ButtonCount = ButtonCount + 1
                ReDim Preserve Buttons2(1 To ButtonCount)
               Set Buttons2(ButtonCount).ButtonGroup = objX.Object
            End If
            
        End If
        
    Next objX


End Sub