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

 

[gbrandreth]

Why are you messing around with all this code, why dont you just apply data validation to the cells you want to control, and reject anything that isnt X in those cells.

Thats what its designed for, input control. You seem to be using a sledgehammer to crack a nut.

 

[KP_SoCal]

Why are you messing around with all this code, why dont you just apply data validation to the cells you want to control, and reject anything that isnt X in those cells.

Thats what its designed for, input control. You seem to be using a sledgehammer to crack a nut.

I know it appears that way, but Data Validation is not the way I want to go for too many reasons to mention.

 

[KP_SoCal]

I'm going to start a new post since my current post has deviated so far from my original question. This thread can be closed.

 

[Gary McMaster]

Have you tried disabling the error handler?

Target = "X" looks like a problem to me ... but who knows?

Gary

 

[KP_SoCal]

Nevermind, no need to repost. I figured it out.

This new code fires on an On Workeet_Change event in case anyone is curious. This code gives me much more flexibility than Microsoft's built in Data Validation tool. Thanks for everyone's input. I'm signing off...


Application.EnableEvents = False
If (Target.Column = 13 Or Target.Column = 14) And (Target.Row > 5) And UCase(Target.Value) <> "X" And Target.Value <> "" Then
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
ElseIf (Target.Column = 13 Or Target.Column = 14) And (Target.Row > 5 ) And Target.Value = "x" Then
Target = UCase(Target)
End If
Application.EnableEvents = True

 

[Blade Hunter]

My mistake, change:

And (Target.Row > 5 Or Target.Row < 51)

to

And (Target.Row > 5 AND Target.Row < 51)

Notice the OR gate becomes an AND gate.

Cheers

Dan

 

[KP_SoCal]

I know, I caught it too and was kicking myself that I missed it.

No worries though. Thanks for all your feedback. I couldn't have gotten to the point I did without your input.

 

[Blade Hunter]

No worries, happy to help. No matter which method you ended up going with, there has been some learning happening here .