Excel VBA UserForm 'OK'

Question

Does anyone know how to make a userform function in the same way as the Message Box 'ok' button? I'll explain.

I'm detecting errors in a column in a spreadsheet. When an error is found, a message box pops up as follows:

MsgBox "Please enter valid data"

When I select "OK" it goes to the next error in the column. This is great, except of course a message box is modal, which freezes the application. I want the user to be able to edit the data and then move to the next error. So, I designed a userform, which can be non-modal. Great, except I want the macro to advance to the next error. It will do that IF the user corrects the error. If they do not, it just stays at that error cell.

I know WHY this happens. My userform 'Next' button just calls the macro which finds the first error. But what I want to know is if there is a way around this.

Error checking starts at row 19 because that is where user input data starts.

I'm including a link to the spreadsheet here. Module 1 'NextValidationError' works great and proceeds to the next error. Module 14 just hangs at the error until it is resolved. I'd like it to be able to skip.

https://www.dropbox.com/s/yqko5kj19pnauc9/Transparency%20Data%20Input%20Sheet%20for%20Indirect%20Spend%20V7%2009212016%20v2%200.xlsm?dl=0

Can anyone give me advice on how to make module 14 proceed as module 1?

Answer 1

Something like this:

Dim r_start As Long

Sub CheckNames()
    Dim r As Long
    '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

                    MsgBox "Please fill in First and Last Name or HCO in Row " & r & "."

                End If
            End If
        Next
    End With

End Sub

Unless I'm mis-reading your code you can combine your two checks with Or.

You will need some method to reset r_start when the user is done checking (if the form stays open after that).

EDIT: here's a very basic example. UserForm1 has two buttons - "Next" and "Close"

Code for "next" is just:

Private Sub CommandButton1_Click()
    ShowErrors
End Sub

In a regular module:

Dim r_start As Long

'this kicks off the checking process
Sub StartChecking()
    r_start = 0
    UserForm1.Show vbModeless
    ShowErrors
End Sub

'a simple example validation...
Sub ShowErrors()

    Dim c As Range, r As Long

    If r_start = 0 Then r_start = 9

    For r = r_start To 200
        With ActiveSheet.Rows(r)
            If Not IsNumeric(.Cells(1).Value) Then
                UserForm1.lblMsg.Caption = "Cell " & .Cells(1).Address() & " is not numeric!"
                r_start = r + 1
                Exit Sub
            End If
        End With
    Next r

    r_start = 0

    UserForm1.lblMsg.Caption = "No more errors"

End Sub