Dynamic Validation List dropdown in Excel Sheet VBA, C# VSTO

Dynamic Validation List dropdown in Excel Sheet VBA, C# VSTO

Validation List

In Excel he most common dropdown list is Validation List. Let’s see how to create a validation list first then we will do some coding to handle the same which will be little tricky. Follow the steps as given below:

Create Source

  • Create list of countries (as shown in the image below) you would like to fill into your list
  • Keep the cursor/pointer to the desired location where you would like to have your dropdown validation list. This is most important on of the steps
  • Navigate Data Tab>> Select Data Validation command
  • A Popup comes having 3 tabs, Settings, Input Message, Error Alert
  • On Settings tab select List from Allow dropdown under Validation Criteria
  • Keep Ignore Blank checkbox default if you want to ignore blank values from selected range
  • keep In-cell dropdown checkbox on by default
  • Navigate to Source range selector and Select the range which has values that you would like to populate in dropdown list
  • Say OK

Let’s understand example. Column “F” contains list of countries that I would like to see in my validation list hence under Settings tab the Source selected as “F2:F5”. If you notice Column “H3” that is my destination where my actual validation list would be populated upon hitting OK button on Data Validation as shown below:

That’s it, my validation list is ready with countries. Let’s do some real magic by putting VBA code. So, what I am trying to achieve here, If I select USA the next output cell should say, “Hay you have selected USA” and so on.

Open VBA Editor by pressing ALT + F11 or navigate to Developer Tab and click on Visual Basic Editor command to open VBA IDE.

Trick: what is the trick here? since we have no control over the Validation List hence, we will have to trap the Cell on which actual selection will take place and values will be replaced one to another. How? the answer is “Worksheet_Change” event which offer a very useful range type argument named as “Target“. This “Target” variable is the key that will help us trap the selection.

Code Example

Private Sub Worksheet_Change(ByVal Target As Range)
    If Replace(Target.Address, "$", "") = "H3" Then
        ThisWorkbook.Sheets("Sheet3").Range("J3") = "You have selected " & Target.Value
    End If
End Sub

Output

C# Code to Add Data Validation List

public static void AddValidationListToSheet(Excel.Worksheet xlSheet, string listString, int rowIndex, int columnIndex, string defaultValue = "")
{
    Excel.Range cell = (Excel.Range)xlSheet.Cells[rowIndex, columnIndex];
    cell.Validation.Delete();
    cell.Validation.Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertInformation, Excel.XlFormatConditionOperator.xlBetween, listString, Type.Missing);
    cell.Validation.IgnoreBlank = true;
    cell.Validation.InCellDropdown = true;
    cell.Value = defaultValue;
}

Note: listString must be a comma separated string, Excel will automatically trim it down and populate the list

Video Example

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Leave a Reply

Your email address will not be published.