Solved

Data Validation with VBA

Posted on 2013-05-12
3
1,234 Views
Last Modified: 2013-05-13
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
Comment
Question by:joyacv2
3 Comments
 
LVL 24

Accepted Solution

by:
Steve earned 400 total points
ID: 39160605
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 100 total points
ID: 39160817
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
 
LVL 1

Author Closing Comment

by:joyacv2
ID: 39161040
Thanks for your answers!!!
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question