When working with UserForms in Excel VBA, it is often necessary to format the date values displayed in TextBox controls for a better user experience. Not only that, sometimes it becomes necessary to check whether the user inserted a valid date, but you also want to give the user the comfort of choosing his/her own date format. Using Excel VBA, we can format the date in TextBox in different ways. This article will cover all of the functionality you want while working with date formatting.
Introduction to UserForm Textbox
One of the many control elements that can be added to a UserForm in Excel is the Textbox. It is simply a box that lets the user enter texts, names, addresses, or any alphanumeric data. It can also save data inserted on the worksheet by the user. Of course, that kind of functionality requires some simple coding in VBA. We can also customize a textbox’s properties such as font type, size, formatting options, etc.
In order to insert a textbox on UserForm, follow these steps.
- Go to Insert on Visual Basic Editor in Excel.
- From the drop-down menu, choose UserForm.
- When a UserForm appears, an additional module Toolbox will also appear. If the Toolbox doesn’t appear, go to View and select Toolbox.
- In the Toolbox, you will have all the control elements that can be inserted into the UserForm. Select the Textbox(inside a rectangle box) and drag it onto the UserForm.
With Textbox, there are many properties available for you to work with. Textboxes are a great way to get user input and incorporate it into your Excel macros.
Excel VBA Textbox to Format Date: 4 Suitable Examples
In this article, we will demonstrate 4 examples of Excel VBA Textbox to format date. We will try to keep our codes as simple as possible. Not only will you learn how to show different date formats in TextBox input, but you will also be able to validate your input date. For those who are new to Microsoft Visual Basic for Applications, this article on How to Write VBA Code in Excel will guide you through the basics. Now, let’s dive into the article.
Example 1: Format Textbox Input Date as DD/MM/YYYY
Suppose you want to take the textbox input from the user, and however the user provides the date, you want it to be displayed in the Excel Short Date format, which is “DD/MM/YYYY”. Meaning if the user inputs the date as YYYY/MM/DD, the text will automatically format it as “DD/MM/YYYY”. Even if you enter a serial number, the text will auto-format it.
For this demonstration, we will include two textboxes, Textbox1 will take input, and the
Textbox2 will give us an update about the date format in Textbox1. Let’s get to coding.
- First, include two Textboxes in your UserForm.
Note:
We have used other control elements such as Labels and Frames. To understand date formatting, these control elements are not necessary. So when we explain things, we will try to omit them to make the code as simple as possible.
- Now, double-click on Textbox1, and a code window will appear where we will write our code to format the date.
- In the code window, you will see two drop-down menus at the top: one for the Object and one for the Event.
- Textbox1 will automatically be selected on the Object box as you click that and for Event select BeforeUpdate from the drop-down menu and a subroutine will be created.
- Inside the subroutine, write this code to display the user’s input date in your preferred format. So, the whole code will look like below.
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
.Text = Format(.Text, "dd/mm/yyyy")
TextBox2.Text = "Date Format Updated"
Frame1.Enabled = False
End With
End Sub- Create another subroutine by selecting Change from the Event drop-down menu and writing this code.
Private Sub TextBox1_Change()
Frame1.Enabled = True
If TextBox1.Text = "" Then
TextBox2.Text = ""
Frame1.Enabled = False
End If
End Sub
- Now, press F5 or go to the Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.
Check out the video for a proper demonstration of the Textbox formatting date.
Code Breakdown
The code consists of two Event procedures.
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
.Text = Format(.Text, "dd/mm/yyyy")
TextBox2.Text = "Date Format Updated"
Frame1.Enabled = False
End With
End Sub- The first Event procedure, TextBox1_BeforeUpdate is triggered when the user tries to update the value in TextBox1. Whatever way the user inputs the date, using the Format function TextBox1 converts the text to DD/MM/YYYY The formatted date is subsequently assigned back to TextBox1 via the .Text property of the text box. When the assigned format is updated, TextBox2 is set to show “Date Format Updated”.
- Also, TextBox2 is inside Frame1 which is disabled so that the user cannot temper with the status update.
Private Sub TextBox1_Change()
Frame1.Enabled = True
If TextBox1.Text = "" Then
TextBox2.Text = ""
Frame1.Enabled = False
End If
End Sub- The next Event procedure is activated when the user deletes the content in TextBox1. But before that, we again enabled Frame1 so that with the deletion of Textbox1 content, Textbox2 can be updated. When TextBox1 is empty, TextBox2 is also set to become empty using .Text
Example 2: Format Textbox Input as Long Date
Suppose you want your dates to be formatted with detailed information, like Tuesday, May 16 2023. But you don’t want to type this. You just type the date, 16/05/2023. If we can change our code from the previous one a little bit, this formatting is possible. In Excel, this version of date formatting is called Long Date.
Like the previous one, we will also use two textboxes and two Event procedures here.
- First, include two textboxes in your UserForm.
- Now, double-click on Textbox1, and a code window will appear where we will write our code to format the date.
- In the code window, you will see two drop-down menus at the top: one for the Object and one for the Event. Textbox1 will automatically be selected in the Object box as you click that and as for Event select BeforeUpdate from the drop-down menu and a subroutine will be created.
- Inside the subroutine, write this code to display the user’s input date in your preferred format. So, the whole code will look like below.
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
.Text = Format(.Text, "Long date")
TextBox2.Text = "Date Format Updated"
Frame1.Enabled = False
End With
End Sub- Create another subroutine by selecting Change from the Event drop-down menu and then, write this code.
Private Sub TextBox1_Change()
Frame1.Enabled = True
If TextBox1.Text = "" Then
TextBox2.Text = ""
Frame1.Enabled = False
End If
End Sub
- Now, press F5 or go to the Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.
This code is the exact code we used in the previous example. The change occurs .Text = Format(.Text, “dd/mm/yyyy”) line where we updated it like this .Text = Format(.Text, “Long Date”).
Read More: Date Variable in VBA Codes
Example 3: Format Textbox Input as “DD MMM, YYYY”
If you want your date in Excel VBA TextBox to be updated like 17 Dec, 2023, or any other custom date format, we can do that too. Like previous examples, we will also use two textboxes and two Event procedures here too.
- First, include two Textboxes in your UserForm.
- Now, double-click on Textbox1, and a code window will appear where we will write our code to format the date.
- In the code window, you will see two drop-down menus at the top: one for the Object and one for the Event. Textbox1 will automatically be selected in the Object box as you click that and as for Event select BeforeUpdate from the drop-down menu and a subroutine will be created.
- Inside the subroutine, write this code to display the user’s input date in your preferred format. So, the whole code will look like below.
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
.Text = Format(.Text, "dd mmm, yyyy")
TextBox2.Text = "Date Format Updated"
Frame1.Enabled = False
End With
End Sub- Create another subroutine by selecting Change from the Event drop-down menu and then, write this code.
Private Sub TextBox1_Change()
Frame1.Enabled = True
If TextBox1.Text = "" Then
TextBox2.Text = ""
Frame1.Enabled = False
End If
End Sub
- Now, press F5 or go to the Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.
This code is the exact code we used in the previous examples. Instead of using “DD/MM/YYYY” or “Long Date” we used “DD MMM, YYYY”. You can use your formatting as you see fit.
Example 4: Format Textbox Input with Date Validation
In this example, we will see how you can include a date validation feature in your UserForm Textbox. To check whether the user inserted a valid date, we will use the VBA IsDate function. It is a variant data type that checks whether a string is a date or time and returns a boolean value of True or False. Other than the code, our UserForm design will remain the same with two Textboxes.
- First, include two Textboxes in your UserForm.
- Now, double-click on Textbox1, and a code window will appear where we will write our code to format the date.
- In the code window, you will see two drop-down menus at the top: one for the Object and one for the Event. Textbox1 will automatically be selected in the Object box as soon as you click that and as for Event select BeforeUpdate from drop-down menu and a subroutine will be created.
- Inside the subroutine, write this code to display whether the user provided the correct date in your preferred format. So, the whole code will look like below.
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsDate(TextBox1.Text) Then
TextBox2.Text = "Please Enter Correct Date"
Frame1.Enabled = False
Else
TextBox1.Text = Format(TextBox1.Text, "dd mmm, yyyy")
TextBox2.Text = "Date Format Updated"
Frame1.Enabled = False
End If
End Sub- Create another subroutine by selecting Change from the Event drop-down menu and then, write this code.
Private Sub TextBox1_Change()
If TextBox1.Text = "" Then
TextBox2.Text = ""
Frame1.Enabled = True
End If
End Sub
- Now, press F5 or go to the Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.
Code Breakdown
The code consists of two Event procedures.
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsDate(TextBox1.Text) Then
TextBox2.Text = "Please Enter Correct Date"
Frame1.Enabled = False
Else
TextBox1.Text = Format(TextBox1.Text, "dd mmm, yyyy")
TextBox2.Text = "Date Format Updated"
Frame1.Enabled = False
End If
End Sub- The first Event procedure, TextBox1_BeforeUpdate is triggered when the user tries to update the value in TextBox1. When the user inputs a date using the IsDate function, TextBox1 checks the date. If the data is not a valid date it returns “Please Enter Correct Date” in TextBox2 and empty TextBox1.
- Again if the date entered is valid, the Else branch is activated and formats the date in your specified one.
- When the status in TextBox2 is updated to make it uneditable we pushed it inside Frame1 and disabled it by setting it to False.
Private Sub TextBox1_Change()
Frame1.Enabled = True
If TextBox1.Text = "" Then
TextBox2.Text = ""
Frame1.Enabled = False
End If
End Sub- The next Event procedure is activated when the user deletes the content in TextBox1. But before that, we again enabled Frame1 so that with the deletion of Textbox1 content, Textbox2 can be updated. When TextBox1 is empty, TextBox2 is also set to become empty using .Text
Read More: VBA to Remove Time from Date in Excel
How to Enter Today’s Date on Textbox using Excel VBA
If you wish your textbox will pick up the current date automatically and show the date in your desired format you can customize your textbox with VBA Script. Follow us to see how to show the present date in UserForm Textbox using Excel VBA.
- First, include two Textboxes in your UserForm.
- Now, double-click on UserForm, and a code window will appear where we will write our code to format the date.
- In the code window, you will see two drop-down menus at the top: one for the Object and one for the Event.
- UserForm will automatically be selected in the Object box as you click that and as for Event select Initialize from the drop-down menu and a subroutine will be created. This way, as soon as UserForm is activated, the date will appear on TextBox1.
- Inside the subroutine, write this code to display the current date in your preferred format. So, the whole code will look like below.
Private Sub UserForm_Initialize()
TextBox1.Text = Format(Now(), "dd mmm, yyyy")
Frame1.Enabled = False
TextBox2.Text = "Current Date Updated"
Frame2.Enabled = False
End Sub
- Now, press F5 or go to the Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.
Code Breakdown
Private Sub UserForm_Initialize()
TextBox1.Text = Format(Now(), "dd mmm, yyyy")
Frame1.Enabled = False- The code will initialize as soon as UserForm In TextBox1 it is set to show the current date by Now() function and the format has been set to “DD/MM/YYYY”.
- After the date appears on TextBox1, Frame1 is disabled so that the date can’t be changed.
TextBox2.Text = "Current Date Updated"
Frame2.Enabled = False
End Sub- TextBox2 will also update its status and will become unchangeable.
How to Change Date Format on Textbox using Excel VBA
Suppose you want to format your date from a variety of selections in your UserForm. So in this example, we will show you how you can change the date format in your TextBox from different choices using Excel VBA.
- First, include one Textbox and one ComboBox in your UserForm.
- Now, double-click on UserForm, and a code window will appear where we will write our code to include the date formats in ComboBox.
- In the code window, you will see two drop-down menus at the top: one for the Object and one for the Event.
- UserForm will automatically be selected in the Object box as you click that and as for Event select Initialize from the drop-down menu and a subroutine will be created. This way as soon as UserForm activates, the formats will appear on the ComboBox1 and you select one.
- Now write the code inside this subroutine to include every format you want. Check out our code to get an idea.
Private Sub UserForm_Initialize()
ComboBox1.Text = "Choose Format"
ComboBox1.AddItem "DD/MM/YYYY"
ComboBox1.AddItem "MM/DD/YYYY"
ComboBox1.AddItem "YYYY/MM/DD"
ComboBox1.AddItem "DD-MMM-YYYY"
ComboBox1.AddItem "MMMM DD, YYYY"
End Sub- Create another subroutine by selecting ComboBox1 on the Object drop-down menu and Change from the Event drop-down menu and then, write this code.
Private Sub ComboBox1_Change()
Dim selectedFormat As String
selectedFormat = ComboBox1.Value
If IsDate(TextBox1.Value) Then
TextBox1.Value = Format(TextBox1.Value, selectedFormat)
End If
End Sub
- Now, press F5 or go to the Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.
Code Breakdown
The code consists of two Event procedures.
Private Sub UserForm_Initialize()
ComboBox1.Text = "Choose Format"
ComboBox1.AddItem "DD/MM/YYYY"
ComboBox1.AddItem "MM/DD/YYYY"
ComboBox1.AddItem "YYYY/MM/DD"
ComboBox1.AddItem "DD-MMM-YYYY"
ComboBox1.AddItem "MMMM DD, YYYY"
End Sub- This event is triggered when the UserForm is initialized. As soon as the UserFrom activates we want our ComboBox to include the date formats.
- We settThe initial text of ComboBox1 to “Choose Format” using the .Text property of ComboBox1.
- Then, we used .AddItem to add five date format options to ComboBox1. These options are “DD/MM/YYYY“, “MM/DD/YYYY“, “YYYY/MM/DD“, “DD-MMM-YYYY” and “MMMM DD, YYYY“.
Private Sub ComboBox1_Change()
Dim selectedFormat As String
selectedFormat = ComboBox1.Value
If IsDate(TextBox1.Value) Then
TextBox1.Value = Format(TextBox1.Value, selectedFormat)
End If
End Sub- Our next event will trigger when we will change the value of ComboBox1. We declared a string selectedFormat which is the value that we will select in ComboBox1.
- We checked the date in TextBox1 using IsDate function whether or not it is a valid date.
- Then using the Format function, we formatted the date according to the format selected in the ComboBox1.
- Finally, we assigned the formatted date back to TextBox1 using the .Value property of the TextBox.
Frequently Asked Questions
1. How do I clear the date value from a TextBox in VBA?
Ans: To clear the date value from a TextBox in VBA, you can assign an empty string to its Value property: TextBox1.Value = “”.
2. Is it possible to use a calendar control or date picker to select a date for TextBox?
Ans: Excel VBA does not have a built-in calendar control or date picker, but you can create one using additional controls or explore third-party add-ins. However, we have an amazing article on How to Make Textbox Datepicker with Excel VBA. Do check it out.
3. Can I change the date format in TextBox based on the regional settings of the user’s computer?
Ans: Yes, you can do this by using VBA‘s Format function and referencing the system’s regional settings. You can dynamically format the date value in TextBox to match the user’s preferred date format. You can use Application.International(xlDateOrder) as date format inside Format function to apply the regional setting.
Download Practice Workbook
Download this file to practice with us.
Conclusion
When using Excel VBA Textbox to format date, you gain flexibility and control over how you want present dates to users. You can define specific date formats, validate user input, and convert dates to different formats by using VBA’s capabilities. This ensures accurate handling and manipulation of date data in Excel applications, resulting in a smooth and efficient user experience.
Get FREE Advanced Excel Exercises with Solutions!
