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

i.e. McDonald

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

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

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

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.

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

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

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é,

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.

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.

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

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)

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.

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

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:

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.