Format Excel Cells on Change

[MTBChik]

Hi there,

I've been digging about and just can't seem to find what I'm looking for.

I am trying to format Column C contents (after they have been entered) to Ucase().

I'm not familiar with the Private Sub Worksheet_Change(ByVal Target As Range)

End Sub method and am not sure how to pass the variables, so that's where I'm getting stuck.

Thank you for pointing me in the right direction!

__~o
`\<,
(_)/(_)
MTBChick
The day you stop riding is the day you die.

 

[SkipVought]

Hi,

There's a big difference between FORMATTING and CHANGING data. String data cannot be FORMATTED. Numeric data CAN be formatted.

That being said, we often use the word "format" in a more general way, and that is what, I believe, you intend. And, in fact, you want to CHANGE the data to upper case. Of course, you could do that in a separate column, using the UPPER spreadsheet function. If that would suffice, then no VBA code is required.

If, however, you need the value in this column CHANGED to upper case, then there are several alternatives. a) If you want this to be done when the value changes, here's what must happen in the Change Event...
[tt]
1. Event processing must be disabled.
2. The change is made using the UCase function.
3. Event processing must be enabled.
[/tt]
CAVEAT: If your event procedure happens to error during the process, event processing will be disabled, until someone enables them or you reopen the workbook.

b) If you want ALL the values to be changed at one time, then you must run a routine to change each value in the column, again, using the UCase function.

Which method are you interested in?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MTBChik]

The latter. I've got untrained, unskilled folks entering the data and two columns would really mess with their heads...

__~o
`\<,
(_)/(_)
MTBChick
The day you stop riding is the day you die.

 

[MTBChik]

Oh, and actually, it need to be all caps....

__~o
`\<,
(_)/(_)
MTBChick
The day you stop riding is the day you die.

 

[MTBChik]

Fiddling:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub
End If
On Error GoTo ErrHandler:
If Not Application.Intersect(Me.Range("C1:C200"), Target) Is Nothing Then
If IsNumeric(Target.Value) = False Then
Application.EnableEvents = False
'Target.Value = StrConv(Target.Text, vbLowerCase)
'Target.Value = StrConv(Target.Text, vbUpperCase)
'Target.Value = StrConv(Target.Text, vbProperCase)
Application.EnableEvents = True
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Appears to be working!

__~o
`\<,
(_)/(_)
MTBChick
The day you stop riding is the day you die.