+ Reply to Thread
Results 1 to 8 of 8

Prefill text box in userform with today's date but allow user to enter unique date

  1. #1
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Prefill text box in userform with today's date but allow user to enter unique date

    Good evening. I have a workbook that I'm using to tracking staffing patterns within a mental health agency. When the workbook opens the user is asked to pick a date range and an office location. I've placed code into the userform that pre-fills the "start date" with today's date and the "end date" 7 days from today's date. I would like the user to be able to enter a unique date range should they wish but I have yet to figure out the coding to accomplish my goal.

    I've attached a copy of the workbook to this thread as well as placed the code in question below. Any feedback you might be able to offer would be great. Thanks.


    Please Login or Register  to view this content.
    Matthew
    Attached Files Attached Files

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,748

    Re: Prefill text box in userform with today's date but allow user to enter unique date

    Instead of putting in the initialize code, try putting it in the value field in the properties for the two text boxes. Use Today() and Today() + 7. See if that makes a difference.

    Four more weeks until we get to Litchfield.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Prefill text box in userform with today's date but allow user to enter unique date

    Quote Originally Posted by alansidman View Post
    Instead of putting in the initialize code, try putting it in the value field in the properties for the two text boxes. Use Today() and Today() + 7. See if that makes a difference.

    Four more weeks until we get to Litchfield.
    Alan,

    First things first......I'll send you my cell number via PM and if you have a free evening we'd love to treat you and your family to dinner at our place. As you know, we're about 15 minutes from you when you're in Litchfield.

    Now to the form......I tried entering Today() and Today() + 7 as the value for TBStartDate and TBEndDate (text box names in UserForm2) and when I call UserForm2 "Today()" and "Today() + 7" appear in their respective text boxes. Was that the intent or am I doing something wrong? Thanks.

    Matthew

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,105

    Re: Prefill text box in userform with today's date but allow user to enter unique date

    Hi, Matthew,

    maybe just format as wanted:
    Please Login or Register  to view this content.
    BTW: my Excel didnīt like the line
    Please Login or Register  to view this content.
    I switched it to read
    Please Login or Register  to view this content.
    but you could change ActiveWorkbook to ThisWorkbook as the opened workbook will alaways be the active one when opened and it holds the code.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  5. #5
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Prefill text box in userform with today's date but allow user to enter unique date

    Holger,

    Thanks for weighing in. I tried that line of code you suggested for the UserInterface and I'll let you know how it holds in my system....you're logic makes sense. Regarding the code for the date range, I think I need to explain myself a bit further as I may have left an important part out of the scenario.......

    As stated previously, the code for the date range in UserForm2 is set up such that today's date is entered into the Start Date and a date 7 days out is entered into the End Date field. My users can enter any date range they wish but what happens is that once UserForm2 is terminated the date range defaults back to what the code dictates. The purpose of UserForm2 is to feed the office location and date range to two locations: 1) footer and 2) Userform1.

    So, for instance, if my user wanted to enter a date range of 4-4-14 to 5-4-14 today into UserForm2, once UserForm2 was terminated and Userform1 was opened, at the top of Userform1 the date range would have defaulted back to 5-30-14 to 6-6-14.

    Does this make any more sense than what I originally posted? I hope so. Thanks again.

    Matthew

  6. #6
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,105

    Re: Prefill text box in userform with today's date but allow user to enter unique date

    Hi, Matthew,

    as the data is written into cells and read back from there I donīt get the point but I found a wrong formatting in the advicdes (my bad). The line for the end date should only hold 4 Y instead of 5 displayed in my sample (sorry for that).

    Ciao,
    Holger

  7. #7
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Prefill text box in userform with today's date but allow user to enter unique date

    Holger,

    Good morning. I hope you had a great weekend. Over the weekend I figured out my problem. I am able to enter a unique date range into my userform2 BUT for some reason I have to open userform1, close userform1 and then re-open userform1 to get the date range in userform1 to update. Not sure why this is happening. Any idea? Thanks.

    Matthew

  8. #8
    Forum Contributor
    Join Date
    02-22-2014
    Location
    Winthrop, ME
    MS-Off Ver
    Excel 2010
    Posts
    420

    Re: Prefill text box in userform with today's date but allow user to enter unique date

    Holger,

    Hello again. The nature of my question in post #7 is different than the original question for this thread. I'm going to mark this thread solved and start a new thread. Thanks again.

    Matthew

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to auto enter today's date into cells
    By Curious Dude in forum Excel General
    Replies: 1
    Last Post: 03-09-2014, 12:43 PM
  2. [SOLVED] how to enter today's date into cell
    By johnandrews in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-13-2014, 04:44 AM
  3. Count Unique Text & blanks in one column prior to Today's date
    By VBSK8R in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-21-2011, 01:25 PM
  4. to enter today's date if a cell is blank
    By Lynn Hanna in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-26-2006, 08:10 AM
  5. in excel how do enter today's date on a laptop
    By bency in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 08-29-2005, 05:05 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1