Jun 04 2019 04:33 AM
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.
Jun 04 2019 05:13 AM
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
Jun 04 2019 05:24 AM
@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.
Jun 04 2019 05:33 AM
@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.
Jun 04 2019 06:48 AM - edited Jun 04 2019 06:49 AM
SolutionHi 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
Jun 05 2019 08:22 AM
@Berndvbatanker That's great. I learned a lot from that code example. Thanks!
Jun 04 2019 06:48 AM - edited Jun 04 2019 06:49 AM
SolutionHi 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