Data Validation List in excel VBA

Question

I am trying to add values to the cell in a list format if certain criteria is met. I initially was unable to assign the values directly so i created an Array to old the values that meet the criteria. however now when i try to assign the values of the array to a cell in a list format with the below code i get a

type mismatch error

ThisWorkbook.Sheets("Plan").Cells(i, 5).Validation.Add _
      Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
      Formula1:=Join(St, ",")

Can anyone suggest how to achieve this or if there is a way to assign the values directly without holding them in an array. remember the list is not continuous an it only needs to select the values that meet my condition.

Sub try() 
Dim j,k As Long 
Dim c As Range 
Dim st As Object 
Set st=CreateObject("System.Collections.ArrayList") 
For j=5 To 150 
If(Workbook.Sheets("Plan").Cells(i,1)= Workbook.Sheets("Master").Cells(j,1))Then 
    For k= 6 To 150 
        If(Workbook.Sheets("Master").Cells(j,k)<>"") Then
            For Each c In Workbook.Sheets("Master").Cells(1,k)
                Select Case c.Value 
                    Case"Starch" st.Add Workbook.Sheets("Master").Cells(3,k)
                EndSelect 
            Nextc 
        EndIf 
    Next k 
EndIf 
Next j 

Workbook.Sheets("Plan").Cells(i,5).Validation.Add Type:=xlValidateList, _
              AlertStyle:=xlValidAlertStop, Formula1:=Join(st,",") 

End Sub

You can edit your question to add your code - it's difficult to read in a comment — Tim Williams, Feb 24 '20 at 07:05
Maybe try `Join(st.ToArray,",")` You need to get your ArrayList into e "regular" array before you can pass it to `Join` — Tim Williams, Feb 24 '20 at 07:13
Hi Tim, i tried Join(st.ToArray,",") however i get a application defined or object defined error. — Rish, Feb 24 '20 at 07:29

score 0 · Accepted Answer · answered Feb 24 '20 at 07:43

0

You need to get your ArrayList into a regular array before you can pass it to Join.

Eg:

Sub Tester()

    Dim st As Object

    Set st = CreateObject("System.Collections.ArrayList")
    st.Add "A"
    st.Add "B"
    st.Add "C"

    'Debug.Print Join(st, ",")         'fails with "Type Mismatch"
    Debug.Print Join(st.toarray, ",") 'Works >> A,B,C

    ActiveSheet.Cells(1, 1).Validation.Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, Formula1:=Join(st.toarray, ",")

End Sub

answered Feb 24 '20 at 07:43

Tim Williams

154,628
8
97
125

Tim i had another query related to this. once i have all my drop downs generated. now if i select a value in one drop down i need to also make sure the same value is not selected in any other drop down through my sheet. is that possible? – Rish Feb 25 '20 at 04:19
Yes that's possible - you can look at using Conditional formatting for example. – Tim Williams Feb 25 '20 at 05:36
i have checked conditional formatting however there is an option for highlighting duplicates. Is there any other way apart from that one? – Rish Feb 25 '20 at 06:11
it would be best to post a new question for this, and explain exactly what the behaviour should be. You want to *prevent* chosing the same value, just warn the user, or ? How many cells need to be monitored? Etc. – Tim Williams Feb 25 '20 at 06:28
hi tim i have posted a new question for this however i havent got a useful answer yet and seeing that you helped on the previous query i was hoping you could on this one as well. – Rish Feb 25 '20 at 10:15

Data Validation List in excel VBA

1 Answers1