Is there a way to set up say Col F to be all upper case when I type in it. I
would just like to continue to enter data and not have to think that I am
entering a Canadian Postal Code and manually change the text to upper case.
TIA

--

Regards
Michael Koerner

Right click the tab at the bottom of the worksheet where you want this
functionality, select View Code from the pop up menu that appears, and then
copy/paste the following into the code window that appeared...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
Application.EnableEvents = True
End If
End Sub

Now, go back to your worksheet and type something into Column F in lower
case and watch it change to upper case.

--
Rick (MVP - Excel)


"Michael Koerner" <(E-Mail Removed)> wrote in message
news:O%(E-Mail Removed)...
> Is there a way to set up say Col F to be all upper case when I type in it.
> I
> would just like to continue to enter data and not have to think that I am
> entering a Canadian Postal Code and manually change the text to upper
> case.
> TIA
>
> --
>
> Regards
> Michael Koerner
>
>
>

Rick;

Absolutely fantastic, that is two in a row you have provided with great
success. Thank you very much.

I do have one question. Would it be possible to change the "If
Target.Column = 6 Then" to read (going out on a limb here) If Target.Column
= 5 or If Target Column=6 Then.... I would like to include column 5 which
contains prov/state

--

Regards
Michael Koerner


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
Right click the tab at the bottom of the worksheet where you want this
functionality, select View Code from the pop up menu that appears, and then
copy/paste the following into the code window that appeared...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
Application.EnableEvents = True
End If
End Sub

Now, go back to your worksheet and type something into Column F in lower
case and watch it change to upper case.

--
Rick (MVP - Excel)


"Michael Koerner" <(E-Mail Removed)> wrote in message
news:O%(E-Mail Removed)...
> Is there a way to set up say Col F to be all upper case when I type in it.
> I
> would just like to continue to enter data and not have to think that I am
> entering a Canadian Postal Code and manually change the text to upper
> case.
> TIA
>
> --
>
> Regards
> Michael Koerner
>
>
>

Yes, you can do that. Here is the statement...

If Target.Column = 5 Or Target.Column = 6 Then

--
Rick (MVP - Excel)


"Michael Koerner" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Rick;
>
> Absolutely fantastic, that is two in a row you have provided with great
> success. Thank you very much.
>
> I do have one question. Would it be possible to change the "If
> Target.Column = 6 Then" to read (going out on a limb here) If
> Target.Column
> = 5 or If Target Column=6 Then.... I would like to include column 5 which
> contains prov/state
>
> --
>
> Regards
> Michael Koerner
>
>
> "Rick Rothstein" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> Right click the tab at the bottom of the worksheet where you want this
> functionality, select View Code from the pop up menu that appears, and
> then
> copy/paste the following into the code window that appeared...
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Column = 6 Then
> Application.EnableEvents = False
> Target.Formula = UCase(Target.Formula)
> Application.EnableEvents = True
> End If
> End Sub
>
> Now, go back to your worksheet and type something into Column F in lower
> case and watch it change to upper case.
>
> --
> Rick (MVP - Excel)
>
>
> "Michael Koerner" <(E-Mail Removed)> wrote in message
> news:O%(E-Mail Removed)...
>> Is there a way to set up say Col F to be all upper case when I type in
>> it.
>> I
>> would just like to continue to enter data and not have to think that I am
>> entering a Canadian Postal Code and manually change the text to upper
>> case.
>> TIA
>>
>> --
>>
>> Regards
>> Michael Koerner
>>
>>
>>
>
>

Thanks very much, greatly appreciated.

--

Regards
Michael Koerner


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
Yes, you can do that. Here is the statement...

If Target.Column = 5 Or Target.Column = 6 Then

--
Rick (MVP - Excel)


"Michael Koerner" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Rick;
>
> Absolutely fantastic, that is two in a row you have provided with great
> success. Thank you very much.
>
> I do have one question. Would it be possible to change the "If
> Target.Column = 6 Then" to read (going out on a limb here) If
> Target.Column
> = 5 or If Target Column=6 Then.... I would like to include column 5 which
> contains prov/state
>
> --
>
> Regards
> Michael Koerner
>
>
> "Rick Rothstein" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> Right click the tab at the bottom of the worksheet where you want this
> functionality, select View Code from the pop up menu that appears, and
> then
> copy/paste the following into the code window that appeared...
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Column = 6 Then
> Application.EnableEvents = False
> Target.Formula = UCase(Target.Formula)
> Application.EnableEvents = True
> End If
> End Sub
>
> Now, go back to your worksheet and type something into Column F in lower
> case and watch it change to upper case.
>
> --
> Rick (MVP - Excel)
>
>
> "Michael Koerner" <(E-Mail Removed)> wrote in message
> news:O%(E-Mail Removed)...
>> Is there a way to set up say Col F to be all upper case when I type in
>> it.
>> I
>> would just like to continue to enter data and not have to think that I am
>> entering a Canadian Postal Code and manually change the text to upper
>> case.
>> TIA
>>
>> --
>>
>> Regards
>> Michael Koerner
>>
>>
>>
>
>

