How do I make specific columns Proper or Caps?

  • Thread starter Thread starter rmm30
  • Start date Start date
R

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
 
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
 
Back
Top