Help with problem validation problem. 256 limit posted earlier.

[I_am_fitz]

I want to load a validation drop down box from array values. If I
define this as a string I am limited to 256 characters. If I
reference the first element in the array the drop down box contains
that one name but i want to list them all. I have provided the code
snip with some comments. Thanks.


Dim Stmt As String
Dim Rs As New ADODB.Recordset
Dim RTNDRDLCF As String
Dim RTNUSER(1 To 100) As String
Dim I As Long

Cmd1.ActiveConnection = CON1
Cmd1.CommandType = adCmdText
Stmt = ""
Stmt = Stmt & "SELECT DRDLCF"
Stmt = Stmt & " FROM DATALIB.LTFXX"
Stmt = Stmt & " WHERE CRFD = '09'"
Stmt = Stmt & " AND CRFDT = 'BU'"
Cmd1.CommandText = Stmt

Rs.Open Cmd1
While Not Rs.EOF
RTNDRDL01 = Trim(Rs.Fields("DRDL01").Value)
If RTNDRDL01 <> "" Then
I = I + 1
RTNUSER(I) = Trim(RTNDRDL01)
End If
Rs.MoveNext
Wend

'At this point my RTNUSER() array has 50 names.

Range("C6").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=RTNUSER 'this does not work, what am I
doing wrong???
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "User Name"
.InputMessage = ""
.ErrorMessage = "Select a Valid Value from List."
.ShowInput = True
.ShowError = True
End With

 

[I_am_fitz]

Can I add anything else to this post?

 

[Dave Peterson]

Can you dump those names to a sheet (maybe hidden), then give that range a nice
workbook level name and use that in your data|validation rules?

 

[I_am_fitz]

Can you dump those names to a sheet (maybe hidden), then give that range a nice
workbook level name and use that in your data|validation rules?












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Well I could, but am I actually doing something wrong here...If the
valadation object can use a named list isn't an array very similar?

I have tried a couple of things here:

defining the RTNUSER as a string (this works but limits my values to
256 - can get 15.5 names to show up )

so then I populated multiple strings and concatenated them for
formula1= string1
+ string2 + string3 (this didnt work either still 256)

so then I tried the array variation ( what you see here) and still not
what I need.

I was actually trying to move away from loading it into another
sheet. Original design was just to maintain a list of names on
another spread sheet. I didnt like that becasue I know that these
names are already in a database that is maintained. (trying to make
it better)

Thanks for your response.

 

[Dave Peterson]

Check out VBA's help for Add and validation. You'll see:

xlValidateList

Formula1 is required, Formula2 is ignored. Formula1 must contain either a
comma-delimited list of values or a worksheet reference to this list.

And if your comma-delimited list is too long, it won't work for you--as you've
seen.

And I would say that a named range and an array aren't really close--well, not
close enough for your purposes.