Formatting a single column

[McBarker]

I'm trying to format column C, from C6 down in a Worksheet, so that entries
are formatted as soon as they are entered. The data is entered as "a1" or
"b3" (without the quotes), and needs to be displayed as "A-1" or "B-3"
(again, without quotes). The following code converts the text to uppercase
as required, but also can convert the Header in cell C5. Is there anyway to
amend the code so that it doesn't change anything above C6?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 3 Then
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
Application.EnableEvents = True
On Error GoTo 0
End If

End Sub

I've tried programming this with a For... Next loop, but it looped endlessly
when data is entered, hanging the spreadsheet until giving the 3 fingered
salute.

Also, I can't seem to find an effective way to insert the "-" character into
the middle of each entry. Any ideas? Thanks.

 

[Rick Rothstein \(MVP - VB\)]

What about changing your If-Then statement to this?

If Target.Column = 3 And Target.Row >=6 Then

Rick

 

[McBarker]

Rick... Thank you. It worked like a charm. As you can see, I'm strictly
amateur at this. Any ideas on inserting the "-" in the center of the string
(between the "A" and the "1")?

 

[Rick Rothstein \(MVP - VB\)]

Sorry, I missed the part about wanting the dash. Do your entries **always**
contain only **one** trailing digit? If so, this will work for up to 5
leading letters (add or remove ampersand symbols to adjust for the maximum
number of leading letters although leaving more than necessary will not hurt
the operation of the code)...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 3 And Target.Row >=6 Then
Application.EnableEvents = False
Target.Value = Format(Target.Value, ">&&&&&-&")
Application.EnableEvents = True
End If
End Sub

Here, the Format command is doing both requested function... upper casing
the letters (that's what the greater than symbol is doing) and inserting the
dash in front of the single trailing digit (the ampersand signs represent
the characters from the first argument, one per character; if no character
exists for the position, nothing it returned in that position). Notice that
I changed the Target.Formula references to Target.Value references instead
(for what you want to do, I think Value is more appropriate to use than
Formula). I also added some error trapping that I think is necessary (stops
the error if you highlight the entire row and press Delete).

Now, if your entries can have more than one trailing digit, then you could
use something like this instead (it allows for any number of leading letters
and any number of trailing digits)...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim X As Long
Dim Pattern As String
If Target.Column = 3 And Target.Row >= 6 Then
Pattern = String(Len(Target.Value) + 1, "&")
For X = 1 To Len(Target.Value)
If Mid$(Target.Value, X, 1) Like "#" Then
Mid$(Pattern, X) = "-"
On Error GoTo CleanUp
Application.EnableEvents = False
Target.Value = Format(Target.Value, ">" & Pattern)
Exit For
End If
Next
End If
CleanUp:
Application.EnableEvents = True
End Sub

Rick

 

[Rick Rothstein \(MVP - VB\)]

Sorry, I missed the part about wanting the dash. Do your entries

**always** contain only **one** trailing digit? If so, this will work for
up to 5 leading letters (add or remove ampersand symbols to adjust for the
maximum number of leading letters although leaving more than necessary
will not hurt the operation of the code)...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 3 And Target.Row >=6 Then
Application.EnableEvents = False
Target.Value = Format(Target.Value, ">&&&&&-&")
Application.EnableEvents = True
End If
End Sub

..... I also added some error trapping that I think is necessary (stops the
error if you highlight the entire row and press Delete).

Of course, had I actually posted the code that included the error trapping,
that might have been useful.<g>

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 3 And Target.Row >=6 Then
Application.EnableEvents = False
Target.Value = Format(Target.Value, ">&&&&&-&")
Application.EnableEvents = True
End If
End Sub

Remember, the above code is for when there is only one trailing digit... the
more general code that I posted in my previous message was complete as
posted.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Of course, had I actually posted the code that included the error

trapping, that might have been useful.<g>

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 3 And Target.Row >=6 Then
Application.EnableEvents = False
Target.Value = Format(Target.Value, ">&&&&&-&")
Application.EnableEvents = True
End If
End Sub

Remember, the above code is for when there is only one trailing digit...
the more general code that I posted in my previous message was complete as
posted.

Sigh! Yes, as I said, if I **had** posted the code with the error trapping,
that would indeed have been useful...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 3 And Target.Row >= 6 Then
On Error GoTo CleanUp
Application.EnableEvents = False
Target.Value = Format(Target.Value, ">&&&&&-&")
End If
CleanUp:
Application.EnableEvents = True
End Sub

Rick

 

[McBarker]

Rick... Thank you... thank you. This worked perfectly. The string in that
column is always an A or B followed by numbers 1, 2 , 3 or 4. There's no
other variation. I appreciate your help. You are a macro programming
genius... Nay, a VBA god <G>. Again thanks