Force Date Format for a User Form

[scotball]

Hi folks,

I have inherited an evaluation form and have a couple of improvements I want to do to it.

There are 2 date fields on the form, Session Date and DOB. There is no formatting control on this so I want to force the date entered to be formatted correctly and throw a message box up if someone tries to use a different format.

Found this code but I need to tweak it so that it's pulling date from the form rather than a cell location:

Dim dDate As DatePrivate Sub CommandButton1_Click()
    Sheet1.Range("G1").Value = dDate
End Sub
 
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Mid(TextBox1.Value, 4, 2) > 12 Then
        MsgBox "Invalid date format, please re-enter as dd/mm/yyyy", vbCritical
        TextBox1.Value = vbNullString
        TextBox1.SetFocus
        Exit Sub
    End If
     
    dDate = DateSerial(Year(Date), Month(Date), Day(Date))
    TextBox1.Value = Format(TextBox1.Value, "dd/mm/yyyy")
    dDate = TextBox1.Value
End Sub

In the VBA the boxes are called: DOBTextBox and SessionsBox

I want to ensure that the users enter the dates as dd/mm/yyyy in the Form but also when the macro copies the information from the form into a sheet that it retains the correct formatting.

Issue arises when someone enters 03/06/2008 as DOB but when it copies to the sheet the formatting switches to 06/03/2008.

Any guidance would be much appreciated.
Lee

 

[scotball]

Could I just change it to:

Private Sub CommandButton1_Click()    DOBTextBox.Value = dDate
End Sub

and this would work when the user inputs a date into the box?

 

[dmt32]

Hi,
try

Dim dDate As Date
Private Sub CommandButton1_Click()
    With Sheet1.Range("G1")
        .Value = dDate
        .NumberFormat = "dd/mm/yyyy"
    End With
End Sub






Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.TextBox1
        If Len(.Text) > 0 Then
            If Not .Text Like "##/##/####" Then
                MsgBox "Invalid date format" & Chr(10) & "Please re-enter as dd/mm/yyyy", vbCritical, "Invalid Format"
                Cancel = True
            Else
                dDate = DateValue(.Text)
            End If
        End If
    End With
End Sub

Dave

 

[scotball]

Thanks Dave,

Can you help/explain... the line: With Sheet1.Range("G1") ... this would read to me as from Sheet1!G1 this is where dDate would get it's value from?

If the value is to come from an input box in a Form called DOBTextBox, how would I change the code to get the correct information?

 

[dmt32]

Thanks Dave,

Can you help/explain... the line: With Sheet1.Range("G1") ... this would read to me as from Sheet1!G1 this is where dDate would get it's value from?

If the value is to come from an input box in a Form called DOBTextBox, how would I change the code to get the correct information?

From VBA Helpfile

The With statement allows you to perform a series of statements on a specified object without requalifying the name of the object.

You can read more in the helpfile

Just change TextBox1 to the actual name of your textbox control

Dave

 

[scotball]

If GroupListBox.Value = "" ThenMsgBox "Please add in Location", vbExclamation
  Exit Sub
End If

The List box has items in there but I want the message to pop up if the user doesnt select one of the items... so far the code above doesnt work... the other code you gave me works a treat!! Thank you.

 

[dmt32]

If GroupListBox.Value = "" ThenMsgBox "Please add in Location", vbExclamation
  Exit Sub
End If

The List box has items in there but I want the message to pop up if the user doesnt select one of the items... so far the code above doesnt work... the other code you gave me works a treat!! Thank you.

That's another question - post in new thread with copy of your code.

Glad solution helped.

Dave