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.

 

[Kenneth Hobson]

Reenable events as detailed earlier. For multiple cell changes:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim cell As Range, iRange As Range
  Set iRange = Intersect(Target, Range("C4:C18900"))
  If iRange Is Nothing Then Exit Sub
  Application.EnableEvents = False
  For Each cell In iRange
      cell = UCase(cell)
  Next cell
  Application.EnableEvents = True
End Sub

 

[David_UK]

Thanks Ken, however, I decided to ditch the ranged cells because I have many columns that I need this function to work on, can you redo this function so that any cell when changed within the worksheet is changed this happens? Or will this do it?

Here was the original.

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

thanks,

David.

 

[AD_Taylor]

Just remove the top bit that checks the cell range

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range

Application.EnableEvents = False

For Each cell In Target.Cells
cell = UCase(cell)   
Next cell   
Application.EnableEvents = True 

End Sub

 

[David_UK]

Doesn't seem to be working at all now...

 

[Kenneth Hobson]

Adam's code should work. Put this into the Immediate window and press Enter at the end.

Application.EnableEvents = True

And this code in the sheet's code of course.

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