Excel 2007 VBA having cell hihglighted instead of moving to cell using Goto

[DougP]

I have this code which works but instead of the spreadsheet jumping to that cell and having it be the upper most left, I would like it to just go to that cell and the sheet stay where it is.

Private Sub txtNumber_AfterUpdate()
    Dim FindNumber As String
    Dim Rng2 As Range
    'get name
    FindNumber = Me.txtNumber.Value
    With Sheets("Sheet1").Range("B2:B500")
        Set Rng2 = .Find(What:=FindNumber, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlFormulas, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng2 Is Nothing Then
            Application.Goto Rng2, True ' <<<<<< jumps whole sheet to that location  >>>>>
            'Get  the column
            TheRow = ActiveCell.Row
            TheName = Worksheets("Sheet1").Cells(Rng2.Row, "A").Value
            Me.txtName = TheName
            Me.txtAmount.SetFocus
     
        Else
            MsgBox "Nothing found"
        End If
    End With
End Sub

DougP

 

[SkipVought]

Hi,

Just use the window FREEZR feature one time manually.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[taupirho]

take a note of what the top left-most cell is before you goto, goto to your new cell
then scroll back such that you are back to the top left-most cell you were at
e,g

toprow = ActiveWindow.ScrollRow
topcol = ActiveWindow.ScrollColumn

Application.Goto Reference:=...whatever....

ActiveWindow.ScrollRow = toprow
ActiveWindow.ScrollColumn = topcol



In order to understand recursion, you must first understand recursion.