Upper Case VBA Script converting formulas to values

[JohnZ1156]

I have the following VBA Script entered in "View Code" on the sheet tab.
I understand what it's doing, but I'd like to know if there's a better way.

Initially, I was only concerned with converting text cells in Column C to Upper Case. Now, I'd like to enter a formula into a cell within Column C.
The VBA Script converts all of the Text cells in Column C to upper case, the problem is that if I type a formula in a cell in column C, it converts the formula to a value.
I'd like the script to convert text cells to upper case, but at the same time do not convert formulas to their values.
Is there a better way?

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Not Application.Intersect(Target, Range("c:c")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True

End Sub

 

[Joe4]

So, if you are entering a formula, do you want the upper case logic applied or not?
If you do, but you don't want to get rid of the formula, then you will need to add the "Upper" function to the beginning of your formula (either manually when you first enter the formula, or have your VBA code do it).

You can use the "HasFormula" property in an ID statement in your VBA code to first determine whether or not an entry is a formula.
If it is a not a formula, then you can use your existing logic, as-is.
If it is a formula, then you write code to add the "Upper" function to the beginning of your formula.

 

[jasonb75]

This quick edit will ignore any formulas entered into the target range. If you want to convert the results of the formula to upper case then it would be better to apply the UPPER function to the formula, as @Joe4 has mentioned above.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Not Application.Intersect(Target, Range("c:c")) Is Nothing Then
If Not Target(1).HasFormula Then Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True

End Sub

 

[JohnZ1156]

Hi Joe4,
Thanks for your quick response.
I only want the script to "Upper Case" text cells.
Basically, I want it to leave the formula cell alone.
I'm not sure I understand what you mean by using the Upper Function at the beginning of my formula.

Here's my VBA Script which enters the formula:

Range("C" & lr + 21).Select
Worksheets("Sheet1").Range("C" & lr + 21).Formula = "=SUBTOTAL(103,Table2[NUM])"
Sheets("Sheet1").Range("C" & lr + 21).NumberFormat = "#,##0"

 

[JohnZ1156]

JasonB75,
Thank you, that did it !!!

Really appreciate your help.

 

[JohnZ1156]

Now, I need to modify this to an, If Not (condition) Or (condition) Then .....

I would like the code to UpperCase text in Column "C", BUT
Do NOT allow a formula to convert to a value and Do NOT convert only the word "Count" to UpperCase

The code below works for either what is in bold, or what is underlined, but when I try to combine them, I can not seem to get it to do both.

    Application.EnableEvents = False
    If Not Application.Intersect(Target, Range("c:c")) Is Nothing Then
    If Target(1).Value <> "Count" Then Target(1).Value = UCase(Target(1).Value)
    If Not Target(1).HasFormula Then Target(1).Value = UCase(Target(1).Value)
    End If
    Application.EnableEvents = True

I tried something like this, but I'm doing something wrong.

    Application.EnableEvents = False
    If Not Application.Intersect(Target, Range("c:c")) Is Nothing Then
    If Not Target(1).HasFormula Or Target(1).Value <> "Count" Then Target(1).Value = UCase(Target(1).Value)
    End If
    Application.EnableEvents = True

JasonB75, Your answer works great for my original question of not converting the formula to a value, but now I would like it to "ignore" only the text "Count". I hope you're still watching.

 

[RoryA]

You actually want And not Or on that line.

 

[JohnZ1156]

Thank you RoryA.
I thought I tried that, I guess not.
I Googled how to combine 2 conditions and figured out the initial script.
One friggin word. ****, I was SO close.

 

[Joe4]

You should really mark the post that answered your original question as the solution (not your secondary follow-up question).
Then it makes sense when people can quickly read the original question and see the solution to that question listed right under it.

 

[JohnZ1156]

Thanks for the info.
I marked the original post as asnwered.
I really appreciate all the help I receive from this Forum.
You folks (guys and gals) are great !!!