Forum Discussion

bvelke's avatar
bvelke
Brass Contributor
Jun 04, 2019
Solved

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...
excel
Macros and VBA
  • Berndvbatanker's avatar
    Berndvbatanker
    Jun 04, 2019

    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

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

Berndvbatanker's avatar
Berndvbatanker
Iron Contributor
Jun 04, 2019

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

http://www.vba-tanker.com - a database full of macros

bvelke's avatar
bvelke
Brass Contributor
Jun 04, 2019

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.