Textbox date formatting

C

capt

I have a userform which has a textbox15 which I have formatted to dd-mmm-yy
by using the following code:

Private Sub TextBox15_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox15.Value = Format(TextBox15.Value, "dd-mmm-yy")
End Sub

But it doesnt work....any ideas?
 
D

Dave Peterson

I'd check to see if I had a date first:

Option Explicit
Private Sub TextBox15_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If IsDate(Me.TextBox15.Value) Then
Me.TextBox15.Value = Format(Me.TextBox15.Value, "dd-mmm-yy")
End If
End Sub
 
M

Mike H

It sounds like you have correctly included
Option Explicit
in your code which means all variables must be declared
Try declaring mydate

dim mydate as date

Mike
 
C

capt

Sorry still no joy.
The textbox value is entered on to a data sheet using the following code
LastRow.Offset(1, 14).Value = TextBox15.Text
When I recall the userform the value in the textbox always revert to the
American format. Checking the data sheet it remains formatted as "dd-mmm-yy".
The code I use to recall the date back to the textbox is:
Me.TextBox15.Value = FoundCell.Offset(0, 14).Value

I hope all this makes sense!!

Oh by the way Happy new year when it comes!!!
 
C

capt

Sorry dave,
The recall still returns the value in the textbox as m/dd/yy
but the data sheet remains "dd-mmm-yy"
 
D

Dave Peterson

I don't understand. There's nothing in the code that deals with a sheet--it's
all userform stuff.
Sorry dave,
The recall still returns the value in the textbox as m/dd/yy
but the data sheet remains "dd-mmm-yy"
Click to expand...
 
C

capt

Yes your right Dave, it all userform codes. Here is the whole code:

Option Explicit

Private Sub CommandButton1_Click()
Dim LastRow As Object


Set LastRow = Sheet6.Range("a5000").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
LastRow.Offset(1, 3).Value = TextBox4.Text
LastRow.Offset(1, 4).Value = TextBox5.Text
LastRow.Offset(1, 5).Value = TextBox6.Text
LastRow.Offset(1, 6).Value = TextBox7.Text
LastRow.Offset(1, 7).Value = TextBox8.Text
LastRow.Offset(1, 8).Value = TextBox9.Text
LastRow.Offset(1, 9).Value = TextBox10.Text
LastRow.Offset(1, 10).Value = TextBox11.Text
LastRow.Offset(1, 11).Value = TextBox12.Text
LastRow.Offset(1, 12).Value = TextBox13.Text
LastRow.Offset(1, 13).Value = TextBox14.Text
LastRow.Offset(1, 14).Value = TextBox15.Text
LastRow.Offset(1, 16).Value = TextBox16.Text
LastRow.Offset(1, 18).Value = TextBox17.Text
LastRow.Offset(1, 15).Value = TextBox18.Text
LastRow.Offset(1, 19).Value = TextBox19.Text
LastRow.Offset(1, 17).Value = TextBox20.Text
LastRow.Offset(1, 20).Value = CheckBox1.Caption
LastRow.Offset(1, 21).Value = CheckBox2.Caption
LastRow.Offset(1, 22).Value = CheckBox3.Caption
LastRow.Offset(1, 23).Value = CheckBox4.Caption
LastRow.Offset(1, 24).Value = CheckBox5.Caption
LastRow.Offset(1, 25).Value = CheckBox6.Caption
LastRow.Offset(1, 26).Value = CheckBox7.Caption
LastRow.Offset(1, 27).Value = CheckBox8.Caption
LastRow.Offset(1, 28).Value = CheckBox9.Caption
LastRow.Offset(1, 29).Value = CheckBox10.Caption
LastRow.Offset(1, 30).Value = CheckBox11.Caption
LastRow.Offset(1, 31).Value = CheckBox12.Caption
LastRow.Offset(1, 32).Value = ComboBox2.Text
LastRow.Offset(1, 33).Value = CheckBox13.Caption

LastRow.Offset(1, 35).Value = TextBox23.Text



MsgBox "Your record has been entered?", vbOKOnly

If vbOK Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""
TextBox11.Text = ""
TextBox12.Text = ""
TextBox13.Text = ""
TextBox14.Text = ""
TextBox15.Text = ""
TextBox16.Text = ""
TextBox17.Text = ""
TextBox18.Text = ""
TextBox19.Text = ""
TextBox20.Text = ""
ComboBox2.Text = ""
TextBox23.Text = ""


TextBox1.SetFocus


Else

