Excel Validation Drop Down list using VBA

Question

I have an array of values  I want to show those values in Excel Cell as drop down list using VBA   Here is my code  It shows  Type Mismatch Error    Dim xlValidateList 6  As Integer xlValidateList 1    1 xlValidateList 2    2 xlValidateList 3    3 xlValidateList 4    4 xlValidateList 5    5 xlValidateList 6    6  With Range  A1   Validation  Add Type  xlValidateList  AlertStyle  xlValidAlertStop  Operator     xlBetween  Formula1  ValidationList  IgnoreBlank   True  InCellDropdown   True  InputTitle       ErrorTitle       InputMessage       ErrorMessage       ShowInput   True  ShowError   True End With   The problem occurs in following line      Add Type  xlValidateList  AlertStyle  xlValidAlertStop  Operator       Please let me where the problem is    Thanks in advance

User · Accepted Answer

You are defining your array as xlValidateList    so when you try to assign the type  it gets confused as to what you are trying to assign to the type   Instead  try this   Dim MyList 5  As String MyList 0    1 MyList 1    2 MyList 2    3 MyList 3    4 MyList 4    5 MyList 5    6  With Range  A1   Validation      Delete      Add Type  xlValidateList  AlertStyle  xlValidAlertStop             Operator  xlBetween  Formula1  Join MyList       End With

User · Answer

The accepted answer is correct but needs to be wary that this way imposes a 255 character limit  Better to reference an actual worksheet range object

User · Answer

