Solved: variable ProperCase

Is it possible to capitalize the first letter, but still be able to captilize any other letter I choose to capitalzie?

i.e. McDonald

Assuming
Cell (A2) has "mcdonald" in it
[VBA] =PROPER(LEFT(A2,2))&PROPER(RIGHT(A2,6))[/VBA]

can this be done in VBA?

[VBA]Function PCase(Rn As Range, i As Integer, j As Integer) As String
Dim Txt As String
Txt = Rn.Value
PCase = WorksheetFunction.Proper(Left(Txt, i)) & WorksheetFunction.Proper(right(Txt, j))

End Function
[/VBA]

You can use this function in a cell as = PCase(A2,2,6). The order of the arguments will be left then right

[vba]
Sub propercase()
Dim strValue As String
strValue = "mcdonald"
strValue = WorksheetFunction.Proper(Left(strValue, 2)) & WorksheetFunction.Proper(Right(strValue, 6))
Debug.Print strValue
End Sub
[/vba]

Mine was just an example of how nepotist went all the way and gave you a nice UDF.

Hi guys, this doesn't work in a userform textbox. is there an alternative code for a userform textbox?

Try using the after update event of the text box to reformat it, are you trying to use the value of the textbox and save it somewhere??

I'm only trying to do this in a textbox.

Then Use after update event

Try

[VBA]Private Sub TextBox1_Change()
With TextBox1
.Text = UCase(Left(.Text, 1)) & Mid(.Text, 2)
End With
End Sub[/VBA]

If it is just Mcs and Macs you wish to capitalise then this should do it. However not all suffixes are capitalised. Macdonald is also valid.
[VBA]Option Explicit
Option Compare Text

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim t
t = TextBox1
If Left(t, 2) = "mc" Then
t = UCase(Left(t, 1)) & "c" & UCase(Mid(t, 3, 1)) & Right(t, Len(t) - 3)
ElseIf Left(t, 3) = "mac" Then
t = UCase(Left(t, 1)) & "ac" & UCase(Mid(t, 4, 1)) & Right(t, Len(t) - 4)
Else
t = UCase(Left(t, 1)) & Right(t, Len(t) - 1)
End If

TextBox1 = t
End Sub
[/VBA]

So, what about our Celtic cousins with O' names?

Slainté,

[VBA]Option Explicit
Option Compare Text

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim t
t = TextBox1
If Left(t, 2) = "mc" Then
t = UCase(Left(t, 1)) & "c" & UCase(Mid(t, 3, 1)) & Right(t, Len(t) - 3)
ElseIf Left(t, 3) = "mac" Then
t = UCase(Left(t, 1)) & "ac" & UCase(Mid(t, 4, 1)) & Right(t, Len(t) - 4)
ElseIf Left(t,2) = "o'" then
t = UCase(Left(t, 2)) & UCase(Mid(t, 3, 1)) & Right(t, Len(t) - 3)
Else
t = UCase(Left(t, 1)) & Right(t, Len(t) - 1)
End If

TextBox1 = t
End Sub
[/VBA]
There ya go.

Hi everyone,

Thank you everyone for the suggestion, but Mikerickson code acomplish what I'm looking for with the exception that if there is a space between word in a sentence, it does not continue to capitalize the first letter.

for example: My Name Is McDonald McPherson

[vba]
With txtAirport
.Text = UCase(Left(.Text, 1)) & Mid(.Text, 2)
End With
[/vba]
The other codes does the same thing.

Picky Picky. You didn't say it needed to deal with multiple words.
[VBA]
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Dim arr
Dim x
Dim t
arr = Split(TextBox1, " ")

For x = 0 To UBound(arr)


If Left(arr(x), 2) = "mc" Then
arr(x) = UCase(Left(arr(x), 1)) & "c" & UCase(Mid(arr(x), 3, 1)) & Right(arr(x), Len(arr(x)) - 3)
ElseIf Left(arr(x), 3) = "mac" Then
arr(x) = UCase(Left(arr(x), 1)) & "ac" & UCase(Mid(arr(x), 4, 1)) & Right(arr(x), Len(arr(x)) - 4)
ElseIf Left(arr(x), 2) = "o'" Then
arr(x) = UCase(Left(arr(x), 2)) & UCase(Mid(arr(x), 3, 1)) & Right(arr(x), Len(arr(x)) - 3)
Else
arr(x) = UCase(Left(arr(x), 1)) & Right(arr(x), Len(arr(x)) - 1)
End If
Next
For x = 0 To UBound(arr)
t = t & " " & arr(x)
Next
TextBox1 = Trim(t)
End Sub[/VBA]

Please don't be offended, as I do really appreciate all your help.

Out curiosity, I've tried to use this in the change event but it errors on this part of the code when i click the space bar. Is it possible to use this in the change event?

[VBA]arr(x) = UCase(Left(arr(x), 1)) & Right(arr(x), Len(arr(x)) - 1)
[/VBA]

Not really as change calls the procedure on every key stroke and the procedure is looking for something after mc so after you type m it runs then c it runs and errors out.

The before update event should work for you anyway.

ok...thank you:friends:

This is a multi-word variation on my previous
[VBA]Private Sub TextBox1_Change()
Static tbChangeDisabled As Boolean
Dim Words As Variant, i As Long
If tbChangeDisabled Then Exit Sub

tbChangeDisabled = True
With TextBox1
Words = Split(.Text)
.Text = vbNullString

For i = 0 To UBound(Words)
.Text = LTrim(.Text & " " & UCase(Left(Words(i), 1)) & Mid(Words(i), 2))
Next i
End With
tbChangeDisabled = False
End Sub[/VBA]

PERFECT!!!! thank you, Mikerickson :beerchug: