vba code to make all letters Capitol needs amending to cover whole workbook

[steve400243]

Hello, I use the following VBA to make everything all capitol letters. I am trying to find out how to change this to apply to all the other data that is already in the worksheet? Thanks for all help.

P

rivate Sub Worksheet_Change(ByVal Target As Range)
    Dim cell   As Range
    On Error Resume Next
    Application.EnableEvents = False
    For Each cell In Target
        cell = UCase(cell)
    Next
    Application.EnableEvents = True
End Sub

 

[jim may]

For Each cell In ActiveSheet.UsedRange ' (Maybe...)

 

[steve400243]

For Each cell In ActiveSheet.UsedRange ' (Maybe...)

Thanks Jim, That seemed to work, but there is a lot of screen flickering?

 

[Fluff]

Add

Application.ScreenUpdating = False

to the top of your code

 

[Scott Huish]

Run a separate macro to convert all other cells to capital 1x only that way your Worksheet Change code won't have to run through the entire range every time.

Sub RunOnce()
Dim c As Range
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
    c = UCase(c)
Next
Application.ScreenUpdating = True
End Sub

 

[jim may]

Why not turn off the flickering by using:
Application.Screenupdating = False
Application.Screenupdating = True

 

[steve400243]

Thank you Scott, got it working as needed with your added code.

 

[steve400243]

thank you Fluff

 

[steve400243]

Thanks Jim -