Excel VBA

Convert Text and Cells with Formulas to UpperCase with VBA

User Rating:  / 3
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
 If Not Target.Text = UCase(Target.Text) Then
'This check prevents the procedure from repeatedly calling itself (ie. recursing)
If Target.HasFormula = True Then
Target.Formula = "=UPPER(" & Mid(Target.Formula, 2) & ")"
'Mid(Target.Formula, 2): eliminates “=” from the formula text
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
If .Value <> UCase(.Value) Then
'This check prevents the procedure from repeatedly calling itself (ie. recursing)
If .HasFormula = True Then
.Formula = "=UPPER(" & Right(.Formula, Len(.Formula) - 1) & ")"
'Alternately: .Formula = "=UPPER(" & Mid(.Formula, 2) & ")"
'Right(.Formula, Len(.Formula) - 1): eliminates “=” from the formula text
'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
If .Value <> UCase(.Value) Then
'This check prevents the procedure from repeatedly calling itself (ie. recursing)
If .HasFormula = True Then
.Formula = "=UPPER(" & Right(.Formula, Len(.Formula) - 1) & ")"
'Alternately: .Formula = "=UPPER(" & Mid(.Formula, 2) & ")"
'Right(.Formula, Len(.Formula) - 1): eliminates “=” from the formula text
'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
 

 
 
Excel Formulas Used:
 
 
InStr {VBA Function}:
Returns the position of the first occurrence of a string in another string.
Syntax: InStr( [start], string1, string2, [compare] )
- start (Integer)   is optional. It is the starting position for the search. If this parameter is omitted, the search will begin at position 1.
- string1   is the string that will be searched (String To Search).
- string2   is the string to search for (String To Find).
- compare   is optional. This is the type of comparison to perform. Default is 0. The choices are:
vbUseCompareOption  ->  -1  ->  Uses option compare
vbBinaryCompare  ->  0  ->  Binary comparison
vbTextCompare  ->  1  ->  Textual comparison
vbDatabaseCompare  ->  2  ->  Comparison based on your database
 
 
UPPER {Excel Function}:
Converts text to uppercase.
Syntax: UPPER (Text)
- Text   is the text you want converted to uppercase. Text can be a reference or text string.
 
 
UCase {VBA Function}:
UCase function converts a string to all upper-case.
Syntax: UCase (Text)
- Text   is the string you want converted to uppercase.
 
 
MID {Excel and VBA Function}:
MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.
Syntax: MID(text,start_num,num_chars)
- Text   is the text string containing the characters you want to extract.
- Start_num   is the position of the first character you want to extract in text. The first character in text has start_num 1, and so on.
- Num_chars   specifies the number of characters you want MID to return from text. Cannot be omitted in the excel function.
- The Mid function can be used in VBA code. Omitting the num_chars parameter in vba will return all characters after the start position (start_num).
   

Bottom Ad

   
   
© 2014 GlobaliConnect.com. All rights reserved.