+ Reply to Thread
Results 1 to 26 of 26

Show Current Date In TextBox

  1. #1
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Question Show Current Date In TextBox

    Hello again,
    I have a textbox in my workbook that looks something like this:

    MK
    19-JUN-12

    I'm trying to find a way to automatically keep the date current. The initials 'MK' still need to be editable as well. Any ideas?
    There is so much good in the worst of us,
    And so much bad in the best of us,
    That it hardly behooves any of us
    To talk about the rest of us.

  2. #2
    Registered User
    Join Date
    07-12-2012
    Location
    New Hampshire, United States
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Show Current Date In TextBox

    If you put this code in the sheet object it will run every time the sheet is activated:

    Please Login or Register  to view this content.
    Remember to change the value in red to the correct cell. If you want it to run via a different method let me know.

    EDIT: Didn't notice that you wanted MK to be editable let me edit my code.

    If you merge the cell you want updated with the cell above it you can edit the "MK" in the cell above while the cell you want is auto updated on Worksheet_Activate. I will try to think of a better way

    Please Login or Register  to view this content.
    Last edited by carrob; 08-03-2012 at 09:29 AM.

  3. #3
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Show Current Date In TextBox

    Hi

    You can create a macro and assign a macro to the TextBox.

    For example you could put this code in the macro:

    Textbox1.Text = Format(Date,"dd mmm yyyy")

    Hope this helps.

    Good luck.

    Tony

  4. #4
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: Show Current Date In TextBox

    see if it helps
    from textbox properties set multiline to "true";
    if your textbox is in a userform you can use
    Please Login or Register  to view this content.
    if not
    Please Login or Register  to view this content.
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

  5. #5
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Show Current Date In TextBox

    Thank you carrob - I don't think that will work for me as I'm not using a cell but a textbox which can be moved around on the sheet.

    ARGK - that looks close to what I'm after, thank you - but I'm having trouble assigning the code. What's the right way of doing that?

    john55! How are you, buddy? Thank you for your reply - how in the world do I set 'multiline' to True?

  6. #6
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: Show Current Date In TextBox

    Hi!
    right click on yr textbox and see properties, then in the left side see Multiline, set it to true.
    sword.jpg
    Last edited by john55; 08-03-2012 at 10:21 AM.

  7. #7
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Show Current Date In TextBox

    When I right-click the textbox, the closest thing I see is 'Size and Properties...', when I click that, I don't see anything called 'Multiline' on the left.

  8. #8
    Registered User
    Join Date
    07-12-2012
    Location
    New Hampshire, United States
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Show Current Date In TextBox

    Make sure you created an ActiveX Text Box. When you go to "Developer" and click "Insert" ActiveX controls are the bottom portion of the box that opens.
    When you click "Properties" a box should open up with a list of properties and "MultiLine" will be on that list

  9. #9
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: Show Current Date In TextBox

    see the test file attached
    Attached Files Attached Files

  10. #10
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Show Current Date In TextBox

    Thank you carrob, that works better.

    Hmm, I think I'll have to put another textbox specifically for editable initials right above the date textbox, as I'm having trouble with the 'MK' being easily editable.

    I'm still not able to get a current date to appear though. How do I apply a specific macro to the textbox? I'm not able to select the textbox in your sample at all, john55.

    UPDATE: Yikes, I think I did something - I hit 'Run' in the VBA mode, and now I have the current date - but how do I select the box? I need to be able to move it around.
    Last edited by swordswinger710; 08-03-2012 at 11:37 AM.

  11. #11
    Registered User
    Join Date
    07-12-2012
    Location
    New Hampshire, United States
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Show Current Date In TextBox

    Go to the "Developer" tab and make sure "Design Mode" is activated.

  12. #12
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Show Current Date In TextBox

    Ooh, okay - that works, but will I always have to make sure that's turned on in order to move it? Is there no way a regular moveable textbox can contain a code or formula or something?

    UPDATE: Also, the date isn't updating when I change my computer's date to tomorrow and open the workbook.

  13. #13
    Registered User
    Join Date
    07-12-2012
    Location
    New Hampshire, United States
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Show Current Date In TextBox

    Yes unfortunately you will have to enter "Design mode" every time you want to move it. As for the date not updating I am not sure, it works fine for me, could you attach your workbook?

  14. #14
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Show Current Date In TextBox

    Well, I don't think that will work for me, as this textbox needs to be moved a LOT. Is there no way to have a normal textbox automatically update?

  15. #15
    Registered User
    Join Date
    07-12-2012
    Location
    New Hampshire, United States
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Show Current Date In TextBox

    If you want you could use a Label instead of a Text Box, just change the code to this:

    Please Login or Register  to view this content.
    Also, make sure the Label does not have any code assigned to it, otherwise you may have trouble moving it around. Let me know if this works

  16. #16
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Show Current Date In TextBox

    Call me dumb, but I can't figure out how to add a label.

  17. #17
    Registered User
    Join Date
    07-12-2012
    Location
    New Hampshire, United States
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Show Current Date In TextBox

    Go to the "Developer" tab, "Insert", under "Form Controls" click the Aa button called "Label"

  18. #18
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Show Current Date In TextBox

    Ah, thank you! Okay - I've attached a sample - is the date updating for you? It isn't for me - and I'm not sure why..
    Attached Files Attached Files
    Last edited by swordswinger710; 08-03-2012 at 03:31 PM.

  19. #19
    Registered User
    Join Date
    07-12-2012
    Location
    New Hampshire, United States
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Show Current Date In TextBox

    Yes it is updating for me. Just in case you weren't aware, the Worksheet_Activate is built in and runs every time the sheet is activated, so in order for the date to update it has to lost focus then gain focus, so try clicking onto "Sheet 2" then clicking back to "Sheet 1", that is how it will update. It should also update when you open the excel workbook.

  20. #20
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: Show Current Date In TextBox

    as an option
    Attached Files Attached Files

  21. #21
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Show Current Date In TextBox

    Thank you carrob! I think this will work - but how do I format the text, such as font, size, positioning and the like? I cannot seem to find the Properties for a label anywhere.

    Thank you as well, john55! I don't think that last one will work in my case though.

  22. #22
    Registered User
    Join Date
    07-12-2012
    Location
    New Hampshire, United States
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Show Current Date In TextBox

    Unfortunately you cannot edit the font in a form label. However try this:

    Go to the "Insert" tab and click "Text Box" you can move this around the page and change the font. You will also need to change your code to this:

    Please Login or Register  to view this content.
    You can also have the text box stay in the top left when you click with this code, just paste it under the code above:

    Please Login or Register  to view this content.
    Or if you want the top right replace the red text above with this:

    Please Login or Register  to view this content.
    Last edited by carrob; 08-08-2012 at 10:59 AM.

  23. #23
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: Show Current Date In TextBox

    hi, I thought that a userform can be moved easily...

  24. #24
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Show Current Date In TextBox

    Thanks again john55, I'm sure you're right, but carrob's solution was exactly what I was looking for. Thank you both.

    One more thought/question - is there a way to prevent a user from changing the date in the textbox manually? So for example, if he clicked it, a message would appear telling him/her not to? It's no big deal if that won't work, I'm just wondering.

  25. #25
    Registered User
    Join Date
    07-12-2012
    Location
    New Hampshire, United States
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Show Current Date In TextBox

    Using the following code will make it unable to select the textbox at all, however if you are using the code I provided earlier which automatically moves the textbox you should not have a problem:

    Please Login or Register  to view this content.
    place in a module.

  26. #26
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Show Current Date In TextBox

    Thank you once again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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