TextBox will not clear date???????????
 

THi,
I have a Textbox when entered a date, it adds 90 days and 120 days to that
date and enteres these dates to two more textboxes.
The trouble I am having is when a date is entered in error in the first
Textbox I can not clear it. The cursor freezes. Any ideas?
The code I am using is as follows:

Private Sub TextBox36_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox36.Value = Format(TextBox36.Value, "dd-mmm-yy")
If IsDate(Me.TextBox36.Value) Then
Me.TextBox37.Value _
= Format(CDate(Me.TextBox36.Value) + 90, "dd-mmm-yy")
Me.TextBox38.Value _
= Format(CDate(Me.TextBox36.Value) + 120, "dd-mmm-yy")
Else
Beep
Cancel = True
End If
End Sub

Tdp

TextBox will not clear date???????????
 

Do you have any code associated with the other textboxes?

--
__________________________________
HTH

Bob

"Tdp" wrote in message
...
THi,
I have a Textbox when entered a date, it adds 90 days and 120 days to that
date and enteres these dates to two more textboxes.
The trouble I am having is when a date is entered in error in the first
Textbox I can not clear it. The cursor freezes. Any ideas?
The code I am using is as follows:

Private Sub TextBox36_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox36.Value = Format(TextBox36.Value, "dd-mmm-yy")
If IsDate(Me.TextBox36.Value) Then
Me.TextBox37.Value _
= Format(CDate(Me.TextBox36.Value) + 90, "dd-mmm-yy")
Me.TextBox38.Value _
= Format(CDate(Me.TextBox36.Value) + 120, "dd-mmm-yy")
Else
Beep
Cancel = True
End If
End Sub

Tdp

TextBox will not clear date???????????
 

The codes I have are as follows:

To insert the dates in data sheet:

LastRow.Offset(1, 48).Value = TextBox36.Text
LastRow.Offset(1, 49).Value = TextBox37.Text
LastRow.Offset(1, 50).Value = TextBox38.Text

To look up data from data sheet:

Me.TextBox36.Value = Format(FoundCell.Offset(0, 48).Value, "dd-mmm-yy")
Me.TextBox37.Value = Format(FoundCell.Offset(0, 49).Value, "dd-mmm-yy")
Me.TextBox38.Value = Format(FoundCell.Offset(0, 50).Value, "dd-mmm-yy")

Else
Me.TextBox36.Value = ""
Me.TextBox37.Value = ""
Me.TextBox38.Value = ""

If you need more of the code let me know.
Thank you

--
Tdp


"Bob Phillips" wrote:

Do you have any code associated with the other textboxes?

--
__________________________________
HTH

Bob

"Tdp" wrote in message
...
THi,
I have a Textbox when entered a date, it adds 90 days and 120 days to that
date and enteres these dates to two more textboxes.
The trouble I am having is when a date is entered in error in the first
Textbox I can not clear it. The cursor freezes. Any ideas?
The code I am using is as follows:

Private Sub TextBox36_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox36.Value = Format(TextBox36.Value, "dd-mmm-yy")
If IsDate(Me.TextBox36.Value) Then
Me.TextBox37.Value _
= Format(CDate(Me.TextBox36.Value) + 90, "dd-mmm-yy")
Me.TextBox38.Value _
= Format(CDate(Me.TextBox36.Value) + 120, "dd-mmm-yy")
Else
Beep
Cancel = True
End If
End Sub

Tdp

TextBox will not clear date???????????
 

Does that mean that neither TextBox37 or 38 have their own event code?

--
__________________________________
HTH

Bob

"Tdp" wrote in message
...
The codes I have are as follows:

To insert the dates in data sheet:

LastRow.Offset(1, 48).Value = TextBox36.Text
LastRow.Offset(1, 49).Value = TextBox37.Text
LastRow.Offset(1, 50).Value = TextBox38.Text

To look up data from data sheet:

Me.TextBox36.Value = Format(FoundCell.Offset(0, 48).Value, "dd-mmm-yy")
Me.TextBox37.Value = Format(FoundCell.Offset(0, 49).Value, "dd-mmm-yy")
Me.TextBox38.Value = Format(FoundCell.Offset(0, 50).Value, "dd-mmm-yy")

Else
Me.TextBox36.Value = ""
Me.TextBox37.Value = ""
Me.TextBox38.Value = ""

If you need more of the code let me know.
Thank you

--
Tdp


"Bob Phillips" wrote:

Do you have any code associated with the other textboxes?

--
__________________________________
HTH

Bob

"Tdp" wrote in message
...
THi,
I have a Textbox when entered a date, it adds 90 days and 120 days to
that
date and enteres these dates to two more textboxes.
The trouble I am having is when a date is entered in error in the first
Textbox I can not clear it. The cursor freezes. Any ideas?
The code I am using is as follows:

Private Sub TextBox36_BeforeUpdate(ByVal Cancel As
MSForms.ReturnBoolean)
TextBox36.Value = Format(TextBox36.Value, "dd-mmm-yy")
If IsDate(Me.TextBox36.Value) Then
Me.TextBox37.Value _
= Format(CDate(Me.TextBox36.Value) + 90, "dd-mmm-yy")
Me.TextBox38.Value _
= Format(CDate(Me.TextBox36.Value) + 120, "dd-mmm-yy")
Else
Beep
Cancel = True
End If
End Sub

