VBA Excel Provide current Date in Text box

Question

I have a dialog box that appears when the user clicks a macro button. This dialog box is mostly filled out (date, email, producer, website, etc are filled) and all the user needs to do is enter their name. The problem is that the date entered is static, so if I entered "3/3/11" it'll stay that way until someone changes it.

I was wondering if there was some way for that text box to always display the current date (unless the user decides to change it for whatever reason). I've tried putting different things into the "Value" section of the text box (such as "getDate()" and "= Date()") but so far haven't been successful.

Thank you,

Jesse Smothermon

Solution 1

Use the form Initialize event, e.g.:

Private Sub UserForm_Initialize()
    TextBox1.Value = Format(Date, "mm/dd/yyyy")
End Sub

Solution 2

The easy way to do this is to put the Date function you want to use in a Cell, and link to that cell from the textbox with the LinkedCell property.

From VBA you might try using:

textbox.Value = Format(Date(),"mm/dd/yy")

Solution 3

Set the value from code on showing the form, not in the design-timeProperties for the text box.

Private Sub UserForm_Activate()
    Me.txtDate.Value = Format(Date, "mm/dd/yy")
End Sub

Comments

[Jesse Smothermon]

So you mean like, hide it behind the macro button? And right now I feel like anything entered in the "Value" section of the text box is treated as a literal string, I'll look into LinkedCell, thank you

[Jesse Smothermon]

Where would you put code? The user form just pops up but there's no place to actually assign values. It's just the preassigned values I entered previously and whatever the user enters. I also don't want to change the value after all of the values have been entered in case if that's not what the user wanted. Thanks for the help

[Jesse Smothermon]

I tried that initially before I asked this question. I think I'm placing that code in the wrong area. Here's what happens: The user clicks a macro button, the button opens a user form, the user enters in whatever and clicks a "run" button. Would I put this code before the user form opens up? If so I get a run-time error "424" Object required. Thank you

[Graham]

Right click the form designer and select View Code. Put the above code there. Change TextBox1 to whatever your text box is called.

[Arnoud Kooi]

In the project explorer right click the form and open the code, then add the code in the UserForm_Initialize() eventhandler

[Jesse Smothermon]

Cool beans, it worked. Just on a side note when I went to "View Code" it automatically went to "UserForm_Click()" so I just changed it to "UserForm_Activate()". Thank you

[Jesse Smothermon]

I just realized that's what you told me at the beginning. My bad, thanks again

[assylias]

This would work as a formula in a cell but the question was for a textbox, so it needs to be a VBA function.

[Andrew]

Also, =today() is a volatile function, meaning that anything in the recalculation chain that references that cell also becomes volatile. Avoid!

[arcadeprecinct]

This does not answer the original question. Furthermore, you are setting the cell formula and then overwriting it in the next step.