Convert Cell contents all to uppercase on exit

[David_UK]

I have a workbook that I want to either, have the cells I enter in the range to be all formatted to uppercase if text, or that when the book is saved, it converts all cells within specific ranges to uppercase when saved.

Either way, before the book is saved, I want it to convert so for example,

Column A is all dates,
Column B is codes, i.e. c01-ret356-eet-01
Column C is say a name i.e. David

So for columns B and C either on leaving the cell via enter or other means the cell will auto uppercase, or when the workbook is saved, it will ask the user to accept the changes...

I prefer the first choice if easier.

thanks,

David.

 

[njimack]

Paste the following into the module for the sheet in question...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End Sub

 

[David_UK]

Thanks, how do I denote each range or do I not need too?

 

[njimack]

The code I posted will convert any cell that changes. To restrict it to certain columns, do something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 2 Or Target.Column = 3 Then
Target = UCase(Target)
End If
Application.EnableEvents = True
End Sub

 

[David_UK]

Perfect, will try that, just one last thing, what if I wanted to restrict a range,

say, C4:C18900

Sorry...

 

[njimack]

Perfect, will try that, just one last thing, what if I wanted to restrict a range,

say, C4:C18900

Sorry...

No problem...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("C4:C18900")) Is Nothing Then
    Target = UCase(Target)
End If
Application.EnableEvents = True
End Sub

 

[David_UK]

Thanks very much.

Great help.

David.

 

[David_UK]

Apart from, it's no longer working at all now? Any ideas why? none of them work now?

 

[njimack]

Oops!
First of all, you'll need to re-enable events. Press Alt+F11 and then press Ctrl+G to display the Immediate window. Enter the following code and press Enter:

Application.EnableEvents = True

Then replace the code I gave you earlier with the below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C4:C18900")) Is Nothing Then
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
End If
End Sub

 

[David_UK]

Hi Mack,

I've just run into a slight issue, I've decided to use the first piece of code you sent for me, which checks the whole sheet I believe.

However, I've hit a stumbling, block, or done something to break it.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End Sub

Is the code, and what I did was to copy and paste a couple of columns of data from another sheet to this one so that I could use the functionality. however I got a

Type Mismatch error message and now it's stopped working?

Any ideas?

Thanks,

David.