Rifat Hassan
While working with data validation list (s) in Excel or VBA, we often need to set a default value. In this article, I’ll show you how you can set a default value in a data validation list with VBA in Excel.
How to Set Default Value in Data Validation List with VBA (Quick View)
Sub Data_Validation_List_with_Default_Value()
List_Location = "B4"
List_Elements = "Great Expectations,Oliver Twist,Hard Times,A Tale of Two Cities,Martin Chuzzulet,Pickwick Papers,The Old Curiousity Shop,David Copperfield,Nicholas Nicolby"
Default_Value = "A Tale of Two Cities"
Range(List_Location).Delete
Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=List_Elements
Range(List_Location) = Default_Value
End Sub
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
How to Set Default Value in Data Validation List with VBA (Macro and UserForm)
Without further delay, let’s move to our main purpose. Let’s try to insert a data validation list in cell B4 of the worksheet.
The list will contain the names of some of the finest novels of Charles Dickens. They are: Great Expectations, Oliver Twist, Hard Times, A Tale of Two Cities, Martin Chuzzulet, Pickwick Papers, The Old Curiosity Shop, David Copperfield, and Nicholas Nicolby.
The default is the most famous one among these, A Tale of Two Cities.
We’ll accomplish this in 2 ways today. First, we’ll develop a Macro to create a data validation list with a default value, then we’ll create a UserForm.
1. Developing a Macro to Set Default Value in Data Validation List with VBA
First, we’ll develop a Macro to set a default value to a data validation list. For the sake of understanding, I am showing you the step-by-step procedure to execute this.
⧪ Step 1: Inserting the Inputs
The step of developing the Macro is to insert the necessary inputs to the code.
The first input is the location of the list, which is B4.
Then we’ll insert the elements of the list, which are the names of the books mentioned above (Separate them by commas, don’t put after the comma).
List_Location = "B4"
List_Elements = "Great Expectations,Oliver Twist,Hard Times,A Tale of Two Cities,Martin Chuzzulet,Pickwick Papers,The Old Curiousity Shop,David Copperfield,Nicholas Nicolby"
Default_Value = "A Tale of Two Cities"⧪ Step 2: Creating the Validation List
Next, we’ll create the data validation list with the inserted inputs.
Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=List_Elements⧪ Step 3: Setting the Default Value
Finally, we’ll set up the default value.
Range(List_Location) = Default_ValueSo, the complete VBA code will be:
⧭ VBA Code:
Sub Data_Validation_List_with_Default_Value()
List_Location = "B4"
List_Elements = "Great Expectations,Oliver Twist,Hard Times,A Tale of Two Cities,Martin Chuzzulet,Pickwick Papers,The Old Curiousity Shop,David Copperfield,Nicholas Nicolby"
Default_Value = "A Tale of Two Cities"
Range(List_Location).Delete
Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=List_Elements
Range(List_Location) = Default_Value
End Sub
⧭ Output:
Run the code. It’ll create a data validation list in cell B4 with the default value, “A Tale of Two Cities”.
Read More: Excel Data Validation Based on Another Cell Value
Similar Readings:
- Autocomplete Data Validation Drop Down List in Excel (2 Methods)
- Excel Data Validation Drop Down List with Filter (2 Examples)
- How to Make a Data Validation List from Table in Excel (3 Methods)
- Use Custom VLOOKUP Formula in Excel Data Validation
- Excel Data Validation Alphanumeric Only (Using Custom Formula)
2. Building a UserForm to Set Default Value in Data Validation List with VBA
We’ve learned to develop a Macro to create a data validation list with a default value. Now we’ll learn to develop a UserForm.
⧪ Step 1: Inserting the UserForm
Open the Visual Basic window and go to the Insert > UserForm option in the toolbar to insert a new UserForm.
⧪ Step 2: Dragging Tools to the UserForm
A UserForm called UserForm1 will be opened. Drag 3 Labels, 3 TextBoxes, and 1 CommandButton as shown in the figure from the Toolbox available besides.
⧪ Step 3: Writing Code for TextBox1
Double-click on TextBox1. A private subprocedure called TextBox1_Change will open. Insert the following code there.
Private Sub TextBox1_Change()
On Error GoTo TB1
Range(UserForm1.TextBox1.Text).Select
Exit Sub
TB1:
x = 21
End Sub
⧪ Step 4: Writing Code for CommandButton1
Double-click on CommandButton1. A private subprocedure called CommandButton_click will open. Insert the following code there:
Private Sub CommandButton1_Click()
On Error GoTo CB1
List_Location = UserForm1.TextBox1.Text
List_Elements = UserForm1.TextBox2.Text
Default_Value = UserForm1.TextBox3.Text
Range(List_Location).Delete
Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=List_Elements
Range(List_Location) = Default_Value
Unload UserForm1
Exit Sub
CB1:
MsgBox "Enter a Valid Cell Reference.", vbExclamation
End Sub
⧪ Step 5: Writing Code for Running the UserForm
Finally, insert a new Macro from the Visual Basic toolbar and enter the following code there.
Sub Run_UserForm()
UserForm1.Caption = "Data Validation with Default Value"
UserForm1.Label1.Caption = "List Location:"
UserForm1.Label2.Caption = "List Elements:"
UserForm1.Label3.Caption = "Default Value:"
UserForm1.CommandButton1.Caption = "OK"
Load UserForm1
UserForm1.Show
End Sub
⧪ Step 6: Running the UserForm
Your UserForm is now ready to use. Run the Macro called Run_UserForm. The UserForm will load with the title “Data Validation with Default Value”.
Insert the List Location, List Elements, and the Default Value (Separe the list elements by commas. No space after the comma).
Then click OK. It’ll insert the desired validation list with the default value in the specified cell.
Read More: Data Validation Drop Down List with VBA in Excel (7 Applications)
Things to Remember
We’ve kept the line Range(List_Location).Delete in our code because in case there is any other validation list in the same location, we need to delete it before inserting a new list. But if you are sure there is no prior list, you can remove this line from the code.
Conclusion
So, these are the ways to set a default value in a data validation list with VBA. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.
