Why wont this work.... please help, going insane!

[Mr.Daines]

All i want is to control the format of the data input to a text box, ie; Date = dd/mm/yy, Time = 00:00, Reference = (maximum 6 digits, numbers only), Post code = #### ###.

Any ideas why the below keeps bringing up my msgbox?

If Me.TextBoxDate.Value <> Format(Date, "DD/MM/YY") Then
  MsgBox "Please enter Date in DD/MM/YY format."
  Me.TextBoxDate.SetFocus
  Me.TextBoxDate.Value = ""
  Exit Sub
End If

 

[kpark91]

It is requiring you to type in only dates.
So, anything other than
07/31/11
08/25/10

would pop up a msgbox saying "please enter date in DD/MM/YY format."

If you just want to get rid of the msgbox then just delete this line

MsgBox "Please enter Date in DD/MM/YY format."

 

[Mr.Daines]

It is requiring you to type in only dates.
So, anything other than
07/31/11
08/25/10

would pop up a msgbox saying "please enter date in DD/MM/YY format."

If you just want to get rid of the msgbox then just delete this line

MsgBox "Please enter Date in DD/MM/YY format."

I need to keep the message, the purpose of the code is to prevent people from entering anything other than the date.

Regards,

 

[Jonmo1]

If you are trying to restrict the user to entering only today's date,
why bother making them enter the date at all?
just use Date in your subsequent code, this will always refer to today's date.

 

[Trevor G]

Here is some code I use to check entries from an inputbox see if it helps

Sub Var1()
Dim varInput As Variant
varInput = InputBox("Please enter number, string or date")
If IsNumeric(varInput) Then
MsgBox (Format(varInput, "#,##0.00;(#,##0.00)"))
ElseIf IsDate(varInput) Then
MsgBox (" The date is " & Format(varInput, "dd mm yyyy"))
Else
MsgBox ("The string is " & varInput)
End If
End Sub

 

[Mr.Daines]

If you are trying to restrict the user to entering only today's date,
why bother making them enter the date at all?
just use Date in your subsequent code, this will always refer to today's date.

I thought that to, but; the problem is that people need to put post dated entries in, for example, person a sold something yesterday, forgot to submit, submits the next day or later.

Regards,

 

[Jonmo1]

OK, well in thoery, this line

If Me.TextBoxDate.Value <> Format(Date, "DD/MM/YY") Then

seems to be trying to restrict the entry to ONLY today's date....

Try Trevor's suggestion for restricting it to a date type entry...

 

[Mr.Daines]

Here is some code I use to check entries from an inputbox see if it helps

Hi Trevor,

That looks more along the lines of what i require but i am unsure where i would need to place it within my code.

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim wb As Workbook
'checks for data entry in each drop down
If Me.TextBoxDate.Value <> Format(Date, "MM/DD/YY") Then
  MsgBox "Please enter Date in MM/DD/YY format."
  Me.TextBoxDate.SetFocus
  Me.TextBoxDate.Value = ""
  Exit Sub
End If
If Trim(Me.TextBoxTime.Value) = "" Then
  Me.TextBoxTime.SetFocus
  MsgBox "Please enter Time."
  Exit Sub
End If
If Trim(Me.ComboBoxAdv.Value) = "" Then
  Me.ComboBoxAdv.SetFocus
  MsgBox "Please select an advisor."
  Exit Sub
End If
If Trim(Me.ComboBoxTeam.Value) = "" Then
  Me.ComboBoxTeam.SetFocus
  MsgBox "Please select Team."
  Exit Sub
End If
If Trim(Me.TextBoxRef.Value) = "" Then
  Me.TextBoxRef.SetFocus
  MsgBox "Please enter reference number."
  Exit Sub
End If
If Trim(Me.TextBoxCust.Value) = "" Then
  Me.TextBoxCust.SetFocus
  MsgBox "Please enter Customers Name."
  Exit Sub
End If
If Trim(Me.TextBoxPC.Value) = "" Then
  Me.TextBoxPC.SetFocus
  MsgBox "Please enter customers Postcode."
  Exit Sub
End If
If Trim(Me.ComboBoxRef.Value) = "" Then
  Me.ComboBoxRef.SetFocus
  MsgBox "Please select who you have referred the lead to."
  Exit Sub
  
End If
'Checks if master in use
Set wb = Workbooks.Open("S:\STB June Fee Data.xlsx")
While wb.ReadOnly
    wb.Close
    Set wb = Nothing
    Set wb = Workbooks.Open("S:\STB June Fee Data.xlsx")
    DoEvents
Wend
Set ws = wb.Worksheets("Raw Data")
'finds first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 1).Row
  
'places data into master call log
ws.Cells(iRow, 1).Value = ThisWorkbook.Name
ws.Cells(iRow, 2).Value = Me.TextBoxDate.Value
ws.Cells(iRow, 3).Value = Me.TextBoxTime.Value
ws.Cells(iRow, 4).Value = Me.ComboBoxAdv.Value
ws.Cells(iRow, 5).Value = Me.ComboBoxTeam.Value
ws.Cells(iRow, 6).Value = Me.TextBoxRef.Value
ws.Cells(iRow, 7).Value = Me.TextBoxCust.Value
ws.Cells(iRow, 8).Value = Me.TextBoxPC.Value
ws.Cells(iRow, 9).Value = Me.ComboBoxRef.Value
ActiveWorkbook.Close SaveChanges:=True
'Confirms data logged
MSG1 = MsgBox("Submission successful, do you wish to refer another?", vbYesNo, "Congratulations!")

If MSG1 = vbNo Then
    Unload Me
Else
Me.TextBoxDate.Value = ""
Me.TextBoxTime.Value = ""
Me.ComboBoxAdv.Value = ""
Me.ComboBoxTeam.Value = ""
Me.TextBoxRef.Value = ""
Me.TextBoxCust.Value = ""
Me.TextBoxPC.Value = ""
Me.ComboBoxRef.Value = ""
Me.TextBoxDate.SetFocus
End If
End Sub

Private Sub cmdReset_Click()
Me.TextBoxDate.Value = ""
Me.TextBoxTime.Value = ""
Me.ComboBoxAdv.Value = ""
Me.ComboBoxTeam.Value = ""
Me.TextBoxRef.Value = ""
Me.TextBoxCust.Value = ""
Me.TextBoxPC.Value = ""
Me.ComboBoxRef.Value = ""
Me.TextBoxDate.SetFocus
End Sub

 

[Mr.Daines]

bump

 

[Jonmo1]

Try changing

If Me.TextBoxDate.Value <> Format(Date, "MM/DD/YY") Then
to
If Not IsDate(Me.TextBoxDate.Value) Then