ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Textbox date formatting (https://www.excelbanter.com/excel-discussion-misc-queries/171114-textbox-date-formatting.html)

capt
Textbox date formatting
 
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?
--
capt

Mike H
Textbox date formatting
 
Maybe

mydate = Date
TextBox15.Value = Format(mydate, "dd-mmm-yy")

Mike

"capt" wrote:

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

capt
Textbox date formatting
 
Sorry that didnt work. I get an error message.
--
capt


"Mike H" wrote:

Maybe

mydate = Date
TextBox15.Value = Format(mydate, "dd-mmm-yy")

Mike

"capt" wrote:

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

Mike H
Textbox date formatting
 
perhaps you could share the error message

"capt" wrote:

Sorry that didnt work. I get an error message.
--
capt


"Mike H" wrote:

Maybe

mydate = Date
TextBox15.Value = Format(mydate, "dd-mmm-yy")

Mike

"capt" wrote:

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

capt
Textbox date formatting
 
Sorry,
Here goes:
Compile error
Variable not Defined

with "mydate" highlighted

--
capt


"Mike H" wrote:

perhaps you could share the error message

"capt" wrote:

Sorry that didnt work. I get an error message.
--
capt


"Mike H" wrote:

Maybe

mydate = Date
TextBox15.Value = Format(mydate, "dd-mmm-yy")

Mike

"capt" wrote:

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

Dave Peterson
Textbox date formatting
 
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



capt wrote:

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

--

Dave Peterson

Mike H
Textbox date formatting
 
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

"capt" wrote:

Sorry,
Here goes:
Compile error
Variable not Defined

with "mydate" highlighted

--
capt


"Mike H" wrote:

perhaps you could share the error message

"capt" wrote:

Sorry that didnt work. I get an error message.
--
capt


"Mike H" wrote:

Maybe

mydate = Date
TextBox15.Value = Format(mydate, "dd-mmm-yy")

Mike

"capt" wrote:

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

capt
Textbox date formatting
 
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!!!
--
capt


"Mike H" wrote:

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

"capt" wrote:

Sorry,
Here goes:
Compile error
Variable not Defined

with "mydate" highlighted

--
capt


"Mike H" wrote:

perhaps you could share the error message

"capt" wrote:

Sorry that didnt work. I get an error message.
--
capt


"Mike H" wrote:

Maybe

mydate = Date
TextBox15.Value = Format(mydate, "dd-mmm-yy")

Mike

"capt" wrote:

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

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


"Dave Peterson" wrote:

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



capt wrote:

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

--

Dave Peterson

Dave Peterson
Textbox date formatting
 
I don't understand. There's nothing in the code that deals with a sheet--it's
all userform stuff.

capt wrote:

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

"Dave Peterson" wrote:

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



capt wrote:

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

--

Dave Peterson


--

Dave Peterson

capt
Textbox date formatting
 
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.
--
capt


"Dave Peterson" wrote:

I don't understand. There's nothing in the code that deals with a sheet--it's
all userform stuff.

capt wrote:

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

"Dave Peterson" wrote:

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



capt wrote:

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

--

Dave Peterson


--

Dave Peterson

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



capt wrote:

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

"Dave Peterson" wrote:

I don't understand. There's nothing in the code that deals with a sheet--it's
all userform stuff.

capt wrote:

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

"Dave Peterson" wrote:

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



capt wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

capt
Textbox date formatting
 
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" wrote:

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



capt wrote:

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

"Dave Peterson" wrote:

I don't understand. There's nothing in the code that deals with a sheet--it's
all userform stuff.

capt wrote:

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

"Dave Peterson" wrote:

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



capt wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave Peterson
Textbox date formatting
 
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


capt wrote:

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" wrote:

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



capt wrote:

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

"Dave Peterson" wrote:

I don't understand. There's nothing in the code that deals with a sheet--it's
all userform stuff.

capt wrote:

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

"Dave Peterson" wrote:

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



capt wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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


"Dave Peterson" wrote:

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


capt wrote:

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" wrote:

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



capt wrote:

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

"Dave Peterson" wrote:

I don't understand. There's nothing in the code that deals with a sheet--it's
all userform stuff.

capt wrote:

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

"Dave Peterson" wrote:

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



capt wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 12:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com