Amend a data validation list code to accept lowercase letters?

[danbates]

Hi,

Here is my code:

With ActiveCell.Validation

.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="D, E, F, G, H, J, K, L, M, N, P"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True

End With

Does anyone know how or if it can be amended so it allows lowercase letters to be accepted?

I only want the uppercase letters to show in the dropdown list.

Hope it makes sense.

Thanks

Dan

 

[Scott Huish]

Since you are doing this with code, you could have it not stop on the error, but couple it with Change event code to check the data entered.

Why are you using Activecell as opposed to specifying a range for this?

The code could be something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo endsub
Dim c As Range, u, t
For Each c In Target
    If c.Validation.Type = 3 Then u = Split(c.Validation.Formula1, ",")
    For Each t In u
        If UCase(c) = Trim(t) Then
            Application.EnableEvents = False
            c = UCase(c)
            Application.EnableEvents = True
            Exit Sub
        End If
Next
Rem ERROR MESSAGE
MsgBox "Incorrect Value Entered"
Application.EnableEvents = False
c.ClearContents
c.Select
Application.EnableEvents = True
Next
endsub:
End Sub

 

[danbates]

Hi Scott,

Thank you for your reply.

The code I have is one I found online. It does need altering to just work in column E but I wanted to get the code working to my needs before changing the range.

I've copied your code into a new workbook but unfortunately I can't get it to work. It comes up with this error:
Runtime error 1004 - Application defined or object defined error and it highlights

[COLOR=#574123] If c.Validation.Type = 3 Then u[/COLOR]

Thanks again

Dan

 

[Scott Huish]

I did edit my post, does the code you copied include the On Error line and the endsub: line?

The line you indicated checks for a validation list in the cell.