VBA Code to force UpperCase in cells.

[StuartUK]

Morning all
Hopefully a nice easy one.....

When a user enters letters into a cell, i want it so that if they forget to type in caps, it automatically changes the text to UPPERCASE
specifically between this range: B5:BK16

I had the code which i found looking through forums, but deleted the code and cant remember it! doh!
any help appreciated

 

[Nasmin Saheed]

I'm only considering a single sheet, but that code has some drawbacks, including.

1. It will error (and probably leave 'events' disabled) if more than one cell is changed at a time (eg Select 2 cells & press Delete, enter multiple cells with Ctrl+Enter, Copy/Paste multiple cells.

2. It will convert some dates entered in a cell to a text value. (eg enter 15/05/2011)

I'm not sure I have everything covered but this should be considerably more robust.

Private Sub Worksheet_Change(ByVal Target As Range)
****Dim changed As Range, c As Range
****Dim cVal

****Const myR As String = "B5:BK16,D3,G20:H22" '<- Your range(s)
****
****Set changed = Intersect(Target, Range(myR))
****If Not changed Is Nothing Then
********Application.EnableEvents = False
********For Each c In changed
************cVal = c.Value
************Select Case True
****************Case IsEmpty(cVal), IsNumeric(cVal), _
************************IsDate(cVal), IsError(cVal)
********************' Do nothing
****************Case Else
********************c.Value = UCase(cVal)
************End Select
********Next c
********Application.EnableEvents = True
****End If
End Sub

Thanks a lot for this code.... but if i do UNDO (Ctrl + Z) it is not working ... could you plz let me know what code needs to be added to work undo as well. And after which line this new code to be added

 

[DavidWF]

I'm sorry Nasmin but my VBA knowledge is probably at a similar level to yours.
Peter - if you're still monitoring this thread, can you assist?
Or can anyone else assist Nasmin?

 

[Michael M]

This thread is almost 7 years old.
BUT
you can't undo what is done via a macro !!
You would need to write an entirely new macro !

 

[Nasmin Saheed]

This thread is almost 7 years old.
BUT
you can't undo what is done via a macro !!
You would need to write an entirely new macro !

Yes, it is almost 7 years .. but it is a very good code ... only this undo it not working .... if possible could u plz assist me for new code

 

[footoo]

Assuming you want to convert to lower case, the following will convert selected cell(s).
Run it in a normal module.

Sub Undo_Capitals()
Dim c As Range
Application.EnableEvents = False
For Each c In Selection
    Select Case True
    Case IsEmpty(c), IsNumeric(c), _
         IsDate(c), IsError(c)
        ' Do nothing
    Case Else
        c.Value = LCase(c)
    End Select
Next c
Application.EnableEvents = True
End Sub