PDA

View Full Version : Solved: variable ProperCase



av8tordude
03-25-2011, 04:20 AM
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
03-25-2011, 06:51 AM
Assuming
Cell (A2) has "mcdonald" in it
=PROPER(LEFT(A2,2))&PROPER(RIGHT(A2,6))

av8tordude
03-25-2011, 06:54 AM
can this be done in VBA?

nepotist
03-25-2011, 07:05 AM
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


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

BrianMH
03-25-2011, 07:06 AM
Sub propercase()
Dim strValue As String
strValue = "mcdonald"
strValue = WorksheetFunction.Proper(Left(strValue, 2)) & WorksheetFunction.Proper(Right(strValue, 6))
Debug.Print strValue
End Sub


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

av8tordude
03-25-2011, 07:43 AM
Hi guys, this doesn't work in a userform textbox. is there an alternative code for a userform textbox?

nepotist
03-25-2011, 07:59 AM
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
03-25-2011, 08:14 AM
I'm only trying to do this in a textbox.

nepotist
03-25-2011, 08:20 AM
Then Use after update event

mikerickson
03-25-2011, 10:57 AM
Try

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

mdmackillop
03-25-2011, 01:45 PM
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.
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

RonMcK
03-26-2011, 04:40 PM
So, what about our Celtic cousins with O' names?

Slainté,

BrianMH
03-27-2011, 03:19 AM
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

There ya go.

av8tordude
03-27-2011, 06:53 AM
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


With txtAirport
.Text = UCase(Left(.Text, 1)) & Mid(.Text, 2)
End With

The other codes does the same thing.

BrianMH
03-27-2011, 09:15 AM
Picky Picky. You didn't say it needed to deal with multiple words.

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

av8tordude
03-27-2011, 09:25 AM
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?

arr(x) = UCase(Left(arr(x), 1)) & Right(arr(x), Len(arr(x)) - 1)

BrianMH
03-27-2011, 09:39 AM
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
03-27-2011, 09:41 AM
ok...thank you:friends:

mikerickson
03-27-2011, 10:03 AM
This is a multi-word variation on my previous
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

av8tordude
03-27-2011, 10:10 AM
PERFECT!!!! thank you, Mikerickson :beerchug: