Caps Lock

[Damo10]

Hi,

Is it possible to have any character entered into range ($E$18:$V$383) entered in capital leters by default? There will only be one letter entered into a cell

Regards

 

[dave3009]

Right click the sheet tab and select view code, paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E18:V383")) Is Nothing Then Exit Sub
Target.Value = UCase(Target.Value)
End Sub

 

[Damo10]

Hi,

Thanks for the code, it woeks perfectly in a blank sheet but when I put it into my workbook sheet that I want it to work on it works very slowely. The only other code I have on the sheet tab is for buttons any ideas?

Regards

 

[Damo10]

Hi,

I found out how to do it by using EnableEvents

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

Thanks a lot for your help

Regards

 

[Peter_SSs]

That code is not robust as it will not handle multiple cells. To test, try any of these in the target range.

1. Select 2 or more cells and press the Delete key.

2. Select 2 or more cells, type any letter and confirm with Ctrl+Enter.

3. Select 2 or more cells (say from outside the target area) and copy then paste in the target range.

If you do test any of the above, make sure you haven't disabled events. To be sure ..
In the VBA window, ensure the Immediate Window is visible (View|Immediate Window) and then on a new line in the Immediate Window, type
Application.EnableEvents=True and press Enter



This would be better code to do what you want.:

<font face=Courier New><br><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>    <br>    <SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Target, Range("E18:V383"))<br>    <SPAN style="color:#00007F">If</SPAN> Changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</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>        c.Value = UCase(c.Value)<br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Peter_SSs]

That code is not robust as it will not handle multiple cells. To test, try any of these in the target range.

1. Select 2 or more cells and press the Delete key.

2. Select 2 or more cells, type any letter and confirm with Ctrl+Enter.

3. Select 2 or more cells (say from outside the target area) and copy then paste in the target range.

If you do test any of the above, make sure you haven't disabled events. To be sure ..
In the VBA window, ensure the Immediate Window is visible (View|Immediate Window) and then on a new line in the Immediate Window, type
Application.EnableEvents=True and press Enter



This would be better code to do what you want.:

<font face=Courier New><br><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>    <br>    <SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Target, Range("E18:V383"))<br>    <SPAN style="color:#00007F">If</SPAN> Changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</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>        c.Value = UCase(c.Value)<br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Damo10]

Hi Peter,

Yes you were right about the multiple cells, your code does handle that a lot better.

I did try selecting the whole range and deleteting all the contents but it freezes, any suggestions?

Regards

 

[Peter_SSs]

I did try selecting the whole range and deleteting all the contents but it freezes, any suggestions?

By 'freeze' what do you mean exactly? How did you recover from that?

I don't see why it should freeze - and it doesn't for me.

Can you confirm ..

a) You used the code exactly as I posted? If not please post your modified code.

b) You have removed the earlier codes (Dave suggestion and your code from post #4)

c) What version of Excel you are using.

d) That your 'Events' are enabled? In the Immediate pane on a new line type:
?Application.EnableEvents and press Enter
What appears on the next line?