UCase Help

[jmbarnes65]

I've created a spreadsheet for users to enter information about students. I've been asked to automatically reformat all text to UPPERCASE. I found the following code to do that but I don't know enough about VBA to insert it into my code. I've pasted my Project Explorer view below. Can someone kindly point out how to insert this into my existing code?



Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = VBA.UCase(Target.Value)
End Sub


Here is my existing code which works just fine thanks to assistance from this forum :yes


Private Sub Worksheet_Change(ByVal Target As Range)
Set myRng = Intersect(Columns(1), Target)
If Not myRng Is Nothing Then
For Each cll In myRng.Cells
selectedNum = Application.VLookup(cll.Value, Worksheets("Dropdowns").Range("counties2"), 2, False)
If Not IsError(selectedNum) Then
cll.Value = selectedNum
End If
Next cll
End If


'Code for Student Home Language (Column 13)
Set myRng = Intersect(Columns(13), Target)
If Not myRng Is Nothing Then
For Each cll In myRng.Cells
selectedNum = Application.VLookup(cll.Value, Worksheets("Dropdowns").Range("Language"), 2, False)
If Not IsError(selectedNum) Then
cll.Value = selectedNum
End If
Next cll
End If


'Code for Active Student? (Column 19)
Set myRng = Intersect(Columns(19), Target)
If Not myRng Is Nothing Then
For Each cll In myRng.Cells
selectedNum = Application.VLookup(cll.Value, Worksheets("Dropdowns").Range("Active"), 2, False)
If Not IsError(selectedNum) Then
cll.Value = selectedNum
End If
Next cll
End If


'Code for Instruction Type by Month Columns 20-29 (Note that I used Range option
Set myRng = Intersect(Target, Range("T:AC"))
'Set myRng = Intersect(Columns(20), Columns(29)), Target)
If Not myRng Is Nothing Then
For Each cll In myRng.Cells
selectedNum = Application.VLookup(cll.Value, Worksheets("Dropdowns").Range("InstructionType"), 2, False)
If Not IsError(selectedNum) Then
cll.Value = selectedNum
End If
Next cll
End If

[Paul_Hossler]

1. I added CODE tags to your first post

2. Continuing your approach, look at the <<<<<<<<<<<< lines

3. Not totally sure about the columns to upper case, but you just need to tweak my ranges




Private Sub Worksheet_Change(ByVal Target As Range)


Application.EnableEvents = False ' <<<<<<<<<<<<<<<<<<<<<<<<<<


Set myRng = Intersect(Columns(1), Target)
If Not myRng Is Nothing Then
For Each cll In myRng.Cells
selectedNum = Application.VLookup(cll.Value, Worksheets("Dropdowns").Range("counties2"), 2, False)
If Not IsError(selectedNum) Then
cll.Value = selectedNum
End If
Next cll
End If


'Code for Student Home Language (Column 13)
Set myRng = Intersect(Columns(13), Target)
If Not myRng Is Nothing Then
For Each cll In myRng.Cells
selectedNum = Application.VLookup(cll.Value, Worksheets("Dropdowns").Range("Language"), 2, False)
If Not IsError(selectedNum) Then
cll.Value = selectedNum
End If
Next cll
End If


'Code for Active Student? (Column 19)
Set myRng = Intersect(Columns(19), Target)
If Not myRng Is Nothing Then
For Each cll In myRng.Cells
selectedNum = Application.VLookup(cll.Value, Worksheets("Dropdowns").Range("Active"), 2, False)
If Not IsError(selectedNum) Then
cll.Value = selectedNum
End If
Next cll
End If


'Code for Instruction Type by Month Columns 20-29 (Note that I used Range option
Set myRng = Intersect(Target, Range("T:AC"))
If Not myRng Is Nothing Then
For Each cll In myRng.Cells
selectedNum = Application.VLookup(cll.Value, Worksheets("Dropdowns").Range("InstructionType"), 2, False)
If Not IsError(selectedNum) Then
cll.Value = selectedNum
End If
Next cll
End If


'Code for making things uppercase <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Set myRng = Intersect(Target, Range("B:E"))
If Not myRng Is Nothing Then
For Each cll In myRng.Cells
cll.Value = UCase(cll.Value)
Next cll
End If


Set myRng = Intersect(Target, Range("G:J"))
If Not myRng Is Nothing Then
For Each cll In myRng.Cells
cll.Value = UCase(cll.Value)
Next cll
End If




Set myRng = Intersect(Target, Range("Q:R"))
If Not myRng Is Nothing Then
For Each cll In myRng.Cells
cll.Value = UCase(cll.Value)
Next cll
End If




Application.EnableEvents = True ' <<<<<<<<<<<<<<<<<<<<<<<<<<


End Sub

[Paul_Hossler]

Unsolicited comment

I think you could simplify the code

ALso, you don't have a dropdown for school name




Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim ws As Worksheet
Dim v As Variant

Set ws = Worksheets("Dropdowns")

Application.EnableEvents = False


For Each rCell In Target.Cells
With rCell
If .Row < 3 Then GoTo NextCell

Select Case .Column
Case 1 ' county
v = Application.VLookup(.Value, ws.Range("counties2"), 2, False)
If Not IsError(v) Then .Value = v

Case 13 ' Student Home Language
v = Application.VLookup(.Value, ws.Range("Language"), 2, False)
If Not IsError(v) Then .Value = v

Case 19 ' Active Student?
v = Application.VLookup(.Value, ws.Range("Active"), 2, False)
If Not IsError(v) Then .Value = v

Case 20 To 29 ' Instruction Type by Month
v = Application.VLookup(.Value, ws.Range("InstructionType"), 2, False)
If Not IsError(v) Then .Value = v

Case 2 To 5, 7 To 10, 17, 18
If Len(.Value) > 0 Then .Value = UCase(.Value)
End Select
End With
NextCell:
Next


Application.EnableEvents = True


End Sub

[jmbarnes65]

Thanks so much for the help. The spreadsheet is working perfectly!