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

"Gord Dibben" wrote:

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

On Mon, 10 Dec 2007 17:56:00 -0800, Mark Barnard
wrote:

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" wrote:

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

On Mon, 10 Dec 2007 07:14:06 -0800, Mark Barnard
wrote:

Hi Mike,

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

Sorry...

Thanks...

Mark

"Mike H" wrote:

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" wrote:

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" wrote:

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" wrote:

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?