How do I make specific columns Proper or Caps?

[rmm30]

Hi -

I copied the following code from this forum to make certain columns caps and another proper:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
If Target.Column <= 5 Then
Target.Value = UCase(Target.Value)
Else
Target.Value = Application.Proper(Target.Value)
End If
ErrHandler:
Application.EnableEvents = True
End Sub

My question is, how do I make a specific column proper? If I want column 6 only proper, how do i do that?

thanks

[Anne Troy]

Change the line to say this:
If Target.Column = 6 Then

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"rmm30" <[email protected]> wrote in message
news:[email protected]...
>
> Hi -
>
> I copied the following code from this forum to make certain columns
> caps and another proper:
>
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> If Target.Cells.Count > 1 Then Exit Sub
> On Error GoTo ErrHandler
> Application.EnableEvents = False
> If Target.Column <= 5 Then
> Target.Value = UCase(Target.Value)
> Else
> Target.Value = Application.Proper(Target.Value)
> End If
> ErrHandler:
> Application.EnableEvents = True
> End Sub
>
> My question is, how do I make a specific column proper? If I want
> column 6 only proper, how do i do that?
>
> thanks
>
>
> --
> rmm30
>
>
> ------------------------------------------------------------------------
> rmm30's Profile:
http://www.excelforum.com/member.php...fo&userid=8358
> View this thread: http://www.excelforum.com/showthread...hreadid=386119
>

[David McRitchie]

I'll throw in an Elseif statement such as for a zip state code
if you don't want it then remove it.

If target.Column = 6 then
Target.Value = Application.Proper(Target.Value)
ElseIF Target.Column 9 Then
Target.Value = UCase(Target.Value)
Else
'code here ... if you still want something done for all other cases
End If

More information on uppercase, propercase, sentence case see
http://www.mvps.org/dmcritchie/excel/proper.htm

You should probably code for proper case exceptions yourself
especially if working with people's names based on what you
find in your own data.

You will have to learn how to code for IF, then, else as this is basic to
use of VBA.

For more information on IF, Then, Else see HELP from within the VBE
search on: if then else
topic: If...Then...Else Statement

variations:
If target.Column = 6
If target.Column = 6 or 8
If target.column <= 6
If target.column = 9

Which is considerably different that a Worksheet function, see HELP from within Excel
Index (not available in Excel 2003): IF_Worksheet_Function
or search on: IF function
topic: IF Worksheet Function

IF THEN in worksheet functions and in VBA
http://www.mvps.org/dmcritchie/excel/ifthen.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"rmm30" <[email protected]> wrote ...
> I copied the following code from this forum to make certain columns
> caps and another proper: [clipped]