Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Textbox date formatting
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 67
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 67
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 67
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default 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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 67
Default 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
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 67
Default 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

Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 67
Default 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

Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 67
Default 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

Reply With QuoteReply With Quote
  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 67
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Textbox Formatting jmc8355 Excel Discussion (Misc queries) 3 April 24th 07 06:30 PM
Textbox date format. AOU Excel Discussion (Misc queries) 2 April 24th 07 02:28 PM
textbox formatting jnf40 Excel Discussion (Misc queries) 1 August 3rd 06 08:12 PM
TextBox Formatting grahammal Excel Discussion (Misc queries) 3 April 12th 06 04:54 PM
Help needed with textbox formatting in Excel 2000 JIMBROOKS Excel Discussion (Misc queries) 1 January 1st 05 03:33 PM


All times are GMT +1. The time now is 09:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement