• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1662
  • Last Modified:

Formatting Textboxes in User Form to Show Date & Time

I have a spreadsheet which contains a userform.

The purpose of the userform is two-fold.
1) It can be used to input data into the spreadsheet.
2) It can be used to recall rows of data from the spreadsheet.

Some of the textboxes are used for entering a date and time. This info is then transposed onto the worksheet where I have cells formatted to display dd/mm/yyyy and dd/mm/yyyy hh:mm and hh:mm:ss. Hpwever, when I use the form to recall the data, instead of displaying the text in the format shown above, in the textboxes it displays the text in a decimal format! eg instead of showing the time as 13:45:00 it shows 0.5729166666667!

Can the textboxes of the userform be formatted to display certain types of text, i.e. Date, Date & Time, Time, Numbers Only, etc

Also in the first instance of the userform, can the textboxes be formatted to only accept certain types of data? i.e. Numbers 7 characters long or N/A if the user leaves them empty?
0
laffs_efc
Asked:
laffs_efc
  • 2
  • 2
2 Solutions
 
balatheexpertCommented:
1. TO ASSIGN A FORMAT TO THE TEXT BOX:
Private Sub TextBox1_Change()
TextBox1.Text = Format(Val(TextBox1.Value), "dd/mm/yyyy") 'or whichever format u want, u can mention
End Sub

2. TO CONTROL THE VALUES INSERTED TO THE TEXT BOX:
you have to write the code in the textbox's lost focus event to validate, if the conditions are satisfied, again set focus to the text box.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(TextBox1.Text) > 7 Then
MsgBox "Text is too long"
TextBox1.SetFocus
End If
' and the other conditions as u want

End Sub



hope this helps,
Bala
0
 
Arno KosterCommented:
Take special care when implementing textbox1_change macro's in which you modiy textbox1 !!

the supplied code will keep running forever, and may not lead to the results you are looking for.
eg, when you try to delete contents of the textbox, strange effects occur.

a better way would be to include a date and time picker (MSCOMCT2.OCX)
0
 
balatheexpertCommented:
hi akoster, a good point..

i thought this is going to capture the data from cells in which case, it wont be an issue...

pls suggest!!
0
 
Arno KosterCommented:
In that case i would suggest either disabling the textbox, or using a  label instead of a textbox

eg. Label1.Caption = Format( worksheets([...]).range([...]), "dd/mm/yyyy")
0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now