Data Validation Macro Explained

posted in: Excel Tutorials | 0

Excel Macro – VBA drop down list

Microsoft Excel VBA data validation list macro – Data Validation VBA

For a guide to how to edit macros for your own purposes, please click here.

In the video below we outline and explain how we can create a Macro in Microsoft Excel which will add data validation properties to a new cell, this is also referred to as VBA Data validation. This is very useful if you would have created new cells using a macro and would like to add preexisting data validation properties to it. This Excel VBA dropdown macro uses VBA code which can be found below. We will term this code “VBA drop down list”.

If you have any questions please post these onto the YouTube channel.

 

Excel VBA dropdown Video (Data Validation VBA)

 

 

 

The code that is used in this example is as follows. Please remember that you will need to edit the sheet and cell values to fit your specific needs. For more direction on how to do this please refer to some of the previous videos and also the guide that has been posted above. Please remember that the code below does a number of things including inserting a new row. For this reason we have outlined the specific “VBA Data Validation List” code below also for clarity.

 

VBA Drop Down List

Private Sub CommandButton1_Click()

Sheets(“Sheet1”).Range(“A4”).Select
ActiveCell.EntireRow.Insert shift:=xlDown

Sheets(“Sheet1”).Range(“A4:E4”).Select
Selection.Borders.Weight = xlThin

Sheets(“Sheet1”).Range(“e5”).Copy
Sheets(“Sheet1”).Range(“e4”).PasteSpecial xlPasteValidation
Application.CutCopyMode = False

End Sub

The code in the example above which is only concerned with data validation in VBA (ie VBA dropdown) is as follows.

Sheets(“Sheet1”).Range(“e5”).Copy
Sheets(“Sheet1”).Range(“e4”).PasteSpecial xlPasteValidation
Application.CutCopyMode = False

This code copies the values you wish to use in you Excel VBA drop down list and then pastes them into your desired cell(s). For more clarity on this type of Excel VBA dropdown please feel free to watch the video above.

To download a copy of this workbook then please click the link below. Any questions please place on the Youtube channel.

INR + Paste Validation.

For more Excel Tutorials please visit my channel.

If this has been helpful then please like or subscribe to the channel.

Leave a Reply