Capitalization Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RF - 14 Aug 2007 03:36 GMT

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

Reply to this Message

Joerg Mochikun - 14 Aug 2007 04:27 GMT

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

> Hello all,
>
[quoted text clipped - 7 lines]
>
> RF

Reply to this Message

Bob Phillips - 14 Aug 2007 09:15 GMT

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.

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hello all,
>
[quoted text clipped - 7 lines]
>
> RF

Reply to this Message

RF - 14 Aug 2007 14:59 GMT

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

>A macro solution
>
[quoted text clipped - 20 lines]
>'code module. To do this, right-click on the sheet tab, select
>'the View Code option from the menu, and paste the code in.

Reply to this Message

Bob Phillips - 14 Aug 2007 19:53 GMT

> Hi, and thanks. This is very cool. I have a couple of questions.
> I know nothing about macros so here goes.
[quoted text clipped - 4 lines]
> 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.?

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?

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

Reply to this Message

RF - 14 Aug 2007 20:45 GMT

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

>> Hi, and thanks. This is very cool. I have a couple of questions.
>> I know nothing about macros so here goes.
[quoted text clipped - 36 lines]
> Application.EnableEvents = True
>End Sub

Reply to this Message