Solved: variable ProperCase

[av8tordude]

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

i.e. McDonald

[nepotist]

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

[av8tordude]

can this be done in VBA?

[nepotist]

[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

[BrianMH]

[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.

[av8tordude]

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

[nepotist]

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??

[av8tordude]

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

[nepotist]

Then Use after update event

[mikerickson]

Try

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

[mdmackillop]

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]

[RonMcK]

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

Slainté,

[BrianMH]

[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.

[av8tordude]

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.

[BrianMH]

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]

[av8tordude]

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]

[BrianMH]

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.

[av8tordude]

ok...thank you

[mikerickson]

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]

[av8tordude]

PERFECT!!!! thank you, Mikerickson