CODEWITHSUNDEEP
vbaexcel
Cory Gabba
Cory Gabba

Reputation: 25

Excel VBA code executes when deleting/copying/pasting more than one cell at a time

These blocks of code seems to execute when you delete or edit more than one cell in the target columns at a time. In most cases the user is trying to paste allowed values into these cell, just more than one cell at a time. This causes users to have to deal with message boxes that pop up for no reason. Any ideas on how to have the same error checking, but without the message box popping up when you select or alter more than one cell? This code is pasted in a microsoft excel object (sheet code).

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 14 Then  'COLUMN N
    If Not IsNumeric(Target.Value) Then
        MsgBox "Please enter LBS ran with only numbers.", vbExclamation
        Exit Sub
    End If
    If Len(Target.Value) < 4 And Target.Value <> 0 Then
        MsgBox "Please enter LBS ran with comma included.", vbExclamation
        Exit Sub
    End If
End If

If Target.Column = 15 Then  'COLUMN O
    If Not IsNumeric(Target.Value) Then
        MsgBox "Please enter LBS ran with only numbers.", vbExclamation
        Exit Sub
    End If
    If Len(Target.Value) < 4 And Target.Value <> 0 Then
        MsgBox "Please enter LBS ran with comma included.", vbExclamation
        Exit Sub
    End If
End If


'IF COLUMN Q IS MARKED, BUT NOT COMPLETE TEXT TURNS RED
If Target.Column = 18 Then
    If Target.Value = "YES" Or Target.Value = "Yes" Or Target.Value = "yes" Then 'Marks text green
        With Target.Font
            .Color = -16724992
            .TintAndShade = 0
        End With
    Else
        With Target.Font
            .Color = vbRed
            .TintAndShade = 0
        End With
    End If
End If


End Sub

Upvotes: 1

Views: 76

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

You can always just check how many Targets were selected, and use that in an If statement:

If target.Count = 1 Then msgbox("My Message!")

Since it's a simple one liner If statement, you can do it all in one line instead of, say,

If target.Count = 1 Then
    msgbox("My Message!")
End If

And, looking more over the code, perhaps wrap the whole first part in such a statement, since all you're doing is putting up a message:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then ' <-------- Checking how many cells were changed.
    If Target.Column = 14 Then    'COLUMN N
        If Not IsNumeric(Target.Value) Then
            MsgBox "Please enter LBS ran with only numbers.", vbExclamation
            Exit Sub
        End If
        If Len(Target.Value) < 4 And Target.Value <> 0 Then
            MsgBox "Please enter LBS ran with comma included.", vbExclamation
            Exit Sub
        End If
    End If

    If Target.Column = 15 Then    'COLUMN O
        If Not IsNumeric(Target.Value) Then
            MsgBox "Please enter LBS ran with only numbers.", vbExclamation
            Exit Sub
        End If
        If Len(Target.Value) < 4 And Target.Value <> 0 Then
            MsgBox "Please enter LBS ran with comma included.", vbExclamation
            Exit Sub
        End If
    End If
End If 'Target.Count = 1 

'IF COLUMN Q IS MARKED, BUT NOT COMPLETE TEXT TURNS RED
' << Rest of your code here >>

Upvotes: 2

Related Questions