Capitalization Question

  • Thread starter Thread starter RF
  • Start date Start date
R

RF

Hello all,

I have a spreadsheet where the user inputs two or three abbreviated
letters such as, wb,sa,crc, etc.

Is there a way to make the letters they input into the cell turn into
all caps?

Thanks to all,

RF
 
The easiest way is to use Autocorrect:
Tools => Autocorrect Options

Here you would define rules, e.g.:
replace crc with CRC

This approach has limitations. It doesn't catch CrC or crC unless you define
replace rules for all variations.
Other approaches would be to use extra cells containing formulas changing
contents of the input cells to uppercase. Apart from these 2 options you
could use macros.

Joerg
 
A macro solution

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Dim aryWords

On Error GoTo ws_exit
Application.EnableEvents = False

aryWords = Array("as", "wb", "sa", "crc")
With Target
If Not IsError(Application.Match(LCase(.Value), aryWords, 0)) Then
.Value = UCase(.Value)
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Hi, and thanks. This is very cool. I have a couple of questions.
I know nothing about macros so here goes.

In your code there is a line here: aryWords = Array("as", "wb",
"sa", "crc")

I have a list of 50 or 60 names I want to be effected by the macro.
Do I have to manually input them here, or can I put in a range, etc.?

My second question has to do with the cell where I want the
capitalization to happen. As it stands, any cell where I input the
selected criteria will capitalize it. Can I make it happen only in
cell C2?

Thanks again, it's much appreciated. Very interesting. I'll be
studying this.

RF
 
RF said:
Hi, and thanks. This is very cool. I have a couple of questions.
I know nothing about macros so here goes.

In your code there is a line here: aryWords = Array("as", "wb",
"sa", "crc")

I have a list of 50 or 60 names I want to be effected by the macro.
Do I have to manually input them here, or can I put in a range, etc.?
Click to expand...


Of course, just takes a tad more code

aryWords = Application.Transpose(Range("A1:A10"))

My second question has to do with the cell where I want the
capitalization to happen. As it stands, any cell where I input the
selected criteria will capitalize it. Can I make it happen only in
cell C2?
Click to expand...


Again yes, but more code again.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C2" '<== change to suit
Dim aryWords

On Error GoTo ws_exit
Application.EnableEvents = False

aryWords = Application.Transpose(Worksheets("Sheet1").Range("A1:A60"))
If Not Intersect(Target, Me.renge(WS_RANGE)) Is Nothing Then
With Target
If Not IsError(Application.Match(LCase(.Value), aryWords, 0))
Then
.Value = UCase(.Value)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
 
Thanks again. For some reason I didn't need to do the second part.
it worked just fine after I made the first change.

I appreciate your time.

RF
 
Back
Top