Solved

Data Validation with VBA

Posted on 2013-05-12
3
1,193 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now