-
1 Attachment(s)
UCase Help
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?
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
Code:
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
-
1 Attachment(s)
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
Code:
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
-
1 Attachment(s)
Unsolicited comment
I think you could simplify the code
ALso, you don't have a dropdown for school name
Code:
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
-
Thanks so much for the help. The spreadsheet is working perfectly!