PDA

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

GTO
07-03-2012, 03:45 AM
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

GTO
07-03-2012, 04:29 AM
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)