Force Date Format for a User Form

scotball

Active Member
Joined
Oct 23, 2008
Messages
375
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:

Code:
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

Active Member
Joined
Oct 23, 2008
Messages
375
Could I just change it to:

Code:
Private Sub CommandButton1_Click()    DOBTextBox.Value = dDate
End Sub
and this would work when the user inputs a date into the box?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,267
Office Version
2013
Platform
Windows
Hi,
try

Code:
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

Active Member
Joined
Oct 23, 2008
Messages
375
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,267
Office Version
2013
Platform
Windows
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

Active Member
Joined
Oct 23, 2008
Messages
375
Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,267
Office Version
2013
Platform
Windows
Code:
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
 

Forum statistics

Threads
1,082,040
Messages
5,362,804
Members
400,693
Latest member
jenlj

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top