Tdp

TextBox will not clear date???????????
 

Thats correct. I included it in with TextBox36 as follows:

Private Sub TextBox36_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox36.Value = Format(TextBox36.Value, "dd-mmm-yy")
If IsDate(Me.TextBox36.Value) Then
Me.TextBox37.Value _
= Format(CDate(Me.TextBox36.Value) + 90, "dd-mmm-yy")
Me.TextBox38.Value _
= Format(CDate(Me.TextBox36.Value) + 120, "dd-mmm-yy")
Else
Beep
Cancel = True
End If
End Sub

The code (cut down) looks like this:

Private Sub CommandButton1_Click()
Dim LastRow As Range
Dim iRow As Long
Dim FirstRow As Long
Dim LastRowNumber As Long
Dim wks As Worksheet
Application.DisplayAlerts = False
Application.EnableEvents = False

Set LastRow = Sheet2.Range("a100").End(xlUp)
LastRow.Offset(1, 48).Value = TextBox36.Text
LastRow.Offset(1, 49).Value = TextBox37.Text
LastRow.Offset(1, 50).Value = TextBox38.Text
MsgBox ("Data has been entered")
For Each wks In Worksheets(Array("customers"))
With wks
FirstRow = 2 'headers in row 1
'Changed LastRow to LastRowNumber
'Dim LastRowNumber as Long added above
'LastRow is declared as Range
'We need it to be Long
LastRowNumber = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRowNumber Step 1
If Application.CountIf(.Range("a2").EntireColumn, _
.Cells(iRow, "A").Value) 1 Then
'it's a duplicate
MsgBox .Cells(iRow, "A").Value
.Rows(iRow).Delete
End If
Next iRow
End With
Next wks
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

Private Sub CommandButton2_Click()
'Search button
Dim FoundCell As Range
Application.DisplayAlerts = False
Application.EnableEvents = False

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.TextBox3.Value = FoundCell.Offset(0, 2).Value
If IsDate(FoundCell.Offset(0, 1).Value) Then
Me.TextBox36.Value = Format(FoundCell.Offset(0, 48).Value, "dd-mmm-yy")
Me.TextBox37.Value = Format(FoundCell.Offset(0, 49).Value, "dd-mmm-yy")
Me.TextBox38.Value = Format(FoundCell.Offset(0, 50).Value, "dd-mmm-yy")
Else
Me.TextBox36.Value = ""
Me.TextBox37.Value = ""
Me.TextBox38.Value = ""
End If


Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

Private Sub TextBox36_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox36.Value = Format(TextBox36.Value, "dd-mmm-yy")
If IsDate(Me.TextBox36.Value) Then
Me.TextBox37.Value _
= Format(CDate(Me.TextBox36.Value) + 90, "dd-mmm-yy")
Me.TextBox38.Value _
= Format(CDate(Me.TextBox36.Value) + 120, "dd-mmm-yy")
Else
Beep
Cancel = True
End If
End Sub

Obviously there are alot more textboxes but that is the general outline of
the code.
--
Tdp


"Bob Phillips" wrote:

Does that mean that neither TextBox37 or 38 have their own event code?

--
__________________________________
HTH

Bob

"Tdp" wrote in message
...
The codes I have are as follows:

To insert the dates in data sheet:

LastRow.Offset(1, 48).Value = TextBox36.Text
LastRow.Offset(1, 49).Value = TextBox37.Text
LastRow.Offset(1, 50).Value = TextBox38.Text

To look up data from data sheet:

Me.TextBox36.Value = Format(FoundCell.Offset(0, 48).Value, "dd-mmm-yy")
Me.TextBox37.Value = Format(FoundCell.Offset(0, 49).Value, "dd-mmm-yy")
Me.TextBox38.Value = Format(FoundCell.Offset(0, 50).Value, "dd-mmm-yy")

Else
Me.TextBox36.Value = ""
Me.TextBox37.Value = ""
Me.TextBox38.Value = ""

If you need more of the code let me know.
Thank you

--
Tdp


"Bob Phillips" wrote:

Do you have any code associated with the other textboxes?

--
__________________________________
HTH

Bob

"Tdp" wrote in message
...
THi,
I have a Textbox when entered a date, it adds 90 days and 120 days to
that
date and enteres these dates to two more textboxes.
The trouble I am having is when a date is entered in error in the first
Textbox I can not clear it. The cursor freezes. Any ideas?
The code I am using is as follows:

Private Sub TextBox36_BeforeUpdate(ByVal Cancel As
MSForms.ReturnBoolean)
TextBox36.Value = Format(TextBox36.Value, "dd-mmm-yy")
If IsDate(Me.TextBox36.Value) Then
Me.TextBox37.Value _
= Format(CDate(Me.TextBox36.Value) + 90, "dd-mmm-yy")
Me.TextBox38.Value _
= Format(CDate(Me.TextBox36.Value) + 120, "dd-mmm-yy")
Else
Beep
Cancel = True
End If
End Sub

Tdp