I need to learn a way to skip? or exit a if routine and go on to the next one if the first line doesn't meet the criteria. READ: ActiveCell.Offset(0, -1) = "F".

Ps. This sub is placed in the Change event.

First Off, i'm really new at this, and i've been watching and reading alot in order to get better. 99% of all my questions have so far been answered here in previous threads. This was my first exception. Reading material tips and tricks are welcomed

I've tried: If Not ActiveCell.Offset(0, -1) = "F" Then Exit Sub Which in this case just killed the whole routine. Ive also tried to split it in 5 different subs and tried to call them from the change event sub, but i get errors there too.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Target.Offset(0, -1) = Now

'Written by Bjoern Monroe (04.27.2019) - [Rev 1]


'If the above cell is = L then the selected cell can't contain "VS" or "VN"
If ActiveCell.Offset(0, -1) = "L" Then
    ElseIf ActiveCell.Text = "VS" Or ActiveCell.Text = "VN" Then
    MsgBox ("Trigger Warning")
    Else
    MsgBox ("Wont Trigger Warning")
End If

'If the above cell is = F then the selected cell can't contain "VS" or "VN"
If ActiveCell.Offset(0, -1) = "F" Then
    ElseIf ActiveCell.Text = "VS" Or ActiveCell.Text = "VN" Then
    MsgBox ("Trigger Warning")
    Else
    MsgBox ("Wont Trigger Warning")
End If

'If the above cell is = S then the selected cell can't contain "L", "VS", "F", or "S"
If ActiveCell.Offset(0, -1) = "S" Then
    ElseIf ActiveCell.Text = "L" Or ActiveCell.Text = "VS" Or ActiveCell.Text = "F" Or ActiveCell.Text = "S" Then
    MsgBox ("Trigger Warning")
    Else
    MsgBox ("Wont Trigger Warning")
End If

'If the above cell is = VN then the selected cell can't contain "L", "VN", "F", or "S"
If ActiveCell.Offset(0, -1) = "VN" Then
    ElseIf ActiveCell.Text = "L" Or ActiveCell.Text = "VN" Or ActiveCell.Text = "F" Or ActiveCell.Text = "S" Then
    MsgBox ("Trigger Warning")
    Else
    MsgBox ("Wont Trigger Warning")
End If

'If the above cell is = VS then the selected cell can't contain "VS", "L", Or "S"
If ActiveCell.Offset(0, -1) = "VS" Then
    ElseIf ActiveCell.Text = "VS" Or ActiveCell.Text = "L" Or ActiveCell.Text = "S" Then
    MsgBox ("Trigger Warning")
    Else
    MsgBox ("Wont Trigger Warning")
End If


End Sub

I have a column which always gets populated by a user with F,L,S,VN and VS. But the sequence of value which are populated is almost always the same. If The column cell above is F, The activecell can only be F, L, or S. Never VN or VS for instance. I want the user to get a popup message that tells them they are about to write the wrong Sequence.

Results so far. I get 5 popup boxes, instead of one which i was expecting. Which makes sense because the sub thinks i want it to check for all 5 even it it meets the criteria or not.

1 Answers

2
Louis On

A first step would be to fix the If syntax, as the ElseIf should not be indented and you're executing nothing if your condition is met.
I also added a check for the ActiveCell.Column, because if you select any row on the first column, your code will throw an Error:

    'If the above cell is = L then the selected cell can't contain "VS" or "VN"
    If ActiveCell.Column > 1 Then
        If ActiveCell.Offset(0, -1) = "L" Then
            'Nothing to execute?
        ElseIf ActiveCell.Text = "VS" Or ActiveCell.Text = "VN" Then
            MsgBox ("Trigger Warning")
        Else
            MsgBox ("Wont Trigger Warning")
        End If
    End If