How do I set all caps as the default?

[Mark Barnard]

I want to set my worksheet up so that all text entered is in caps. I do not
want to have to toggle the Caps Lock key to do this. Is there a way to make
this work?

 

[Mike H]

Mark,

There are VB solutions to this but as you want all caps then pressing the
capslock key once doesn't seem to onerous.

Mike

 

[Mark Barnard]

Hi Mike,

Thank you for your post. I agree, however, this is not what the user wants.

I am a long way from an expert in VB, so any help/advice/expertise you might
have would be appreciated.

Thanks!

Respectfully,

Mark

 

[Mike H]

Mark,

Right click your worksheet, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
On Error Resume Next
Application.EnableEvents = False
For Each MyRange In Selection.SpecialCells(xlCellTypeConstants,
xlTextValues).Cells
If Err.Number = 0 Then
MyRange.Value = StrConv(MyRange.Text, vbUpperCase)
End If
Next MyRange
Application.EnableEvents = True
End Sub

Mike

 

[Mark Barnard]

Hi Mike,

Thanks for this. Stupid question #3: Right click exactly where? I do not
find "View Code".

Sorry...

Thanks...

Mark

 

[Gord Dibben]

Right-click on the sheet tab and "View Code"

Paste the code into that sheet module.

Alt + q to return to the sheet window.


Gord Dibben MS Excel MVP

 

[Mark Barnard]

Hi Mr. Dibben,

Thank you...I was able to do this exactly as you said.

However, when I type text into a cell, it is not in caps.

Usually, when I get mad at my computer, it is because it is doing EXACTLY
what I tell it to do! Heehee

Any suggestions?

Thank you!

Sincerely,

Mark

 

[Gord Dibben]

Mark

Mike's code works for me when pasted into the sheet module as instructed.

Re-posted without the line-wrap.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
On Error Resume Next
Application.EnableEvents = False
For Each MyRange In Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues).Cells
If Err.Number = 0 Then
MyRange.Value = StrConv(MyRange.Text, vbUpperCase)
End If
Next MyRange
Application.EnableEvents = True
End Sub


Gord

 

[Mark Barnard]

Dear Mr. Dibben,

It worked perfectly...thank u!

Now, if I want to get really cute, can I do the same thing, only for two
particular cells?

A little history may help...I am writing this for an aviation application.
Each airport in the world is identified by a unique 4-letter identifier. I
have a cell for the departure airport and the destination airport. The user
wants these two cells in all upper case. The remaining cells he wants to
remain in title case.

Sorry to be a pain...if it's a hassle, don't worry about it. You and Mike
have been great help already.

Sincerely,

Mark

 

[Andrew Thompson]

Re-posted without the line-wrap.

Here is a small tool I developed, to help check for line-wrap.
<http://www.physci.org/twc.jnlp>

HTH

 

[Gord Dibben]

Some modifications to original code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const WS_RANGE As String = "A1, A5"
'add more cells if needed
'like "A1, A5, A10, B1, B5"
On Error GoTo ErrHandler
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
Target.Formula = UCase(Target.Formula)
Next cell
End If
ErrHandler:
Application.EnableEvents = True
End Sub


Gord