Default Value in Data Validation List with Excel VBA (Macro and UserForm)

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

VBA Code to Set Default Value to a Data Validation List in VBA in Excel


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_Value

So, 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

VBA Code to Set Default Value to a Data Validation List in VBA in Excel

⧭ Output:

Run the code. It’ll create a data validation list in cell B4 with the default value, “A Tale of Two Cities”.

Output to Set Default Value in Data Validation List with VBA

Read More: Excel Data Validation Based on Another Cell Value


Similar Readings:


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.

Dragging Tools to Set Default Value in Data Validation List with VBA

⧪ 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

TextBox1 Code to Set Default Value in Data Validation List with VBA

⧪ 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

UserForm Code to Set Default Value in Data Validation List with VBA

⧪ 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”.

Running UserForm to Set Default Value in Data Validation List with VBA

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.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo