Message box go to cell

[m1ngle]

Hello all;

I have a macro searching for errors throughout a worksheet. When an error is returned a message box is displayed indicating the location of the error. I would like the box to center over the offending cell. Is this possible?

What I have so far is

Sub CheckNames()
    Dim r As Long, c As Range
    'Dim emptyRow As Boolean


     If r_start = 0 Then r_start = 19


     With ActiveSheet
        For r = r_start To 5000
            'Checks entire row for data. User may skip rows when entering data.
            If WorksheetFunction.CountA(.Range(.Cells(r, 1), .Cells(r, 33))) > 0 Then
                If ((.Cells(r, 2) = "") <> (.Cells(r, 3) = "")) Or _
                   ((.Cells(r, 2) = "") = (.Cells(r, 4) = "")) Then
                    'UserForm20.Show
                    ActiveSheet.Range (.Cells(r, 2))
                    MsgBox "Please fill in First and Last Name or HCO in Row " & r & ".", vbOKCancel, "First and Last Name or HCO"
                    If vbCancel Then
                    HCPAddress
                    End If
                    
                End If
            End If
        Next
    End With
'Call HCPAddress
End Sub

The line "ActiveSheet.Range(.Cells(r,2)) always throws an error. Does anyone have any ideas?

Thanks!

 

[wesborland1234]

I don't think you can choose where to place a regular msgbox. You can use a Userform instead, and you should be able to change the position of that. But if you just want the cursor to jump to the offending cell to alert the user, you could just replace the line that is giving you trouble with:

Cells(r, 2).Activate

 

[m1ngle]

That was exactly what I needed. Thanks so much.

 

[wesborland1234]

No problem.