End If

End Sub

Private Sub CommandButton2_Click()
Dim FoundCell As Range
If Me.ComboBox1.ListIndex = -1 Then
'nothing filled in
Beep
Exit Sub
End If
With Worksheets("Customers").Range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlPrevious, _
MatchCase:=False)
End With
If FoundCell Is Nothing Then
'this shouldn't happen!
Beep
Else
Me.TextBox1.Value = FoundCell.Offset(0, 0).Value
Me.TextBox2.Value = FoundCell.Offset(0, 1).Value
Me.TextBox3.Value = FoundCell.Offset(0, 2).Value
Me.TextBox4.Value = FoundCell.Offset(0, 3).Value
Me.TextBox5.Value = FoundCell.Offset(0, 4).Value
Me.TextBox6.Value = FoundCell.Offset(0, 5).Value
Me.TextBox7.Value = FoundCell.Offset(0, 6).Value
Me.TextBox8.Value = FoundCell.Offset(0, 7).Value
Me.TextBox9.Value = FoundCell.Offset(0, 8).Value
Me.TextBox10.Value = FoundCell.Offset(0, 9).Value
Me.TextBox11.Value = FoundCell.Offset(0, 10).Value
Me.TextBox12.Value = FoundCell.Offset(0, 11).Value
Me.TextBox13.Value = FoundCell.Offset(0, 12).Value
Me.TextBox14.Value = FoundCell.Offset(0, 13).Value
Me.TextBox15.Value = FoundCell.Offset(0, 14).Value
Me.TextBox16.Value = FoundCell.Offset(0, 16).Value
Me.TextBox17.Value = FoundCell.Offset(0, 18).Value
Me.TextBox18.Value = FoundCell.Offset(0, 15).Value
Me.TextBox19.Value = FoundCell.Offset(0, 19).Value
Me.TextBox20.Value = FoundCell.Offset(0, 17).Value
Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(0, 20).Value) = "yes")
Me.CheckBox2.Value = CBool(LCase(FoundCell.Offset(0, 21).Value) = "yes")
Me.CheckBox3.Value = CBool(LCase(FoundCell.Offset(0, 22).Value) = "yes")
Me.CheckBox4.Value = CBool(LCase(FoundCell.Offset(0, 23).Value) = "yes")
Me.CheckBox5.Value = CBool(LCase(FoundCell.Offset(0, 24).Value) = "yes")
Me.CheckBox6.Value = CBool(LCase(FoundCell.Offset(0, 25).Value) = "yes")
Me.CheckBox7.Value = CBool(LCase(FoundCell.Offset(0, 26).Value) = "yes")
Me.CheckBox8.Value = CBool(LCase(FoundCell.Offset(0, 27).Value) = "yes")
Me.CheckBox9.Value = CBool(LCase(FoundCell.Offset(0, 28).Value) = "yes")
Me.CheckBox10.Value = CBool(LCase(FoundCell.Offset(0, 29).Value) = "yes")
Me.CheckBox11.Value = CBool(LCase(FoundCell.Offset(0, 30).Value) = "yes")
Me.CheckBox12.Value = CBool(LCase(FoundCell.Offset(0, 31).Value) = "yes")
Me.CheckBox13.Value = CBool(LCase(FoundCell.Offset(0, 33).Value) = "yes")

Me.ComboBox2.Value = FoundCell.Offset(0, 32).Value
Me.TextBox23.Value = FoundCell.Offset(0, 35).Value
End If
End Sub


Private Sub CommandButton99_Click()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

With Worksheets("Customers")
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow Step 1
If Application.CountIf(.Range("a1").EntireColumn, _
.Cells(iRow, "A").Value) > 1 Then
'it's a duplicate
.Rows(iRow).Delete
End If
Next iRow
End With

Unload Me

End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Call CommandButton99_Click
End If
End Sub


Private Sub TextBox15_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox15.Value = Format(TextBox15.Value, "dd-mmm-yy")

End Sub

Private Sub TextBox16_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox16.Value = Format(TextBox16.Value, "dd-mmm-yy")

End Sub

Private Sub TextBox17_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox17.Value = Format(TextBox17.Value, "dd-mmm-yy")

End Sub

There is only three textboxes that are giving me this problem, Textboxes 15
16 and 17.
They just default to the American date settings when I search and call up
client details.
 
D

Dave Peterson

I don't understand the question--is it about formatting in the userform or what?


Yes your right Dave, it all userform codes. Here is the whole code:

Option Explicit

