We often use VBA userforms to enter data. These userforms contain various types of controls such as combo boxes, textboxes, command buttons etc. Among them textboxes are a commonly used control type in userforms. So today I am going to explain you a cool trick you can use in VBA textboxes. This will be very useful when you use text boxes to input dates. we can enter dates in various formats such as dd/mm/yyyy, mm/dd/yyyy, mm/dd/yy etc. but there are situations where we need to tell our user to enter date in only particular format. So how we can give this message to our users. We can do it simply like this
When you show the form you can put date format inside the textbox. This can be done by assigning the relevant value to textbox in userform's initialise event. Below is the code you can use for that.
|
Private Sub UserForm_Initialize() txtStart.Value = "DD/MM/YYYY" End Sub |
But it will be a little difficult if user need to delete this value every time he or she want to enter a date. Then your application won't be user friendly. So the best thing is to find a way to clear the textbox when user click on it. We can do this easily using textbox MouseDown event. Here below is the code you can use for that.
|
Private Sub txtStart_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) If StrComp(txtStart.Value, "DD/MM/YYYY", vbTextCompare) = 0 Then txtStart.Value = "" End If End Sub |
I will explain you why I have used if statement here. Sometimes user can unintentionally click on the textbox after entering the correct date. And sometimes user may need to correct a part of the date if he or she has entered it incorrectly. Because of that, we need to check the current value of the text box before clear it. So that's why I have used a if statement before clear the value. Then it will clear the value of the textbox only if it find DD/MM/YYYY.
