userform data entry restrictions.

[Mr.Daines]

Hi all,

I have a userform which asks for certain details such as; date, time. but within the form anyone can enter what they like, is there anyway i can get the user form to display eg date dd/mm/yy in the box for people to see as a reference and to set the textbox to only accept values to that format?

regards.

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 Trim(Me.TextBoxDate.Value) = "" Then
  Me.TextBoxDate.SetFocus
  MsgBox "Please enter Date."
  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

 

[Sektor]

Use Comboboxes with Style = fmStyleDropDownList property instead of Textboxes.

 

[Mr.Daines]

Use Comboboxes with Style = fmStyleDropDownList property instead of Textboxes.

Hi Sektor,

That sounds great but, i'm really new to all of this, how do i specify the contents lock? i.e. date dd/mm/yy, time 00:00, post code #### ###.

Regards,

 

[Mr.Daines]

Can anyone tell me what i am doing wrong here?

It doesn't matter what i enter it will not accept the value, i just get my msgbox error.

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