?
Solved

Formatting Textboxes in User Form to Show Date & Time

Posted on 2011-04-25
6
Medium Priority
?
1,387 Views
Last Modified: 2012-05-11
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
Comment
Question by:laffs_efc
  • 2
  • 2
5 Comments
 
LVL 2

Accepted Solution

by:
balatheexpert earned 500 total points
ID: 35461687
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
 
LVL 19

Assisted Solution

by:Arno Koster
Arno Koster earned 500 total points
ID: 35465686
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
 
LVL 2

Expert Comment

by:balatheexpert
ID: 35465787
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
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35466352
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
 
LVL 24

Expert Comment

by:Tracy
ID: 35877422
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question