Keep All Caps when event code changes it to Proper Case

[still learning]

Hi

I use the following event macro to change what I type in C:C to Proper Case

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
Application.EnableEvents = False
Target.Value = WorksheetFunction.Substitute(WorksheetFunction.Proper(WorksheetFunction.Substitute(Target, "'", "z-z")), "z-Z", "'")
Application.EnableEvents = True
End If
End Sub

Is there something I can add to it for when I want to have it in all caps?


Mike

 

[Joe4]

I think you need some way to distinguish/communicate with Excel when you want things in Proper case versus when you want them all Caps.
You could have your code pop open an Input Box asking them to choose which one they want, or perhaps have another column in that row contain information that tells Excel which one to choose.

 

[still learning]

Hi Joe4,
I didn't think of that. I could put an Icon on the ribbon and have it override the event code
BUT, I don't know how to write the macro.
How would I stop the event macro from doing anything to that cell only.
I could probably do the opposite...
not have an event macro and use an icon on the ribbon to change the cell to proper case BUT. there are only a very few times that i need to have all caps

mike

 

[Joe4]

Why not just create a macro (that you could assign to an icon on your ribbon) that changes it to all caps?
So you can click on this icon and run it "after the fact" to change any of those cells to all caps.

The code would look like this:

Sub ConvertToAllCaps()
'   Converts the active cell to all caps
    Application.EnableEvents = False
    ActiveCell.Value = UCase(ActiveCell.Value)
    Application.EnableEvents = True
End Sub

And then you would just add a button to a ribbon, and assign this code that that button.

 

[still learning]

Hi Joe 4,
Yep. That's exactly what I needed.
I couldn't figure out how to keep the event macro from changing the upper case back to proper case.
I didn't know to use ....EnableEvents = False

That will go in my macro help workbook

Thank you

mike

 

[Joe4]

You are welcome!
Glad I was able to help.

Yep, temporarily setting Event to "False" disables all event code, so the Worksheet_Change code won't get called by that change.
Then we just reset it, turn them back on after our change.

 

[still learning]

thanks again
I never understood what Application.EnableEvents... meant.

mike

 

[Joe4]

thanks again
I never understood what Application.EnableEvents... meant.

mike

You are welcome.
Now you know!

It is quite powerful, as you can control when automated code runs and when you don't want it to.
You also need to sometimes know to prevent certain code from getting caught in an endless loop, i.e. Worksheet_Change code runs when a cell is updated, but if the code you have in there is updating cells, it will cause the code to call itself, which is some circumstances, could lead to an infinite loop! So often times, you want to temporarily disable the event calling while you are changing a cell.