How to capitilize automatically?
just as a quickie test this worked for me..........
====================
Sub change_case()
Dim ws As Worksheet
Dim c As Range
Dim s As String
Set ws = ActiveSheet
Set c = ws.Range("e5")
s = "mary smith"
c = s
c.Value = StrConv(c.Value, vbProperCase)
End Sub
================
result was "Mary Smith".
(WS is short for worksheet, so JLGWhiz was talking about your
worksheet_change event. the command line is the line that calls
another macro.)
what happens when you change
If Not Application.Intersect(Target, Range("C9:C47")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
to
If Not Application.Intersect(Target, Range("C9:C47")) Is Nothing Then
Target(1).Value = ProperCase(Target(1).Value)
does that work?
:)
susan
On Apr 23, 11:51 pm, wrote:
On Apr 23, 10:14 pm, JLGWhiz
wrote:
This will get you started:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("C:C"), Target) "" Then
Range("C3").Value = StrConv(Range("C3").Value, 3)
End If
End Sub
I did not see the specific macro that you referenced in your WS change code,
but if you substitute it on the command line of the if statement above and
you still don't get the results you want, then it will be that macro that is
the problem. The one above will execute, as is, and will change whatever is
in Range("C3") of that worksheet to ProperCase.
I'm sorry I'm still new so I'm not sure what you mean by "WS" change
code and I have absolutely no idea what the command line is on the if
statement. I did come close to what I'm trying to achieve though. On
another site I found a code for proper for a macro and it also has a
change code for Upper to which it says all you have to do is change it
to proper if that's what you want like it previously described. The
trouble is I can't get it to work. It looks like it's looking for
something different than just entering "vbProperCase".
This is my macro and my only macro now:
Sub Proper_Case()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = StrConv(Rng.Value, vbProperCase)
End If
Next Rng
End Sub
This is my change code and it is entered on sheet 1:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("C9:C47")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub
This works beautifully to change it to upper case but I can't figure
out how to do proper case instead. I've tried changing UCase to
vbproper case, proper, propercase, none worked. I also tried replacing
the whole line with rng.value line from the macro. Obviously I'm not
doing something right, I just don't know what.
Ideas?
Charles
|