SOLVED
Home

List Validation

%3CLINGO-SUB%20id%3D%22lingo-sub-667054%22%20slang%3D%22en-US%22%3EList%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-667054%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20cell%20defined%20through%20Data%20Validation%20as%20a%20list%20with%20two%20possible%20values%3A%20blank%20or%20%22X%22.%26nbsp%3B%20That%20works%20fine%20except%20the%20user%20can%20type%20%22x%22%20in%20lower%20case%20and%20it%20passes%20the%20validation%20test.%26nbsp%3B%20I'm%20not%20sure%20why.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20my%20education%2C%20I'd%20like%20to%20know%20how%20to%20limit%20their%20entry%20to%20exactly%20what's%20in%20the%20list.%26nbsp%3B%20Or%20perhaps%20to%20not%20allow%20manual%20entry%20at%20all%20but%20to%20require%20them%20to%20pick%20from%20the%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20case%2C%20however%2C%20it%20would%20be%20more%20user-friendly%20to%20allow%20them%20to%20type%20%22x%22%20but%20then%20convert%20that%20entry%20to%20UCASE.%26nbsp%3B%20I%20have%20such%20a%20function%20but%20I'm%20not%20sure%20where%20to%20put%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReal%20newbie%20question%3A%20I'm%20used%20to%20a%20programming%20language%20that%20has%20a%20WHEN%20method%20(which%20is%20triggered%20when%20the%20user%20enters%20the%20field)%20and%20a%20VALID%20method%20(which%20is%20triggered%20when%20they%20leave%20it).%26nbsp%3B%20Does%20VBA%20have%20a%20similar%20distinction%3F%26nbsp%3B%20I%20see%20only%20the%20Data%20Validation%20feature%20which%2C%20in%20this%20case%2C%20is%20busy%20defining%20the%20list.%26nbsp%3B%20It%20seems%20that%20it%20won't%20simultaneously%20let%20me%20trigger%26nbsp%3Bmy%20UCASE%26nbsp%3Bvalidation%20function%20(like%20it%20would%20if%20it%20were%20Custom).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-667054%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-667164%22%20slang%3D%22en-US%22%3ERe%3A%20List%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-667164%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347015%22%20target%3D%22_blank%22%3E%40bvelke%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehi%2C%3C%2FP%3E%3CP%3Euse%20the%20following%20event.%20See%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EIf%20Target.Column%20%26lt%3B%26gt%3B%201%20Then%20Exit%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3ETarget.Value%20%3D%20UCase(Target.Value)%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.vba-tanker.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-tanker.com%3C%2FA%3E%20-%20a%20database%20full%20of%20macros%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-667198%22%20slang%3D%22en-US%22%3ERe%3A%20List%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-667198%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347933%22%20target%3D%22_blank%22%3E%40Berndvbatanker%3C%2FA%3E%26nbsp%3BThank%20you.%26nbsp%3B%20I%20see%20that%20your%20code%20works%20but%20I'm%20a%20newbie.%26nbsp%3B%20I%20don't%20see%20how%20you're%20attaching%20your%20code%20to%20those%20cells.%26nbsp%3B%20I%20don't%20see%20anything%20under%20Data%20Validation%20and%20I%20don't%20see%20any%20macros%20at%20all.%26nbsp%3B%20What%20am%20I%20missing%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-667229%22%20slang%3D%22en-US%22%3ERe%3A%20List%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-667229%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347933%22%20target%3D%22_blank%22%3E%40Berndvbatanker%3C%2FA%3E%26nbsp%3BOh%2C%20it's%20attached%20to%20the%20whole%20sheet%20but%20ignored%20outside%20of%20column%20A%20because%20of%20your%20first%20line%20of%20code%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUmmm%E2%80%A6%20I%20was%20going%20to%20attach%20it%20to%20specific%20cells.%26nbsp%3B%20But%20if%20this%20is%20the%20way%20to%20do%20it%2C%20how%20could%20I%20restrict%20the%20code%20to%20apply%20to%20Columns%20C%20and%20D%2C%20rows%206%2B%20only%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20do%20something%20similar%20on%20other%20sheets%20in%20the%20same%20workbook.%26nbsp%3B%20So%20should%20I%20attach%20it%20to%20the%20workbook%20as%20a%20whole%20and%20include%20code%20in%20the%20function%20so%20that%20it%20is%20only%20triggered%20for%20specific%20cells%20on%20each%20sheet%3F%26nbsp%3B%20If%20so%2C%20could%20you%20show%20me%20an%20example%20of%20that%20in%20the%20code%2C%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-667497%22%20slang%3D%22en-US%22%3ERe%3A%20List%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-667497%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347015%22%20target%3D%22_blank%22%3E%40bvelke%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20the%20follwing%20code%20is%20behind%20class%20%22Thisworkbook%22%20(%22DieseArbeitsmappe%22).%20See%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EPrivate%20Sub%20Workbook_SheetChange(ByVal%20Sh%20As%20Object%2C%20ByVal%20Target%20As%20Range)%3CBR%20%2F%3E%3CBR%20%2F%3ESelect%20Case%20Sh.Name%3CBR%20%2F%3ECase%20%22Sheet1%22%3CBR%20%2F%3E%3CBR%20%2F%3ESelect%20Case%20Target.Column%3CBR%20%2F%3ECase%203%2C%204%20'Column%20C%2BD%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Target.Row%20%26gt%3B%206%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3ETarget.Value%20%3D%20UCase(Target.Value)%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ECase%20Else%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Select%3CBR%20%2F%3E%3CBR%20%2F%3ECase%20%22Sheet2%22%3CBR%20%2F%3E'other%20rules%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Select%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.vba-tanker.com%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-tanker.com%3C%2FA%3E%20-%20a%20database%20full%20of%20excel%20macros%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-671490%22%20slang%3D%22en-US%22%3ERe%3A%20List%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-671490%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347933%22%20target%3D%22_blank%22%3E%40Berndvbatanker%3C%2FA%3E%26nbsp%3BThat's%20great.%26nbsp%3B%20I%20learned%20a%20lot%20from%20that%20code%20example.%26nbsp%3B%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E
bvelke
Occasional Contributor

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.

5 Replies

@bvelke 

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 Sub

 

Best regards

Bernd

www.vba-tanker.com - a database full of 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.

@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.

Solution

@bvelke 

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 Sub

 

Regards

Bernd

www.vba-tanker.com - a database full of excel macros

@Berndvbatanker That's great.  I learned a lot from that code example.  Thanks!

Related Conversations