VBA validation list based on data from SQL

Question

Background:
I am trying to make a template in excel to import data in a database storage system held in Access. I am trying to create some validation lists for the data using already present variables in the database selected via SQL. Each column to be imported needs a header with 5 items, each of which needs to be validated separately.

I have SQL queries that populate sheets with the contents of each of the validation lists. These are in the format of a single column of strings. (Ask for code if you want it, I will leave it out for now as this isn't the problem).

I am using Excel 2007 .xlsm

Problem:
So, my problem is trying to get an xlvalidatelistin the relevant cell, that validates based on the list of data taken out of access, held within a separate sheet. Originally I was trying to create a comma delimited string to use for the validation, but I came up against a problem of the 255 character limit in a string. I am now trying to reference the list of strings in the other sheet directly. I would also like the validation list to accept the contents of the cell being validated; to allow the user to add a new value not in the database (I have code to handle this case when the file is imported into the database).

Code:

Public Function CreateValidationLists(ByVal SheetName As String, Row As Integer)

Dim RowCounter As Integer
Dim Colcounter As Integer

RowCounter = Row


For Colcounter = 2 To 256
   With ActiveWorkbook.Worksheets("Import Data").Cells(RowCounter, Colcounter)
        With .Validation
             .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Formula1:="Sites!" & Range(Worksheets("Sites").Cells(1, 1), Worksheets("Sites").Cells(1, 1).End(xlDown)).Address
        End With
    End With
Next

End Function

Error
The Error I get is on the line .Add Type:=xlValidateL...
The Error message is
Run-time error '1004':
Application-defined or object-defined error

Any help greatly appreciated. If anyone has any ideas of a better way to go about this I am open to suggestions.

Answer 1

The only thing you missed is equal sign in you formula like this:

'....your code here
Formula1:="=Sites!" & Range(Worksheets("Sites").Cells(1, 1), Worksheets("Sites").Cells(1, 1).End(xlDown)).Address
'.... your code here

You idea is quite correct and I like it. You could pass values directly from array. Here is sample code which you could use:

Sub ValueFromArray()
'testing code!
    Dim myArray
        myArray = Array("A", "B", "C", "D")

    With ActiveWorkbook.Worksheets("Import Data").Cells(1, 2)
        With .Validation
             .Add Type:=xlValidateList, _
             AlertStyle:=xlValidAlertInformation, _
             Formula1:=Join(myArray, ", ")
        End With
    End With
End Sub

Look into Join function which is required in the code above.