Displaying Dates in a Text Box

Posted by Todd on October 04, 2001 1:06 PM

My text box is linked to a cell. Whenever I change the date in the cell (and it is formated mm/dd/yyyy) the text box displays the date in number format (e.g. 33149 for 10/03/1990). I need to have the text box display the date in date format (mm/dd/yyyy).

I tried doing this in the module I have that changes the linked cell. But I couldn't figure out how to reference the text box from the module (it says the variable is undefined), and I couldn't figure out how to call a macro assigned to the worksheet the text box is on from the module.

I'd greatly appreciate any help.
Thanks! Todd

Posted by Juan Pablo on October 04, 2001 1:16 PM

If you can Unlink the textbox i think it'll work better. Put this in your UserForm (Is it in a Userform, by the way) code module

Private Sub UserForm_Initialize()
TextBox1 = WorksheetFunction.Text([A1], "mm/dd/yyyy")
End Sub

Where A1 is the cell you are linking to the textbox.

Juan Pablo

Posted by Todd on October 04, 2001 1:21 PM

No, its not in a userform, it is attached to the spreadsheet. Can I use the initialize event directly on the text box? e.g. Private Sub TextBox1_Initialize() ? and will that keep the format if I change cell A1?
Thanks,
Todd

Posted by Juan Pablo on October 04, 2001 1:31 PM

If its a ActiveX TextBox in the worksheet and your date is in A1 try this (There's no Initialize event for TextBoxes)

In your worksheet module (The specific worksheet that has the textbox) put this code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, [A1]) Is Nothing Then
TextBox1 = WorksheetFunction.Text([A1], "mm/dd/yyyy")
End If
End Sub

Juan Pablo

Posted by Barrie Davidson on October 04, 2001 1:31 PM

Todd, can you reference another cell instead? What I am thinking is using Excel's TEXT function to convert the date to a text value. For example, if your text box is linked to A1, put the following formula in B1 and then link your textbox to B1.

=TEXT(A1,"mmm-d-yyyy")

Regards,
BarrieBarrie Davidson

Posted by Todd on October 05, 2001 9:50 AM

Thanks much for the help. When you wrote that, I realized I could just format the date display using the textbox_change() property...
Todd