How do I set all caps as the default?

  • Thread starter Thread starter Mark Barnard
  • Start date Start date
M

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?
 
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
 
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
 
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
 
Hi Mike,

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

Sorry...

Thanks...

Mark
 
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
 
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
 
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
 
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
 
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
 
Back
Top