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

Data Validation with VBA

Hi,

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

sSQL = "SELECT * FROM EMPLEADOS;"

Set rsData = New ADODB.Recordset

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

Do While rsData.EOF = False

'code need here

rsData.MoveNext

Loop

rsData.Close
Set rsData = Nothing

Open in new window

0
joyacv2
Asked:
joyacv2
2 Solutions
 
SteveCommented:
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
        .Delete
        .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 & "FROM EMPLEADOS"
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.
Example.xlsm
0
 
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.
0
 
joyacv2Author Commented:
Thanks for your answers!!!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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