Private Sub CommandButton1_Click()
Dim LastRow As Object

Set LastRow = Sheet6.Range("a5000").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
LastRow.Offset(1, 3).Value = TextBox4.Text
LastRow.Offset(1, 4).Value = TextBox5.Text
LastRow.Offset(1, 5).Value = TextBox6.Text
LastRow.Offset(1, 6).Value = TextBox7.Text
LastRow.Offset(1, 7).Value = TextBox8.Text
LastRow.Offset(1, 8).Value = TextBox9.Text
LastRow.Offset(1, 9).Value = TextBox10.Text
LastRow.Offset(1, 10).Value = TextBox11.Text
LastRow.Offset(1, 11).Value = TextBox12.Text
LastRow.Offset(1, 12).Value = TextBox13.Text
LastRow.Offset(1, 13).Value = TextBox14.Text
LastRow.Offset(1, 14).Value = TextBox15.Text
LastRow.Offset(1, 16).Value = TextBox16.Text
LastRow.Offset(1, 18).Value = TextBox17.Text
LastRow.Offset(1, 15).Value = TextBox18.Text
LastRow.Offset(1, 19).Value = TextBox19.Text
LastRow.Offset(1, 17).Value = TextBox20.Text
LastRow.Offset(1, 20).Value = CheckBox1.Caption
LastRow.Offset(1, 21).Value = CheckBox2.Caption
LastRow.Offset(1, 22).Value = CheckBox3.Caption
LastRow.Offset(1, 23).Value = CheckBox4.Caption
LastRow.Offset(1, 24).Value = CheckBox5.Caption
LastRow.Offset(1, 25).Value = CheckBox6.Caption
LastRow.Offset(1, 26).Value = CheckBox7.Caption
LastRow.Offset(1, 27).Value = CheckBox8.Caption
LastRow.Offset(1, 28).Value = CheckBox9.Caption
LastRow.Offset(1, 29).Value = CheckBox10.Caption
LastRow.Offset(1, 30).Value = CheckBox11.Caption
LastRow.Offset(1, 31).Value = CheckBox12.Caption
LastRow.Offset(1, 32).Value = ComboBox2.Text
LastRow.Offset(1, 33).Value = CheckBox13.Caption

LastRow.Offset(1, 35).Value = TextBox23.Text

MsgBox "Your record has been entered?", vbOKOnly

If vbOK Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""
TextBox11.Text = ""
TextBox12.Text = ""
TextBox13.Text = ""
TextBox14.Text = ""
TextBox15.Text = ""
TextBox16.Text = ""
TextBox17.Text = ""
TextBox18.Text = ""
TextBox19.Text = ""
TextBox20.Text = ""
ComboBox2.Text = ""
TextBox23.Text = ""

TextBox1.SetFocus

Else

End If

End Sub

Private Sub CommandButton2_Click()
Dim FoundCell As Range
If Me.ComboBox1.ListIndex = -1 Then
'nothing filled in
Beep
Exit Sub
End If
With Worksheets("Customers").Range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlPrevious, _
MatchCase:=False)
End With
If FoundCell Is Nothing Then
'this shouldn't happen!
Beep
Else
Me.TextBox1.Value = FoundCell.Offset(0, 0).Value
Me.TextBox2.Value = FoundCell.Offset(0, 1).Value
Me.TextBox3.Value = FoundCell.Offset(0, 2).Value
Me.TextBox4.Value = FoundCell.Offset(0, 3).Value
Me.TextBox5.Value = FoundCell.Offset(0, 4).Value
Me.TextBox6.Value = FoundCell.Offset(0, 5).Value
Me.TextBox7.Value = FoundCell.Offset(0, 6).Value
Me.TextBox8.Value = FoundCell.Offset(0, 7).Value
Me.TextBox9.Value = FoundCell.Offset(0, 8).Value
Me.TextBox10.Value = FoundCell.Offset(0, 9).Value
Me.TextBox11.Value = FoundCell.Offset(0, 10).Value
Me.TextBox12.Value = FoundCell.Offset(0, 11).Value
Me.TextBox13.Value = FoundCell.Offset(0, 12).Value
Me.TextBox14.Value = FoundCell.Offset(0, 13).Value
Me.TextBox15.Value = FoundCell.Offset(0, 14).Value
Me.TextBox16.Value = FoundCell.Offset(0, 16).Value
Me.TextBox17.Value = FoundCell.Offset(0, 18).Value
Me.TextBox18.Value = FoundCell.Offset(0, 15).Value
Me.TextBox19.Value = FoundCell.Offset(0, 19).Value
Me.TextBox20.Value = FoundCell.Offset(0, 17).Value
Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(0, 20).Value) = "yes")
Me.CheckBox2.Value = CBool(LCase(FoundCell.Offset(0, 21).Value) = "yes")
Me.CheckBox3.Value = CBool(LCase(FoundCell.Offset(0, 22).Value) = "yes")
Me.CheckBox4.Value = CBool(LCase(FoundCell.Offset(0, 23).Value) = "yes")
Me.CheckBox5.Value = CBool(LCase(FoundCell.Offset(0, 24).Value) = "yes")
Me.CheckBox6.Value = CBool(LCase(FoundCell.Offset(0, 25).Value) = "yes")
Me.CheckBox7.Value = CBool(LCase(FoundCell.Offset(0, 26).Value) = "yes")
Me.CheckBox8.Value = CBool(LCase(FoundCell.Offset(0, 27).Value) = "yes")
Me.CheckBox9.Value = CBool(LCase(FoundCell.Offset(0, 28).Value) = "yes")
Me.CheckBox10.Value = CBool(LCase(FoundCell.Offset(0, 29).Value) = "yes")
Me.CheckBox11.Value = CBool(LCase(FoundCell.Offset(0, 30).Value) = "yes")
Me.CheckBox12.Value = CBool(LCase(FoundCell.Offset(0, 31).Value) = "yes")
Me.CheckBox13.Value = CBool(LCase(FoundCell.Offset(0, 33).Value) = "yes")

