All Caps Code Help

[motherteresa]

I have this All Caps code which works great. But, it also turns all emails on the worksheet into caps, which I don't want.

Question:
Is there a way to have it work on everything except emails and web addresses?

or

Is there a way to have it work on everything except certain cells?


Thank you Ex(cel)perts!!

 

[Michael M]

Maybe you should post the code you have, and then provide the criteria you need to turn off all caps.

 

[motherteresa]

Oh...duh. Blonde moment. I had it copied and then forgot to paste

Here it is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
With Application
.EnableEvents = False
Target = UCase(Target)
.EnableEvents = True
End With
End Sub

 

[Smitty]

You might be able to do something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
    With Application
        .EnableEvents = False
            If InStr(Target.Value, "@") Then
            Else: Target = UCase(Target)
            End If
            .EnableEvents = True
    End With
End Sub

HTH,

 

[motherteresa]

WOW, guys. You're quick. Thanks! Worked like a charm. Thanks Smitty!!!

 

[motherteresa]

Oh well...it got complicated. Thanks in advance for any further help.

In some of the sheets it works. In other sheets it doesn't, because I already have another worksheet macro in place it seems to be in conflict with.

So, is there a way to just apply it to the whole workbook, or is there a way to incorporate it into a worksheet that has the conflicting macro?

Here's the other macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, MyRange As Range
If Intersect(Target, Range("C:C, E:E")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Select Case Target.Column
Case 5
If Target > 0 Then Target = -Target
Case 3
If Target < 0 Then Target = -Target
End Select
Application.EnableEvents = True
End Sub

 

[djreiswig]

Not sure what the Dim's are for as the variables aren't use in the code you supplied, but this should do what you want. I don't usually use Exit Sub, so I changed the If...Then's around a bit.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range, MyRange As Range
    
    If Target.Cells.Count = 1 Then
        On Error Resume Next
        Application.EnableEvents = False
        If InStr(Target.Value, "@") = 0 Then
            Target = UCase(Target)
        End If
    
        If Not Intersect(Target, Range("C:C, E:E")) Is Nothing Then
            Select Case Target.Column
                Case 5
                    If Target > 0 Then Target = -Target
                Case 3
                    If Target < 0 Then Target = -Target
            End Select
        End If
        Application.EnableEvents = True
    End If
End Sub

 

[motherteresa]

Up late happy dancing over here. Y'all are so helpful!! Thanks Smitty and djreiswig. That looks like it's a winner.

 

[motherteresa]

Help Smitty and djreiswig (or anyone)!! That combo code seems to be wiping out formulas embedded in the cells.

For instance, when I change the value of any cell or edit it's formula, any formula is wiped out and all that is left is the value.

When I take the macro out, it stops doing that. Can you offer any assistance?

 

[Smitty]

You could add:

If Target.HasFormula Then Exit Sub

HTH,