Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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.

    VB:
    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
    7,941

    Re: Setting Date Picker Default To Today's Date

    Did you try putting the line
    VB:
    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
    18,508

    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
    7,941

    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