View Full Version : Populating an array with a userform
sassora
07-01-2012, 01:55 PM
Is there a standard way to produce to populate a combo box with a list based on an array from a user form?
For example, the list includes a, b and c and I would like the user to be able to include item d in the array via the user form and also be able to delete selections as well.
Kenneth Hobs
07-02-2012, 07:08 AM
I am confused about your problem description. What do you mean by array on userform?
The combobox and listbox controls have a List property. You can get or put data from/to an array using it.
If you post an example workbook, it is easier to help.
sassora
07-03-2012, 12:21 AM
Thanks, I have attached an example of what I would like to achieve.
sassora
07-03-2012, 03:39 AM
I think that something like this would help with the unique values:
Sub UniqueValues()
Dim tmp As String
Dim arr() As String
Dim cell As Range
Dim arraycount As Integer
If Not Selection Is Nothing Then
tmp = "|"
For Each cell In Selection
If (cell <> "") And (InStr(tmp, "|" & cell & "|") = 0) Then
tmp = tmp & cell & "|"
End If
Next cell
End If
If Len(tmp) > 0 Then
tmp = Right(Left(tmp, Len(tmp) - 1), Len(tmp) - 2)
arr = Split(tmp, "|")
End If
For arraycount = 0 To UBound(arr)
'Sheets("Categories").Range("J8").Cells(arraycount + 1, 1).Value = arr(arraycount)
Debug.Print arraycount & ". " & arr(arraycount)
Next arraycount
End Sub
Hi sassora,
I am probably (actually, hopefully), only going to be up for another 30 minutes or so, but as you just posted... If you spot this right off, could you post in .xls format?
Mark
sassora
07-03-2012, 03:51 AM
Sure - I've posted as xls version
Apologies, as I must be off to bed, but I am not understanding (probably just me being thick-headed) as to:
"the second containing, sorted component parts"
a1
a1:b1
a1:b1:c1
a1:b1:c2
a1:b1:c3
a1:b2
a1:b2:c4
a1:b2:c5
and:
"I would like they (sic) contents of these boxes to be available in array form, so I can work with the contents later"
I do not understand as to the sort firstly. Are the (Forms toolbar type) listboxes multi-column and a1:b1 represent two columns in one row? Or, is a1:b1 one string that we need to pick apart to sort (at least as I read your input)?
Thus, it seems unclear to me as to what we are trying to do in storing any value(s) to an array?
Mark
sassora
07-03-2012, 04:34 AM
I am defining documents according to work area using data validation. I would like to create a user form that can be used to update the validation lists. The idea is to pass the userform data into an array which can then feed the validation lists.
The a, b and c represent categories and they are separated by a colon.
For example
Subjects:English:Literature
Subjects:Maths:Algebra
Subjects:Maths:Logic
Then I would like to return a list
Subjects
Subjects:English
Subjects:English:Literature
Subjects:Maths
Subjects:Maths:Algebra
Subjects:Maths:Logic
(basically all of the combinations are shown)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.