jmbarnes65
04-09-2021, 04:52 PM
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
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