based on examples above and examples found on other sites  I created a generic procedure and some examples    Simple helper procedure to create a dropdown in a cell based on a list of values in a range  ValueSheetName   the name of the sheet containing the value range  ValueRangeString   the range on the sheet with name ValueSheetName containing the values for the dropdown  CreateOnSheetName   the name of the sheet where the dropdown needs to be created  CreateInRangeString   the range where the dropdown needs to be created  FieldName As String   a name of the dropdown  will be used in the inputMessage and ErrorMessage  See example below ExampleCreateDropDown Public Sub CreateDropDown ValueSheetName As String  ValueRangeString As String  CreateOnSheetName As String  CreateInRangeString As String  FieldName As String      Dim ValueSheet As Worksheet     Set ValueSheet   Worksheets ValueSheetName   The sheet containing the values     Dim ValueRange As Range  Set ValueRange   ValueSheet Range ValueRangeString   The range containing the values     Dim CreateOnSheet As Worksheet     Set CreateOnSheet   Worksheets CreateOnSheetName   The sheet containing the values     Dim CreateInRange As Range  Set CreateInRange   CreateOnSheet Range CreateInRangeString      Dim InputTitle As String   InputTitle    Please Select a Value      Dim InputMessage As String   InputMessage    for    amp  FieldName     Dim ErrorTitle As String   ErrorTitle    Please Select a Value      Dim ErrorMessage As String   ErrorMessage    for    amp  FieldName     Dim ShowInput As Boolean   ShowInput   True  Show input message on hover     Dim ShowError As Boolean   ShowError   True  Show error message on error     Dim ValidationType As XlDVType   ValidationType   xlValidateList     Dim ValidationAlertStyle As XlDVAlertStyle   ValidationAlertStyle   xlValidAlertStop  Stop on invalid value     Dim ValidationOperator As XlFormatConditionOperator   ValidationOperator   xlEqual  Value must be equal to one of the Values from the ValidationFormula1     Dim ValidationFormula1 As Variant   ValidationFormula1        amp  ValueSheetName  amp       amp  ValueRange Address  Formula referencing the values from the ValueRange     Dim ValidationFormula2 As Variant   ValidationFormula2           Call CreateDropDownWithValidationInCell CreateInRange  InputTitle  InputMessage  ErrorTitle  ErrorMessage  ShowInput  ShowError  ValidationType  ValidationAlertStyle  ValidationOperator  ValidationFormula1  ValidationFormula2  End Sub    An example using the ExampleCreateDropDown Private Sub ExampleCreateDropDown       Call CreateDropDown ValueSheetName   Test   ValueRangeString   C1 C5   CreateOnSheetName   Test   CreateInRangeString   B1   FieldName   test2   End Sub    The full option function if you need more configurable options  To create a dropdown in a cell based on a list of values in a range  Validation  https   msdn microsoft com en-us library office ff840078 aspx  ValidationTypes  XlDVType  https   msdn microsoft com en-us library office ff840715 aspx  ValidationAlertStyle   XlDVAlertStyle  https   msdn microsoft com en-us library office ff841223 aspx  XlFormatConditionOperator  https   msdn microsoft com en-us library office ff840923 aspx  See example below ExampleCreateDropDownWithValidationInCell Public Sub CreateDropDownWithValidationInCell CreateInRange As Range                                            Optional InputTitle As String                                                 Optional InputMessage As String                                                 Optional ErrorTitle As String                                                 Optional ErrorMessage As String                                                 Optional ShowInput As Boolean   True                                            Optional ShowError As Boolean   True                                            Optional ValidationType As XlDVType   xlValidateList                                            Optional ValidationAlertStyle As XlDVAlertStyle   xlValidAlertStop                                            Optional ValidationOperator As XlFormatConditionOperator   xlEqual                                            Optional ValidationFormula1 As Variant                                                 Optional ValidationFormula2 As Variant            With CreateInRange Validation          Delete          Add Type  ValidationType  AlertStyle  ValidationAlertStyle  Operator  ValidationOperator  Formula1  ValidationFormula1  Formula2  ValidationFormula2          IgnoreBlank   True          InCellDropdown   True          InputTitle   InputTitle          ErrorTitle   ErrorTitle          InputMessage   InputMessage          ErrorMessage   ErrorMessage          ShowInput   ShowInput          ShowError   ShowError     End With End Sub    An example using the CreateDropDownWithValidationInCell Private Sub ExampleCreateDropDownWithValidationInCell       Dim ValueSheetName As String  ValueSheetName    Hidden   The sheet containing the values     Dim ValueRangeString As String  ValueRangeString    C7 C9   The range containing the values     Dim CreateOnSheetName As String  CreateOnSheetName    Test    The sheet containing the dropdown     Dim CreateInRangeString As String  CreateInRangeString    A1   The range containing the dropdown      Dim ValueSheet As Worksheet     Set ValueSheet   Worksheets ValueSheetName      Dim ValueRange As Range  Set ValueRange   ValueSheet Range ValueRangeString      Dim CreateOnSheet As Worksheet     Set CreateOnSheet   Worksheets CreateOnSheetName      Dim CreateInRange As Range  Set CreateInRange   CreateOnSheet Range CreateInRangeString      Dim FieldName As String  FieldName    Testing Dropdown      Dim InputTitle As String   InputTitle    Please Select a value      Dim InputMessage As String   InputMessage    for    amp  FieldName     Dim ErrorTitle As String   ErrorTitle    Please Select a value      Dim ErrorMessage As String   ErrorMessage    for    amp  FieldName     Dim ShowInput As Boolean   ShowInput   True     Dim ShowError As Boolean   ShowError   True     Dim ValidationType As XlDVType   ValidationType   xlValidateList     Dim ValidationAlertStyle As XlDVAlertStyle   ValidationAlertStyle   xlValidAlertStop     Dim ValidationOperator As XlFormatConditionOperator   ValidationOperator   xlEqual     Dim ValidationFormula1 As Variant   ValidationFormula1        amp  ValueSheetName  amp       amp  ValueRange Address     Dim ValidationFormula2 As Variant   ValidationFormula2           Call CreateDropDownWithValidationInCell CreateInRange  InputTitle  InputMessage  ErrorTitle  ErrorMessage  ShowInput  ShowError  ValidationType  ValidationAlertStyle  ValidationOperator  ValidationFormula1  ValidationFormula2   End Sub

User · Answer

Private Sub main     replace  J2  with the cell you want to insert the drop down list With Range  J2   Validation      Delete      replace   A1 A6  with the range the data is in       Add Type  xlValidateList  AlertStyle  xlValidAlertStop        Operator  xlBetween  Formula1    Sheet1 A1 A6       IgnoreBlank   True      InCellDropdown   True      InputTitle           ErrorTitle           InputMessage           ErrorMessage           ShowInput   True      ShowError   True End With End Sub

User · Answer

This worked on my test file  note the index in VBA starts from zero    Sub DV Test       Dim ValidationList 5  As Variant  i As Integer      For i   0 To UBound ValidationList          ValidationList i    i   1     Next      With Range  A1   Validation          Delete          Add Type  xlValidateList  AlertStyle  xlValidAlertStop                Operator  xlEqual  Formula1  Join ValidationList                IgnoreBlank   True          InCellDropdown   True          InputTitle               ErrorTitle               InputMessage               ErrorMessage               ShowInput   True          ShowError   True     End With End Sub   I used xlEqual because that s what I think you are trying to get people to select one of the list

[vba] Excel Validation Drop Down list using VBA

Examples related to vba

Examples related to excel