Force proper case on user form text box

[Jock]

Hi
How can I code proper case to text boxes on a user form?
Here's the code to adapt; all text boxes to be formatted the same:

Private Sub CommandButton2_Click()
Dim LastRow As Object
Set LastRow = Sheet1.Range("f4000").End(xlUp)

LastRow.Offset(0, -1).Value = TextBox1.Text & " " & TextBox2.Text & " "
& TextBox3.Text & " " & TextBox4.Text _
& " " & TextBox5.Text & " v " & TextBox6.Text & " " & TextBox7.Text & "
" & TextBox8.Text & " " & TextBox9.Text _
& " " & TextBox10.Text 'copies text box data to cell
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""

Me.Hide
End Sub

On another user form, the date needs to be input by the user. Can an input
mask be added to a particular text box to ensure users all enter the date in
the same way?

Thanks,

 

[Jacob Skaria]

--For converting to proper case try the below

Dim intTemp as Integer, strTemp as String

For intTemp = 1 to 10
strTemp = strTemp & " " StrConv(Me.Controls("TextBox" & _
intTemp),vbProperCase)
Next
LastRow.Offset(0, -1).Value = Trim(strTemp)


-- You can display a default format for this text box
Private Sub UserForm_Initialize()
Me.TextBox1 = Format(Date, "dd-mm-yyyy")
End Sub


If this post helps click Yes

 

[john]

see if this does what you want:

Private Sub CommandButton2_Click()
Dim LastRow As Object
Dim DataStr As String
Dim na As Integer

Set LastRow = Sheet1.Range("f4000").End(xlUp)

For na = 1 To 10

DataStr = DataStr & " " & StrConv(Me.Controls("TextBox" & na).Text, _
vbProperCase)

Next na

LastRow.Offset(0, -1).Value = Trim(DataStr) 'copies text box data to cell

For na = 1 To 10

Me.Controls("TextBox" & na).Text = ""

Next na

Me.Hide

End Sub

 

[Jock]

Good stuff John,
How can I get a lowercase "v" to appear between the contents of text box 5
and text box 6 when they are copied to the worksheet?

 

[john]

see if this does what you want.

Private Sub CommandButton2_Click()
Dim LastRow As Object
Dim DataStr As String
Dim na As Integer

Set LastRow = Sheet1.Range("f4000").End(xlUp)

For na = 1 To 10

If na = 6 Then

DataStr = DataStr & " v " & StrConv(Me.Controls("TextBox" & na).Text, _
vbProperCase)

Else

DataStr = DataStr & " " & StrConv(Me.Controls("TextBox" & na).Text, _
vbProperCase)

End If

Next na

LastRow.Offset(0, -1).Value = Trim(DataStr) 'copies text box data to cell

For na = 1 To 10

Me.Controls("TextBox" & na).Text = ""

Next na

Me.Hide

End Sub

 

[Jock]

perfect, thank very much

 

[john]

glad it worked - on reflection, can reduce code a little this way:

Private Sub CommandButton2_Click()
Dim LastRow As Object
Dim DataStr As String
Dim na As Integer

Set LastRow = Sheet1.Range("f4000").End(xlUp)

For na = 1 To 10

v = " "

If na = 6 Then v = " v "

DataStr = DataStr & v & _
StrConv(Me.Controls("TextBox" & na).Text, _
vbProperCase)

Next na

LastRow.Offset(0, -1).Value = Trim(DataStr) 'copies text box data to
cell

For na = 1 To 10

Me.Controls("TextBox" & na).Text = ""

Next na

Me.Hide

End Sub

 

[Jock]

Have used this instead - always looking for shorter code!
Had to add:
Dim v As String
to make it work though.

Thanks again