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

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B5:BK16")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End Sub

 

[SuperFerret]

Hi Peter,

Is this easy enough to apply to multiple ranges on the one worksheet (Or in one workbook over various sheets)? I might require this for an ongoing project but on some sheets their may be multiple ranges.

Thanks,

SF

 

[VoG]

Hi Peter,

Is this easy enough to apply to multiple ranges on the one worksheet (Or in one workbook over various sheets)? I might require this for an ongoing project but on some sheets their may be multiple ranges.

Thanks,

SF

Hi. For multiple ranges in one sheet try like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B5:BK16, D5:L13, Z1:Z3")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End Sub

For areas in all sheets in the same workbook, in the ThisWorkbook module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Range("B5:BK16, D5:L13, Z1:Z3")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End Sub

 

[SuperFerret]

Thanks Peter,

You really know your stuff! I did think it'd be something like just putting the extra ranges in for the single sheet so maybe this VBA stuff is sinking in finally! haha!

And the Workbook_SheetChange is definitely going to come in useful!

 

[RoryA]

FWIW, I'd restrict the converted range to the Intersect range rather than the whole of Target. In fact, to be bulletproof, I'd loop through the intersect range, in case several discontiguous cells were changed at once.

 

[SuperFerret]

FWIW, I'd restrict the converted range to the Intersect range rather than the whole of Target. In fact, to be bulletproof, I'd loop through the intersect range, in case several discontiguous cells were changed at once.

You'd do what now? You've lost me Rory, not difficult, granted...but you lost me lol

 

[Peter_SSs]

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.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> changed <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> cVal<br><br>    <SPAN style="color:#00007F">Const</SPAN> myR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "B5:BK16,D3,G20:H22" <SPAN style="color:#007F00">'<- Your range(s)</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> changed = Intersect(Target, Range(myR))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> changed<br>            cVal = c.Value<br>            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">True</SPAN><br>                <SPAN style="color:#00007F">Case</SPAN> IsEmpty(cVal), IsNumeric(cVal), _<br>                        IsDate(cVal), IsError(cVal)<br>                    <SPAN style="color:#007F00">' Do nothing</SPAN><br>                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN><br>                    c.Value = UCase(cVal)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Peter_SSs]

I missed a couple of the recent posts while i was composing mine but ...

You've lost me Rory ..

Try a couple of the things I suggested in my 2 numbered points.

... in case several discontiguous cells were changed at once.

I don't think they need to be discontiguous. Target.Value is a problem if Target consists of more than one cell in any arrangement.

 

[SuperFerret]

Thanks Peter for the suggestion, it all actually makes sense to me now I can see it, which is a surprise

The ranges I'm using this for myself are Product References and some in-house system codes which always need to be uppercase, we've trying asking politely but people don't seem to bother so I plan on forcing it.

There may be some problems with multiple cell changes, would this error if the Product Ref's were say pasted into the range? - Duh, already answered in previous message! >.<;