# Excel VBA

## Convert Text and Cells with Formulas to UpperCase with VBA

Poor Best

- Details
- Category: Excel VBA

**Convert to Uppercase - Excel VBA Codes:**

*To insert vba code for***, right click on the Worksheet name tab, select View Code and paste the code into the Code Window that appears at right.***worksheet event procedures***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) ThenIf Target.HasFormula = True Then'Mid(Target.Formula, 2): eliminates “=” from the formula textTarget.Formula = "=UPPER(" & Mid(Target.Formula, 2) & ")"ElseTarget = UCase(Target.Text)End IfEnd IfEnd 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 TargetIf .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 textElse.Value = UCase(.Value)End IfEnd IfEnd IfEnd 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 TargetIf 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) ThenIf .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 textElse.Value = UCase(.Value)End IfEnd IfEnd IfEnd 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 RangeFor Each Cell In Selection.CellsIf Not Cell.HasFormula ThenCell = UCase(Cell)End IfNext

**End Sub**