Apply VBA code to multiple columns as oppose to just one.

[KP_SoCal]

The code below restricts users from inputting anything other than an uppercase "X" in a cell within column N. If the user types a lowercase "x", it would automatically convert it to uppercase. If any other value is entered other than "X", a message box will fire.

The code is specific to column N (column 13). However, I would like to apply this to multiple columns, such as columns 13,14,17,20.

Any suggestions on how I would rewrite this code? Thanks!

    If Target.Column = 13 And Target.Value <> "X" And Target.Value <> "x" And Target.Value <> "" Then
        Target.ClearContents
        Cells(Target.Row, IIf(Target.Column = 13, 13, 13)).Select
        MsgBox "Value" & Chr$(34) & "X" & Chr$(34) & " is the only value allowed.", _
        vbInformation + vbOKOnly
    ElseIf Target.Column = 13 And Target.Value = "x" Then
        Target = UCase(Target)
    End If

 

[lenze]

Select Case Target.Column
Case 13,14,17,20
'your code
Case Else:
End Select

lenze

 

[KP_SoCal]

Select Case Target.Column
Case 13,14,17,20
'your code
Case Else:
End Select

lenze

Thanks Lenze, I guess I just needed a little push to get it to work. I'm good-to-go now. That was precisely what I needed to use.

 

[KP_SoCal]

Another dilemma with this is that I'm not only trying to restrict it to certain columns, but also rows within that column. Without the If Target.Range("M6:N50000") Then line of code the case statement on its own works perfectly. But I need to restrict it to cells M6:N50000.

Here's the code I'm using that isn't working. I'm sure it is something wrong with my IF statement. Thanks again for your help!

If Target.Range("M6:N50000") Then
    Select Case Target.Column
    Case 13, 14 And Target.Value <> "X" And Target.Value <> "x" And Target.Value <> ""
Sun Target.ClearContents
        Target.Select
        MsgBox "To restrict WKST query to vehicles with a specific option (e.g. Sunroof), " & _
            "input the letter " & Chr$(34) & "X" & Chr$(34) & " in the appropriate cell row.", _
            vbInformation + vbOKOnly
 
    Case 13, 14 And Target.Value = "x"
        Target = UCase(Target)
    Case Else:
    End Select
End If

 

[Blade Hunter]

What is

Sun Target.ClearContents

?

 

[Blade Hunter]

And change your if to:

If Target.Address = ("$M$6:$N$50000") Then

Also, you can shorten:

Case 13, 14 And Target.Value <> "X" And Target.Value <> "x" And Target.Value <> ""

to

Case 13, 14 And UCase(Target.Value) <> "X" And Target.Value <> ""

Edit: Another one

Change

    Case 13, 14 And Target.Value = "x"
        Target = UCase(Target)
    Case Else:

to

        if Target.Value = "x" Target = "X"

You already know it is case 13,14 as you satisfied that criteria before.

No need for an Case Else when there is none.

You could also make it an if instead of a select case if you like as there is only 1 criteria. Horses for courses though.

Cheers

Dan

 

[KP_SoCal]

Dan, thanks for the suggestions. I applied the first one, but as soon as I implements the If Target.Address = ("$M$6:$N$50000") Then, my entire code stopped working. Without this statement, the code works fine. Any other ideas?

DOESN'T WORK

If Target.Address = ("$M$6:$N$50000") Then
    Select Case Target.Column
    Case 13, 14 And Target.Value <> "X" And Target.Value <> "x" And Target.Value <> ""
Target.ClearContents
        Target.Select
        MsgBox "To restrict WKST query to vehicles with a specific option (e.g. Sunroof), " & _
            "input the letter " & Chr$(34) & "X" & Chr$(34) & " in the appropriate cell row.", _
            vbInformation + vbOKOnly
 
    Case 13, 14 And Target.Value = "x"
        Target = UCase(Target)
    Case Else:
    End Select
End If

WORKS (but this doesn't restrict the range to certain cells, just the columns)

    Select Case Target.Column
    Case 13, 14 And Target.Value <> "X" And Target.Value <> "x" And Target.Value <> ""
Target.ClearContents
        Target.Select
        MsgBox "To restrict WKST query to vehicles with a specific option (e.g. Sunroof), " & _
            "input the letter " & Chr$(34) & "X" & Chr$(34) & " in the appropriate cell row.", _
            vbInformation + vbOKOnly
 
    Case 13, 14 And Target.Value = "x"
        Target = UCase(Target)
    Case Else:
    End Select

 

[Blade Hunter]

Try this:

On error goto finished
If Target.Count > 1 Then End 'This stops it erroring when a range is selected and deleted
Application.EnableEvents = False 'Turn the events off to prevent infinite loops
If (Target.Column = 13 Or Target.Column = 14) And (Target.Row > 5 Or Target.Row < 50001) And Target.Value <> "" Then
    If Target.Value = "x" Then
        Target = "X"
        GoTo Finished 'Finished, no need to do the next set of instructions
    End If
    Target.ClearContents
    Target.Select
    MsgBox "To restrict WKST query to vehicles with a specific option (e.g. Sunroof), " & _
        "input the letter " & Chr$(34) & "X" & Chr$(34) & " in the appropriate cell row.", _
        vbInformation + vbOKOnly
End If
Finished:
Application.EnableEvents = True 'Re enable the events for the next entry

 

[KP_SoCal]

Hey, Aussie, Aussie, Aussie! It worked prefectly! Thanks for all your help Dan, Cheers!!!

 

[KP_SoCal]

Oooops, sorry I spoke too soon. My fault, I should have tested it more thoroughly. Below is the complete code I'm using.

Problems:
1) The only way the message box won't fire now is if you enter a lower case "x". If the user enters an uppercase "X", the message should not fire. I tried fixing this by adding an ElseIF statement, but then the entire code stopped working (I'm definitely not a VBA expert--I'm hardly worthy to be referred to as a "novice").

2) Also this code should not be firing if entries are made in cells M1:N4, but it does. I'm not sure why, because your code explicitly states that it should only target row 6 thru 51 in columns M & N.

If you paste this code into an excel worksheet object, you'll see exactly what I'm talking about. It looks really close to hitting the mark. Just a couple more small tweeks, maybe (hopefully).

Any other ideas???

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
On Error GoTo Finished
    If Target.Count > 1 Then End 'This stops it erroring when a range is selected and deleted
    Application.EnableEvents = False 'Turn the events off to prevent infinite loops
        If (Target.Column = 13 Or Target.Column = 14) And (Target.Row > 5 Or Target.Row < 51) And Target.Value <> "" Then
            If Target.Value = "x" Then
            Target = "X"
            GoTo Finished 'Finished, no need to do the next set of instructions
            End If
        Target.ClearContents
        Target.Select
        MsgBox "To restrict WKST query to vehicles with a specific option (e.g. Sunroof), " & _
            "input the letter " & Chr$(34) & "X" & Chr$(34) & " in the appropriate cell row.", _
            vbInformation + vbOKOnly
        End If
Finished:
Application.EnableEvents = True 'Re enable the events for the next entry
End Sub