Me.ComboBox2.Value = FoundCell.Offset(0, 32).Value
Me.TextBox23.Value = FoundCell.Offset(0, 35).Value
End If
End Sub

Private Sub CommandButton99_Click()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

With Worksheets("Customers")
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow Step 1
If Application.CountIf(.Range("a1").EntireColumn, _
.Cells(iRow, "A").Value) > 1 Then
'it's a duplicate
.Rows(iRow).Delete
End If
Next iRow
End With

Unload Me

End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Call CommandButton99_Click
End If
End Sub

Private Sub TextBox15_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox15.Value = Format(TextBox15.Value, "dd-mmm-yy")

End Sub

Private Sub TextBox16_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox16.Value = Format(TextBox16.Value, "dd-mmm-yy")

End Sub

Private Sub TextBox17_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox17.Value = Format(TextBox17.Value, "dd-mmm-yy")

End Sub

There is only three textboxes that are giving me this problem, Textboxes 15
16 and 17.
They just default to the American date settings when I search and call up
client details.
Click to expand...
 
C

capt

Yes, the textboxes 15,16,&17 are in a userform, and every time I select a
clients name it brings up the details that have been entered before. the
porblem is that the textboxes that have date in they always revert back to
the American format. Is there a way of formatting "dd-mmm-yy" permernant?
 
D

Dave Peterson

Make sure you format it the way you want it when you populate the textbox.

if isdate(Foundcell.Offset(0, 14).Value) then
me.TextBox15.Value = format(FoundCell.Offset(0, 14).Value, "dd-mmm-yy")
else
me.textbox15.value = "not a date"
end if

Yes, the textboxes 15,16,&17 are in a userform, and every time I select a
clients name it brings up the details that have been entered before. the
porblem is that the textboxes that have date in they always revert back to
the American format. Is there a way of formatting "dd-mmm-yy" permernant?
--
capt

Dave Peterson said:
I don't understand the question--is it about formatting in the userform or what?
Click to expand...
Click to expand...
 
C

capt

Dave,
Thanks very much that did the trick.
--
capt


Dave Peterson said:
Make sure you format it the way you want it when you populate the textbox.

if isdate(Foundcell.Offset(0, 14).Value) then
me.TextBox15.Value = format(FoundCell.Offset(0, 14).Value, "dd-mmm-yy")
else
me.textbox15.value = "not a date"
end if

Yes, the textboxes 15,16,&17 are in a userform, and every time I select a
clients name it brings up the details that have been entered before. the
porblem is that the textboxes that have date in they always revert back to
the American format. Is there a way of formatting "dd-mmm-yy" permernant?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

TextBox will not clear date??????????? 4
Textbox date format. 2
Compile error:.......?? 1
Textbox font colour change? 6
Trying to remove error from conflicting code 0
Date Format - simple question 1
VBA - exporting serial letters to single documents 0
code crashing my programme??????? 4

Top