MS Excel VBA Code - Data Validation List Error

[OilEconomist]

Thanks in advance for your assistance. Why do I get the following error and how do I fix it:

Error: "Run-tim error '438: Object doesn't support this propert of method"

Code that gives error:

Cells(7, 3).Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=DataValList

I did previously do something similar when it was read from a spreadsheet: VBA Code to Extract Data from a List and Making a Validation , but in this case, I am not reading the values in.

Option Explicit

Sub DataValListError()

 Application.ScreenUpdating = False

  '_______________________________________________________________________________________________________
  'Dim
    
     Dim i As Long, j As Long
     
     Dim StringList() As String, Size As Long
     
     Dim DataValList As Variant
    
    

  '_______________________________________________________________________________________________________
  'Code -
    
    'Set up the array
        'Set the size of the array
         Size = 5
            
        'Redim the size of the array
         ReDim StringList(Size)
        
        'Initialize each element of the array
         StringList(1) = "$APPLES"
         StringList(2) = "$ORANGES"
         StringList(3) = "$PEARS"
         StringList(4) = "$TANGERINES"
         StringList(5) = "$BANANAS"
        
        
        'DataValList
         For i = 1 To Size
            DataValList = DataValList & StringList(i) & ", "
         Next i
        
        'Remove the extra comma and space
         DataValList = Left(DataValList, Len(DataValList) - 2)
        
        'Delete old validation list and insert new one
         Cells(7, 3).Validation.Delete
         Cells(7, 3).Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=DataValList
        
 

  '_______________________________________________________________________________________________________

   Application.ScreenUpdating = True


End Sub

 

[6StringJazzer]

Needs to be

Cells(7, 3).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=DataValList

 

[OilEconomist]

Needs to be

Cells(7, 3).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=DataValList

@6StringJazzer thank you very much for the quick response. That worked!

​