VBA Tutorial Login  

Free! Excel VBA Online Tutorial

   

Excel VBA

Convert Text and Cells with Formulas to UpperCase with VBA

User Rating:  / 4
PoorBest 
Details
 
Convert to Uppercase - Excel VBA Codes:
 
To insert worksheet vba code, 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 vba code
 
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
 
If Target.Column = 1 Then
'This check prevents the procedure from repeatedly calling itself (ie. recursing)
 If Not Target.Text = UCase(Target.Text) Then
'Mid(Target.Formula, 2): eliminates “=” from the formula text
If Target.HasFormula = True Then
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 vba code
 
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
 
With Target
 
If .Column = 1 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 and Cells with Formulas to UpperCase - worksheet vba code
 
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
 
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
'Right(.Formula, Len(.Formula) - 1): eliminates “=” from the formula text
If .HasFormula = True Then
.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 - worksheet vba code
 
Sub uppercase()
'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
 
Dim Cell As Range
 
For Each Cell In Selection.Cells
If Not Cell.HasFormula Then
'Alternatively: "If Cell.HasFormula = False Then"
Cell = UCase(Cell)
End If
Next
 
End Sub
 

 
   

Bottom Ad

   
   
© 2014 GlobaliConnect.com. All rights reserved.