2 'myStr' values in one Worksheet, need VB Code help!

[Betrock]

MY VB is too rusty to cope!

I've copied one of ya'll's code to format a specific cell(I4) the way I want
it to appear:

User enters(cell formatted as text): ##-@###-###
and it converts to: example 24-Q123-456 (even changes the
letter to upper case - I love you guys!)

Code Used:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myStr As String

With Target
If .Cells.Count > 1 Then Exit Sub
If .HasFormula Then Exit Sub
If Intersect(.Cells, Me.Range("I4")) Is Nothing Then
Exit Sub
End If

On Error GoTo ErrHandler:

If Len(.Value) = 9 Then
'do it
'##-@###-###

myStr = Left(.Value, 2) & "-" _
& Mid(.Value, 3, 4) & "-" _
& RIGHT(.Value, 3)
'make those letters uppercase???
myStr = UCase(myStr)
Application.EnableEvents = False
.Value = myStr
End If
End With

ErrHandler:
Application.EnableEvents = True

End Sub
****************************
C4 will be one or two people's names, typed as: John & Mary Jones, or john &
mary jones, or john jones, etc.
What I also need to do is, in the same worksheet as above: change Cell C4 to
all upper case.
How do I add the coding to do that without interfering with the first sub?

Regards,
Betrock

 

[Jacob Skaria]

Try the below..

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myStr As String

If Target.Cells.Count = 1 Then
On Error GoTo ErrHandler:
If Not Application.Intersect(Target, Range("I4")) Is Nothing Then
With Target
If Len(.Value) = 9 Then
myStr = Left(.Value, 2) & "-" _
& Mid(.Value, 3, 4) & "-" _
& Right(.Value, 3)
myStr = UCase(myStr)
Application.EnableEvents = False
.Value = myStr
End If
End With
ElseIf Not Application.Intersect(Target, Range("C4")) Is Nothing Then
If Target.Text <> "" Then
Application.EnableEvents = False
Target.Value = UCase(Target.Text)
End If
End If
End If

ErrHandler:
Application.EnableEvents = True

End Sub

If this post helps click Yes

 

[Mike H]

Hi

Simply add this line

Range("c4") = UCase(Range("C4"))

Mike

 

[Betrock]

Thank you!