use VBA code to create Excel drop down list

[raschwab]

I am endeavouring to create and store a dropdown list in a worksheet cell as opposed to using the traditional excel 2007 methodology. The list currently contains seven items. I have played with a few lines of code and cannot seem to get it to work. Also, I would like to be able to wrap the list of list elements in more than one line of code to reduce code width. One of the pieces of code that I have attempted is;

  With Range("A5").Validation
        .Delete
        .Add Type:=xlValidateList, Operator:=xlBetween, Formula:="ListItem1," & _
                "ListItem2," & _
                "ListItem3," & _
                "ListItem4," & _
                "ListItem5," & _
                "ListItem6," & _
                "ListItem7"
    End With

Any help would be very much appreciated.

Rick

 

[zero269]

This will place Data Validation in cell A5 with a list of items ListItem1 through ListItem7 as you asked.

Sub DVraschwab()
Dim myList$, i%
myList = ""
For i = 1 To 7
myList = myList & "ListItem" & i & ","
Next i
myList = Mid(myList, 1, Len(myList) - 1)
With Range("A5").Validation
.Delete
.Add _
Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:=myList
End With
End Sub

This post really came in handy with creating my long awaited vba-based drop-down list solution...

Sub Planner_List_Period()

  'Declarations
  Dim Grade As Integer, Qtr As Integer
  Dim mpList As String: mpList = "" 'Marking Period List

  'Get Active Grade
  Grade = WorksheetFunction.XLookup(Range("t_Period_Active[Name]"), _
    Range("t_Students[Name]"), Range("t_Students[Grade]"))

  'Build Drop-Down List Criteria
  For Qtr = 1 To 4
    mpList = mpList & Grade & "." & Qtr & ","
  Next Qtr

  mpList = Mid(mpList, 1, Len(mpList) - 1)
  
  'Add Drop-Down List
  With Range("t_Period_Active[Period]").Validation
    .Delete
    .Add _
    Type:=xlValidateList, _
    AlertStyle:=xlValidAlertStop, _
    Formula1:=mpList
  End With
  
  'Insert Active Period
  Range("t_Period_Active[Period]").Value = WorksheetFunction.XLookup(Range("t_Period_Active[Name]"), _
    Range("t_Students[Name]"), Range("t_Students[Period]"))
  
End Sub