Forum Discussion
List Validation
I have a cell defined through Data Validation as a list with two possible values: blank or "X". That works fine except the user can type "x" in lower case and it passes the validation test. I'm not sure why.
For my education, I'd like to know how to limit their entry to exactly what's in the list. Or perhaps to not allow manual entry at all but to require them to pick from the list.
In this case, however, it would be more user-friendly to allow them to type "x" but then convert that entry to UCASE. I have such a function but I'm not sure where to put it.
Real newbie question: I'm used to a programming language that has a WHEN method (which is triggered when the user enters the field) and a VALID method (which is triggered when they leave it). Does VBA have a similar distinction? I see only the Data Validation feature which, in this case, is busy defining the list. It seems that it won't simultaneously let me trigger my UCASE validation function (like it would if it were Custom).
Thanks for any help.
Hi the follwing code is behind class "Thisworkbook" ("DieseArbeitsmappe"). See attached file.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Select Case Sh.Name
Case "Sheet1"
Select Case Target.Column
Case 3, 4 'Column C+D
If Target.Row > 6 Then
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End If
Case Else
End Select
Case "Sheet2"
'other rules
End Select
End SubRegards
Bernd
http://www.vba-tanker.com - a database full of excel macros
5 Replies
hi,
use the following event. See attached file.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End SubBest regards
Bernd
http://www.vba-tanker.com - a database full of macros
Berndvbatanker Oh, it's attached to the whole sheet but ignored outside of column A because of your first line of code?
Ummm… I was going to attach it to specific cells. But if this is the way to do it, how could I restrict the code to apply to Columns C and D, rows 6+ only?
I need to do something similar on other sheets in the same workbook. So should I attach it to the workbook as a whole and include code in the function so that it is only triggered for specific cells on each sheet? If so, could you show me an example of that in the code, please?
Thanks again.
Hi the follwing code is behind class "Thisworkbook" ("DieseArbeitsmappe"). See attached file.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Select Case Sh.Name
Case "Sheet1"
Select Case Target.Column
Case 3, 4 'Column C+D
If Target.Row > 6 Then
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End If
Case Else
End Select
Case "Sheet2"
'other rules
End Select
End SubRegards
Bernd
http://www.vba-tanker.com - a database full of excel macros
Berndvbatanker Thank you. I see that your code works but I'm a newbie. I don't see how you're attaching your code to those cells. I don't see anything under Data Validation and I don't see any macros at all. What am I missing?
Thanks again.
