Solved

Data Validation with VBA

Posted on 2013-05-12
3
1,145 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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for your answers!!!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

772 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