How to add cell range that is excluded from VB script

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AA Arens - 24 Jul 2007 08:31 GMT

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

Reply to this Message

Bob Phillips - 24 Jul 2007 08:51 GMT

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

Signature

HTH

Bob

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

>I do have a code that automatically make the text capitalized.
>
[quoted text clipped - 18 lines]
>
> Bart

Reply to this Message

AA Arens - 24 Jul 2007 12:18 GMT

> Private Sub Worksheet_Change(ByVal Target As Range)
> Const WS_RANGE As String = "10:10,A11,B12,H19:M22" '<=== change to suit
[quoted text clipped - 43 lines]
>
> > Bart

Thanks, it works.

Reply to this Message