Canadian postal codes like a space between 3rd and 4th character.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 7 Then Exit Sub 'adjust column to suit
On Error GoTo ErrHandler
Application.EnableEvents = False
With Target
.Formula = UCase(Target.Formula)
.Value = (Left(Target.Value, 3) & " " & Right(Target.Value, 3))
End With
ErrHandler:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Fri, 15 May 2009 16:37:06 -0400, "Michael Koerner" <(E-Mail Removed)>
wrote:

>Is there a way to set up say Col F to be all upper case when I type in it. I
>would just like to continue to enter data and not have to think that I am
>entering a Canadian Postal Code and manually change the text to upper case.
>TIA

Coming from Ottawa I automatically put in the space <g>

--

Regards
Michael Koerner


"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:(E-Mail Removed)...
Canadian postal codes like a space between 3rd and 4th character.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 7 Then Exit Sub 'adjust column to suit
On Error GoTo ErrHandler
Application.EnableEvents = False
With Target
.Formula = UCase(Target.Formula)
.Value = (Left(Target.Value, 3) & " " & Right(Target.Value, 3))
End With
ErrHandler:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Fri, 15 May 2009 16:37:06 -0400, "Michael Koerner" <(E-Mail Removed)>
wrote:

>Is there a way to set up say Col F to be all upper case when I type in it.
>I
>would just like to continue to enter data and not have to think that I am
>entering a Canadian Postal Code and manually change the text to upper case.
>TIA

Coming from a small town in B.C. I do the same when hand entering on an
envelope.

When entering in a worksheet I just let the event code do it all.


Gord

On Sat, 16 May 2009 16:53:49 -0400, "Michael Koerner" <(E-Mail Removed)>
wrote:

>Coming from Ottawa I automatically put in the space <g>

After a good nights sleep, your suggestion really made a lot of sense. thank
you very much.

But, when I tried to insert the macro you provided, I received a compile
error indicating I had an ambiguous statement.

I also have a couple of questions. The column with the postal code is column
6. Your macro I believe indicated <>7 I would also like to have column 5 if
possible in Upper Case as this is the prov/state column.

--

Regards
Michael Koerner


"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:(E-Mail Removed)...
Coming from a small town in B.C. I do the same when hand entering on an
envelope.

When entering in a worksheet I just let the event code do it all.


Gord

On Sat, 16 May 2009 16:53:49 -0400, "Michael Koerner" <(E-Mail Removed)>
wrote:

>Coming from Ottawa I automatically put in the space <g>

A worksheet cannot have multiple events of the same type.

Different types...........yes.........same type........no.

Sounds like you have more than one worksheet_change event in that sheet
module.

Either combine or change one of them to a different type.

BTW...............postal codes are in column 6...........is column 5 simply
province names?

I can't imagine you want the same format on both columns in that case.

Assumes you enter a province name in column 5 then a postal code in column 6

After the column 6 entry the code will run on both columns.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range(Target(1).Address), _
Range("F:F")) Is Nothing Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
With Target
.Formula = UCase(Target.Formula)
.Value = (Left(Target.Value, 3) & " " & Right(Target.Value, 3))
End With
With Target.Offset(0, -1)
.Formula = UCase(.Formula)
End With
ErrHandler:
Application.EnableEvents = True
End Sub


Gord

On Sun, 17 May 2009 06:51:15 -0400, "Michael Koerner" <(E-Mail Removed)>
wrote:

>After a good nights sleep, your suggestion really made a lot of sense. thank
>you very much.
>
>But, when I tried to insert the macro you provided, I received a compile
>error indicating I had an ambiguous statement.
>
>I also have a couple of questions. The column with the postal code is column
>6. Your macro I believe indicated <>7 I would also like to have column 5 if
>possible in Upper Case as this is the prov/state column.