How to add cell range that is excluded from VB script

  • Thread starter Thread starter AA Arens
  • Start date Start date
A

AA Arens

I do have a code that automatically make the text capitalized.


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Error_handler
With Target
If Not .HasFormula Then
Application.EnableEvents = False
If Target.Row = 10 Then Target.Value = UCase(Target.Value)
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End If
End With

Error_handler:
Resume Next

End Sub

How to add a range of cells that is excluded from this cript?

Bart
 
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "10:10,A11,B12,H19:M22" '<=== change to suit

On Error GoTo Error_handler
Application.EnableEvents = False

With Target
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If Not Target.HasFormula Then
Target.Value = UCase(Target.Value)
End If
End If
End With

Error_handler:
Application.EnableEvents = True
End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "10:10,A11,B12,H19:M22" '<=== change to suit

On Error GoTo Error_handler
Application.EnableEvents = False

With Target
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If Not Target.HasFormula Then
Target.Value = UCase(Target.Value)
End If
End If
End With

Error_handler:
Application.EnableEvents = True
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...

Thanks, it works.
 
Back
Top