• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1596
  • Last Modified:

Data Validation with VBA


In the following code, I need to include a part that in the cell sheet1!A21 creates a data validation list with all the values in the "nombre completo" field from the database. And before create the data validation list,  erase all possible data validation items.

Dim rsData As ADODB.Recordset

Dim sSQL As String

'Create the connection string

sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & databasetest & ";" & _
"Jet OLEDB:Database Password=test;"

'Create the SQL Statement


Set rsData = New ADODB.Recordset

rsData.Open sSQL, sConnect, adOpenFowardOnly, _
adLockReadOnly, adCmdText

Do While rsData.EOF = False

'code need here



Set rsData = Nothing

Open in new window

2 Solutions
The code below is to demonstrate how to fill a Validation listbox using VBA:

Sub Macro1()
Dim ArrayOfValues()
Dim ListOfValues As String

For x = 0 To 5
    ReDim Preserve ArrayOfValues(x)
    ArrayOfValues(x) = "Test" & x
Next x

ListOfValues = Join(ArrayOfValues, ",")

    With Selection.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=ListOfValues
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Open in new window

This should get you going in the right direction.

I would suggest that you do not use the '*' (star) to retrive all records for the list as it is better practice to retrive just the field you need.
The use of DISTINCT after the word SELECT may help as may an ORDER BY.
This would be to get the returned recordset into a suitable condition.

sSQL = sSQL & "SELECT DISTINCT 'nombre completo'"
sSQL = sSQL & vbCrLf
sSQL = sSQL & vbCrLf
sSQL = sSQL & "GROUP BY 'nombre completo'"

Open in new window

You could then just use a simple :
ArrayOfValues = rs.GetRows
ListOfValues = Join(ArrayOfValues, ",")
or just:
ListOfValues = Join(rs.GetRows, ",")
This would replace the loop to fill the array.

If you need furtur guidance feel free to say.
but hopefully this will get you on the right track.
Rory ArchibaldCommented:
Also bear in mind that there is a limit of 255 characters on a hardcoded list in a Data Validation formula. If your text may be longer I suggest you populate a range with the data, name it and then use the name as the list source.
joyacv2Author Commented:
Thanks for your answers!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now