Home / Excel VBA / Convert Text and Cells with Formulas to UpperCase with VBA

Convert Text and Cells with Formulas to UpperCase with VBA

 
Convert to Uppercase – Excel VBA Codes:
 
To insert vba code for worksheet event procedures, right click on the Worksheet name tab, select View Code and paste the code into the Code Window that appears at right.
 
Convert Text and Cells with Formulas to UpperCase – worksheet event procedure
 
Private Sub Worksheet_Change(ByVal Target As Range)
‘Forces any cell entry (text or formula) in the target column to be UPPER case
‘Target is an argument which refers to the range being changed
‘worksheet event procedures (ex. worksheet Change Event) are installed with the worksheet ie. the code must be placed in the code module of the appropriate Sheet object
 
‘for any change in column A 
If Target.Column = 1 Then
‘this check prevents the procedure from repeatedly calling itself (ie. recursing)
 If Not Target.Text = UCase(Target.Text) Then
If Target.HasFormula = True Then
‘Mid(Target.Formula, 2): eliminates “=” from the formula text
Target.Formula = “=UPPER(” & Mid(Target.Formula, 2) & “)”
Else
Target = UCase(Target.Text)
End If
End If
End If
 
End Sub
 
 
Convert Text and Cells with Formulas to UpperCase – worksheet event procedure
 
Private Sub Worksheet_Change(ByVal Target As Range)
‘Forces any cell entry (text or formula) in the target column to be UPPER case
‘Target is an argument which refers to the range being changed
‘worksheet event procedures (ex. worksheet Change Event) are installed with the worksheet ie. the code must be placed in the code module of the appropriate Sheet object
 
With Target
If .Column = 1 Then
‘this check prevents the procedure from repeatedly calling itself (ie. recursing)
If .Value <> UCase(.Value) Then
‘for any change in column A
If .HasFormula = True Then
‘Right(.Formula, Len(.Formula) – 1): eliminates “=” from the formula text
.Formula = “=UPPER(” & Right(.Formula, Len(.Formula) – 1) & “)”
‘Alternately:
‘.Formula = “=UPPER(” & Mid(.Formula, 2) & “)”
‘Mid(Target.Formula, 2): eliminates “=” from the formula text
Else
.Value = UCase(.Value)
End If
End If
End If
End With
 
End Sub
 
 
Convert Text and Cells with Formulas to UpperCase – worksheet event procedure
 
Private Sub Worksheet_Change(ByVal Target As Range)
‘Forces any cell entry (text or formula) in the target range to be UPPER case
‘Target is an argument which refers to the range being changed
‘Change A1:C10 to the desired range
‘worksheet event procedures (ex. worksheet Change Event) are installed with the worksheet ie. the code must be placed in the code module of the appropriate Sheet object
 
With Target
If Not Application.Intersect(Target, Range(“A1:C10”)) Is Nothing Then
‘this check prevents the procedure from repeatedly calling itself (ie. recursing)
If .Value <> UCase(.Value) Then
If .HasFormula = True Then
‘Right(.Formula, Len(.Formula) – 1): eliminates “=” from the formula text
.Formula = “=UPPER(” & Right(.Formula, Len(.Formula) – 1) & “)”
‘Alternately:
‘.Formula = “=UPPER(” & Mid(.Formula, 2) & “)”
‘Mid(Target.Formula, 2): eliminates “=” from the formula text
Else
.Value = UCase(.Value)
End If
End If
End If
End With
 
End Sub
 
 
Convert Text to UpperCase – vba code
 
Sub Uppercase1()
‘To convert the selected range to UpperCase, only if it is text and not formula
‘Use “LCase” instead of “UCase” to convert from UpperCase to LowerCase
‘not being a worksheet event procedure, this macro can be placed in a standard module & on execution will convert the selected range to UpperCase
 
Dim Cell As Range
For Each Cell In Selection.Cells
If Not Cell.HasFormula Then
Cell = UCase(Cell)
End If
Next
 
End Sub
 
 
 

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top