Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Setting Date Picker Default To Today's Date

  1. #1
    Join Date
    4th November 2010
    Posts
    38

    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.

    Code:
    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
      Me.customertextbox.SetFocus
      MsgBox "Please enter a customer name"
      Exit Sub
    End If
    
    'check for a due date
    If Trim(Me.DTPicker1.Value) = Date Then
      Me.DTPicker1.SetFocus
      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
            Me.customerpotextbox.SetFocus
                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"
    Me.customertextbox.SetFocus
    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?

    Dan

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    1st September 2010
    Posts
    10,362

    Re: Setting Date Picker Default To Today's Date

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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    4th November 2010
    Posts
    38

    Re: Setting Date Picker Default To Today's Date

    Ctytop,

    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,

    Dan

    Work Orders (Sample).xlsm

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,293

    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

    Roy

    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. #5
    Join Date
    1st September 2010
    Posts
    10,362

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    4th November 2010
    Posts
    38

    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.

    Dan

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 5
    Last Post: March 24th, 2012, 13:21
  2. Replies: 6
    Last Post: March 6th, 2012, 01:05
  3. Replies: 6
    Last Post: February 24th, 2011, 05:27
  4. Default Pivot Table Date Field To Today/Current Date
    By G8tor4life in forum EXCEL HELP
    Replies: 4
    Last Post: June 5th, 2008, 05:24
  5. Replies: 2
    Last Post: March 3rd, 2006, 03:38

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno