No announcement yet.

Setting Date Picker Default To Today's Date

  • Filter
  • Time
  • Show
Clear All
new posts

  • Setting Date Picker Default To Today's Date

    Hi All,

    I am using Excel 2010 and have a userform with a datepicker in it. The problem is that the default date is set to the day that the form was created. I want the default to be todays date (what ever date that may be). I have seen a few possible solutions online but i will be honest, they are over my head. Here is the code that i am working with in the user form.

    Private Sub addnewbutton_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Work Orders")
    'find first empty row in database
    iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    'check for a customer name
    If Trim(Me.customertextbox.Value) = "" Then
      MsgBox "Please enter a customer name"
      Exit Sub
    End If
    'check for a due date
    If Trim(Me.DTPicker1.Value) = Date Then
      MsgBox "Please enter a due date"
      Exit Sub
    End If
    'check for a customer po / DWD PO#, or Decription
    If Trim(Me.customerpotextbox.Value) = "" And Trim(Me.dwdnumbertextbox.Value) = "" And Trim(Me.descriptiontextbox.Value) = "" Then
                MsgBox "You Must Enter Either A PO#, WO#, or Description"
        Exit Sub
    Else: End If
    'add selected departments
    If stockcheckbox.Value Then ws.Cells(iRow, 11).Value = "Yes" Else ws.Cells(iRow, 11).Value = "No"
    If customcheckbox.Value Then ws.Cells(iRow, 12).Value = "Yes" Else ws.Cells(iRow, 12).Value = "No"
    If jambcheckbox.Value Then ws.Cells(iRow, 13).Value = "Yes" Else ws.Cells(iRow, 13).Value = "No"
    If productioncheckbox.Value Then ws.Cells(iRow, 14).Value = "Yes" Else ws.Cells(iRow, 14).Value = "No"
    'copy the data to the database
    ws.Cells(iRow, 2).Value = Me.customertextbox.Value
    ws.Cells(iRow, 3).Value = Me.customerpotextbox.Value
    ws.Cells(iRow, 4).Value = Date
    ws.Cells(iRow, 5).Value = Me.DTPicker1.Value
    ws.Cells(iRow, 6).Value = Me.dwdnumbertextbox.Value
    ws.Cells(iRow, 7).Value = Me.descriptiontextbox.Value
    ws.Cells(iRow, 8).Value = "On Time"
    'clear the data
    Me.customertextbox.Value = ""
    Me.customerpotextbox.Value = ""
    Me.DTPicker1.Value = Date
    Me.dwdnumbertextbox.Value = ""
    Me.descriptiontextbox.Value = ""
    Me.stockcheckbox.Value = "False"
    Me.customcheckbox.Value = "False"
    Me.jambcheckbox.Value = "False"
    Me.productioncheckbox.Value = "False"
    Unload Me
    End Sub
    I tried adding Me.Datepicker1.Value = Date at the top but it didnt do anything.

    Anyone have a suggestion that isnt too complicated?


  • #2
    Re: Setting Date Picker Default To Today's Date

    Did you try putting the line
     Me.Datepicker1.Value = Date
    in the userform_initialize event...?


    • #3
      Re: Setting Date Picker Default To Today's Date


      Honestly, i dont know. Like i say, i am still new to coding vba. If the userform-initialize event is the macro that calls the form, then yes. i get a compile error: Invalid use of ME keyword. Right now the only thing in that macro is workorderform.Show. I have attached a copy of the file if that helps at all. Thanks,


      Work Orders (Sample).xlsm


      • #4
        Re: Setting Date Picker Default To Today's Date

        The Initialize event is part of the UserForm modules.

        Open the VB Editor and double click on the userform. This will open the userform's code module. On the right at the top of the window there is a drop down that lists all the events, including initialize. That's where your code should be
        Hope that Helps


        New users should read the Forum Rules before posting

        For free Excel tools & articles visit my web site

        If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

        RoyUK's Web Site

        royUK's Database Form

        Where to paste code from the Forum

        About me.


        • #5
          Re: Setting Date Picker Default To Today's Date

          1st lesson in coding... Learn what events are.

          The userform_initialize event fires when the form is created. If you go to the VBA editor, find that event in the userform code (Use the dropdowns at the top of the code window, select 'UserForm' in the dropdown on the left and Initialize in the RHS dropdown, you'll get there.

          Add the one liner from my other post - it'll work. If it doesn't, you're still doing something wrong.


          • #6

            Re: Setting Date Picker Default To Today's Date

            Thank you both. Consider lesson 1 learned. Works great. I had the right code i just had no idea where to put it. Thanks again.