TextBox Format

[elgringo56]

If I format a cell as mm/dd/yyyy and then I type into the cell only the month / day, the year is automatically put in. I have a UserForm with a TextBox that I would like to act the same way so that when the user types in a date as 7/25, what the cell gets is 07/25/2003. I have spent several hours trying to figgure this out. Any ideas??

[Juan Pablo González]

Do you have the TextBox and the cell linked ?

[elgringo56]

I see no way in the properties for a TextBox to link it to a cell. Am I missing something here? I do have code that that puts the contants of the cell in the TextBox when the UserForm is open and puts the TextBox contents in the cell when a command button is clicked.

I just did some checking, and a TextBox on a sheet can be linked to a cell, but on a UserForm, that provision is not there.

[Juan Pablo González]

Well, actually there is a way, but good for you to not link cells ! (You use the ControlSource property for that).

However, this seemed to work:


Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
****If TextBox2.Value Like "#/##" Or TextBox2.Value Like "##/##" Then
********TextBox2.Value = Format$(TextBox2.Value & "/" & Year(Date), "dd/mm/yy")
****End If
End Sub


Note that I used dd/mm/yy because that's the format I use on my computer... change that to mm/dd/yy to suit your needs.

[elgringo56]

Thank you very much, Juan. That does the trick. Can you tell me why it is not good to link cells?