Input a date into a VBA form in Excel

[PaulRob]

I am trying to have the user put in a DATE into a user form I have written into a VBA on the back of an Excel Spreadsheet.

Currently I have a txt box with the following coding:

Private Sub txtInServiceDate_Change()


End Sub

What/how do I change this so that the User inputs a date in the format mm/dd/yyyy please?

Thank you

 

[nemmi69]

Private Sub txtInServiceDate_Change()

if not ISDATE(txtInServiceDate.Object.Text) then
    MsgBox"The data entered is not a date",vbcritical
else
    ' If you wish a specific format
    txtInServiceDate.Object.Text = Format(DateStr, "DD/MM/YYYY")
endif
End Sub

 

[dmt32]

Hi
welcome to forum

Don't worry too much about the format user enters a date - just check what they have entered is a date & then change it to your required format

Private Sub txtInServiceDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Const DateFormat As String = "mm/dd/yyyy"
   
    With Me.txtInServiceDate
       Cancel = Len(.Value) > 0 And Not IsDate(.Value)
       If Not Cancel Then .Value = Format(.Value, DateFormat) Else .Value = ""
     End With
       
    If Cancel Then MsgBox "Please Enter A Valid In Service Date", 48, "Invalid"
   
End Sub

If user enters Dec 18 2023 should change to your format when they exit the control

Dave

 

[PaulRob]

Private Sub txtInServiceDate_Change()

if not ISDATE(txtInServiceDate.Object.Text) then
    MsgBox"The data entered is not a date",vbcritical
else
    ' If you wish a specific format
    txtInServiceDate.Object.Text = Format(DateStr, "DD/MM/YYYY")
endif
End Sub

Many thanks

Will check it out and use either this solution or the other one suggested!

Paul

 

[PaulRob]

Hi
welcome to forum

Don't worry too much about the format user enters a date - just check what they have entered is a date & then change it to your required format

Private Sub txtInServiceDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Const DateFormat As String = "mm/dd/yyyy"
  
    With Me.txtInServiceDate
       Cancel = Len(.Value) > 0 And Not IsDate(.Value)
       If Not Cancel Then .Value = Format(.Value, DateFormat) Else .Value = ""
     End With
      
    If Cancel Then MsgBox "Please Enter A Valid In Service Date", 48, "Invalid"
  
End Sub

If user enters Dec 18 2023 should change to your format when they exit the control

Dave

Many thanks

Will check it out and use either this solution or the other one suggested!

Paul

 

[PaulRob]

Hi
welcome to forum

Don't worry too much about the format user enters a date - just check what they have entered is a date & then change it to your required format

Private Sub txtInServiceDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Const DateFormat As String = "mm/dd/yyyy"
  
    With Me.txtInServiceDate
       Cancel = Len(.Value) > 0 And Not IsDate(.Value)
       If Not Cancel Then .Value = Format(.Value, DateFormat) Else .Value = ""
     End With
      
    If Cancel Then MsgBox "Please Enter A Valid In Service Date", 48, "Invalid"
  
End Sub

If user enters Dec 18 2023 should change to your format when they exit the control

Dave

Many thanks

Will check it out and use either this solution or the other one suggested!

Paul

 

[PaulRob]

Hi
welcome to forum

Don't worry too much about the format user enters a date - just check what they have entered is a date & then change it to your required format

Private Sub txtInServiceDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Const DateFormat As String = "mm/dd/yyyy"
  
    With Me.txtInServiceDate
       Cancel = Len(.Value) > 0 And Not IsDate(.Value)
       If Not Cancel Then .Value = Format(.Value, DateFormat) Else .Value = ""
     End With
      
    If Cancel Then MsgBox "Please Enter A Valid In Service Date", 48, "Invalid"
  
End Sub

If user enters Dec 18 2023 should change to your format when they exit the control

Dave

Similar question please Dave - how do I format to $x.xx (2 decimal places)? Or should I start a new thread please?

 

[dmt32]

Similar question please Dave - how do I format to $x.xx (2 decimal places)?

Similar way but probably in this case you can use the AfterUpdate event of the control

Private Sub TextBox1_AfterUpdate()
    With Me.TextBox1
        .Value = Format(Val(.Value), "$#,##0.00")
    End With
End Sub

Do be mindful that what comes out of the control is Text & if want the same format to apply when posting to a range you should consider using an appropriate type-conversion-functions
to coerce to required data type & apply number format to the range.

Dave

 

[PaulRob]

Similar way but probably in this case you can use the AfterUpdate event of the control

Private Sub TextBox1_AfterUpdate()
    With Me.TextBox1
        .Value = Format(Val(.Value), "$#,##0.00")
    End With
End Sub

Do be mindful that what comes out of the control is Text & if want the same format to apply when posting to a range you should consider using an appropriate type-conversion-functions
to coerce to required data type & apply number format to the range.

Dave

Thank you Dave.

I may have misunderstood something as in my spreadsheet I put in:

Private Sub ActualCost_AfterUpdate()
With Me.txtActualCost.value = Format(Val(.value), "$#,##0.00")
End With
End Sub

On Compile I get a Method or Data Member not found.

Is this because In MODULE I have this field showing as .Numformat.value = "" or .Cells(iRow, 12) = frmForm.Numformat in different parts of my Module code?

 

[dmt32]

code goes in your userform code page & you have not copied the code I posted correctly.

Private Sub txtActualCost_AfterUpdate()
    With Me.txtActualCost
        .Value = Format(Val(.Value), "$#,##0.00")
    End With
End Sub

Dave