Formula = "=UPPER(" & Right(.Formula, Len(.Formula) - 1) & ")" 'Alternately: '.Formula = "=UPPER(" & Mid(.Formula, 2) & ")" 'Mid(Target.Formula, 2): eliminates “=” from the formula text Else. HasFormula = True Then 'Right(.Formula, Len(.Formula) - 1): eliminates “=” from the formula text.
CONVERT TEXT TO UPPERCASE EXCEL 2010 CODE
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.
worksheet Change Event) are installed with the worksheet ie. Value = UCase(.Value) End If End If End If End With End SubĬonvert 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. Value UCase(.Value) Then 'for any change in column A If. Column = 1 Then 'this check prevents the procedure from repeatedly calling itself (ie. the code must be placed in the code module of the appropriate Sheet object With Target If. 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. 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 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.
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.Ĭonvert Text and Cells with Formulas to UpperCase - worksheet event procedure
0 kommentar(er)
