CODEWITHSUNDEEP
excelvba
Alexis
Alexis

Reputation: 1

VBA code for Row Validation

Attached is an expense report I’ll be rolling out to employees. To cut to the chase, Employees need to enter a job number and cost code for any expense in the upper section.

What I’d like is for a message to appear if an employee has neglected to enter a job number and/or cost code but is attempting to enter expenses in that same row, something along the lines of “Please enter a Job Number before continuing” and/or “Please enter a Cost Code before continuing”, depending on which box(s) they fail to complete. I received some help in creating the VBA language, but it isn’t quite working and I’m not sure what the problem is. Script for the Job Number works, but regardless of whether a cost code has been included or not, I continue to get the error message that the employee has to enter a cost code in column C.

I’ve already set up some validation rules for the Job number and Cost Code columns: job numbers have to be 7 characters long (our job numbers look like this: 13-7410) and cost codes have to be 4 characters long (our cost codes look like this: 9-20).

This is the VBA code that I’ve created so far:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("D:K")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Cells(Target.Row, 1) = "" Then
    MsgBox ("Please enter the Job Number in Column A.")
    Target.ClearContents
    Cells(Target.Row, 1).Select
ElseIf Cells(Target.Row, 2) = "" Then
    MsgBox ("Please enter the Cost Code in Column C.")
    Target.ClearContents
    Cells(Target.Row, 2).Select
End If
Application.EnableEvents = True

End Sub

unfortunately, I can't post a picture of the expense report...

Upvotes: 0

Views: 1066

Answers (1)

Tim Williams
Tim Williams

Reputation: 166511

Private Sub Worksheet_Change(ByVal Target As Range)

    Const MSG As String = "Please enter both the Job Number in Column A " & _
                 " and the Cost Code in Column C before adding expenses."
    Dim rw As Range

    If Intersect(Target, Me.Range("D:K")) Is Nothing Then Exit Sub

    Set Target = Target.Cells(1) 'in case user changed>1 cell
    Set rw = Target.EntireRow

    Application.EnableEvents = False

    If rw.Cells(1).Value = "" Then
        MsgBox MSG
        Target.ClearContents
        rw.Cells(1).Select
    ElseIf rw.Cells(3).Value = "" Then
        MsgBox MSG
        Target.ClearContents
        rw.Cells(3).Select
    End If

    Application.EnableEvents = True
End Sub

Upvotes: 1

Related Questions