populate a combobox based on unique values in another workbook

[Roninn75]

hi good day

i have two workbooks, the one is used as a register the second has
form with some comboboxes. i want :
1. the combobox on the form to be go look in the 1st workbook at
specific column,
2. take all the unique values in that column, and
3. populate the combobox with those values.

it can either be an open workbook or closed workbook, i kno
application.events = false or true will open that workbook with out th
user knowing it opened then close it again.

any assistance would be appreciated...

 

[isabelle]

hi Roninn75,

Private Sub UserForm_Initialize()
Dim i As Integer
Dim wkbkSource As Workbook

Application.ScreenUpdating = False
Set wkbkSource = Workbooks.Open(Filename:="C:\List_test.xlsm")

With wkbkSource.Sheets("Sheet1")
For i = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
ComboBox1 = .Cells(i, 1)
If ComboBox1.ListIndex = -1 Then ComboBox1.AddItem .Cells(i, 1)
Next i
End With

wkbkSource.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

isabelle

Le 2013-08-02 07:45, Roninn75 a écrit :

 

[Roninn75]

thank you Isabelle. i used the following:
Private Sub BxStaDistrict_AfterUpdate()
Dim Cl As Range
Dim ClAddress As String
Dim coll As New Collection
Dim itm As Variant
'if no selection in district quit
If Me.BxStaDistrict.ListIndex < 0 Then Exit Sub
With Sheet17
Set rSource = .Range(.Cells(1, 1), .Cells(.Rows.Count
1).End(xlUp))
End With
With Me
.BxStaName.Clear
.BxStaOIC.Clear
Set Cl = rSource.Find(wHAT:=Me.BxStaDistrict.Value
LookIn:=xlValues, lOOKaT:=xlWhole)
If Not Cl Is Nothing Then
ClAddress = Cl.Address
Do
On Error Resume Next
coll.Add Item:=Cl.Offset(0, 1).Value
Key:=CStr(Cl.Offset(0, 1).Value)
On Error GoTo 0
Set Cl = rSource.FindNext(After:=Cl)
Loop While Not Cl Is Nothing And Cl.Address <
ClAddress
End If
For Each itm In coll
.BxStaName.AddItem itm
Next itm
End With
End Sub

i have done this to create dependant comboboxes as there are 4 columns
am referring to... however, i now want to copy those values back to
different sheet in that order, but the unique values of the last colum
needs to be printed one below the other...
column A = District, column B = Town, column C = Name, Column D
Number

hope you can assist.

 

[GS]

Try this in the userform code window...

Option Explicit

Const msSRC_FILE$ = "<path\folder\sourcefile.xls>" '//edit to suit
Const lSRC_COL& = 1 '//edit to suit
Dim wkbSource As Workbook

Private Sub UserForm_Initialize()
Dim vData, sList$, n&
Set wkbSource = Workbooks.Open(msSRC_FILE)
vData = wkbSource.Sheets("Sheet1").UsedRange
vData = Application.Index(vData, 0, lSRC_COL)
For n = 1 To UBound(vData)
If Not InStr(sList, vData(n, 1)) Then _
sList = sList & "," & vData(n, 1)
Next 'n
ComboBox1.List = Split(Mid(sList, 2), ",")
End Sub

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Modified to iterate a list of cols to load multiple comboboxes...

Option Explicit

Const msSRC_FILE$ = "<path\folder\sourcefile.xls>" '//edit to suit
Const lSRC_COL& = 1 '//edit to suit
Dim wkbSource As Workbook, vData

Private Sub UserForm_Initialize()
' Set wkbSource = ThisWorkbook '//for testing only
Set wkbSource = Workbooks.Open(msSRC_FILE)
vData = wkbSource.Sheets("Sheet1").UsedRange
Load_CboList ComboBox1, 1
Load_CboList ComboBox2, 2
Load_CboList ComboBox3, 3
Load_CboList ComboBox4, 4
Set wkbSource = Nothing
End Sub

Sub Load_CboList(Cbo As ComboBox, ColNdx&)
Dim vLst, sList$, n&
vLst = Application.Index(vData, 0, ColNdx)
For n = 1 To UBound(vLst)
If Not InStr(sList, vLst(n, 1)) Then _
sList = sList & "," & vLst(n, 1)
Next ',
Cbo.List = Split(Mid(sList, 2), ",")
End Sub

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Note that in both examples I left the cleanup (close files, set
wkbsource=nothing) to you. I suggest closing the file after loading its
data into vData since it's no longer needed. I'd normally set objects I
create =Nothing in the _Terminate event.

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[isabelle]

hi Roninn75,

is that Gary's response responding at your needs?

isabelle

 

[Roninn75]

thank you both Gary and Isabelle...

i got the dependant comboboxes to function as i want thank you, but
need to write the result of the comboboxes back to resultant sheet. so
the 3 comboboxes i have represents column A: District, Column B: Town
Column C: Name, Column D: number.
For Column D i need that unique values to be held in either a variabl
or an array (whatever works best), then print it to the resultant shee
in that order.
an example of the result would be:
DISTRICT TOWN NAME NUMBER
East A John 345
East A John 451
East A John 185

i hope that makes sense, and once again thank you for your assistance.

 

[GS]

It might be as simple as...

Private Sub UserForm_Initialize()
' Set wkbSource = ThisWorkbook '//for testing only
Set wkbSource = Workbooks.Open(msSRC_FILE)
vData = wkbSource.Sheets("Sheet1").UsedRange
Set wkbSource = Nothing
Load_CboList ComboBox1, 1
Load_CboList ComboBox2, 2
Load_CboList ComboBox3, 3
Load_CboList ComboBox4, 4

'Put each list into worksheet
Set wkbTarget = Workbooks.Add
With wkbTarget.Sheets("Sheet1").Cells(1)
.Resize(ComboBox1.ListCount) = ComboBox1.List
.Offset(, 1).Resize(ComboBox2.ListCount) = ComboBox2.List
.Offset(, 2).Resize(ComboBox3.ListCount) = ComboBox3.List
.Offset(, 3).Resize(ComboBox4.ListCount) = ComboBox4.List
End With
End Sub

...where the resulting lists will be placed starting in A1 on "Sheet1"
of a new workbook. Modify this to suit your need so it gets to your
wkbTarget as expected!

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

In case it's not obvious to you, you'll need to modify the variable
defs at the top of the module like so...

Dim wkbSource As Workbook, wkbTarget As Workbook, vData

...to include the ref to the target workbook.

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[isabelle]

hi Roninn75,

is that applying an automatic filter on the source range would be a
possible solution?
(and then remove the filter before close the source)
you could populate the combobox with the filtered range, then copy the
same result somewhere else

is that the complete list is included in the ListBox?

isabelle

Le 2013-08-09 17:17, Roninn75 a écrit :

 

[GS]

is that the complete list is included in the ListBox?

Hi Isabelle,
I'm thinking the results lists are a subset of unique records in the
main data source. If so, I don't get why using comboboxes to separate
this out of all fields when the data may not align with its source
record row.

As you can see, there's no problem constructing a list of unique values
from each field. Question is whether this is what OP wants, *or* is OP
looking for unique records. (I see a point to either and so is why I
answered the request for each field)

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Roninn75]

Hi Gary and Isabelle
this is part of quite a large workbook, entries up to about 4000 rows.
i have the data loaded on the form as the user base is diverse and i a
trying to make the operation as simple as possible.
the source sheet is populated from a different workbook on a differen
server, the resultant sheet looks into the source sheet and only thos
unique values under NAME is processed and marked as complete.(that wil
be the second part of the workflow)
so i do not necessarilly want to filter the source sheet on the shee
as that will be protected. on the resultant sheet i am also adding
date, time and user stamp for further processing.

the data does not have to align with the source record row. ultimately
once the records are processed as complete i will be tracking those tha
wasnt processed.

regards and thank you for your help

;1613281']> is that the complete list is included in the ListBox?

Hi Isabelle,
I'm thinking the results lists are a subset of unique records in the
main data source. If so, I don't get why using comboboxes to separate
this out of all fields when the data may not align with its source
record row.

As you can see, there's no problem constructing a list of unique value

from each field. Question is whether this is what OP wants, *or* is OP
looking for unique records. (I see a point to either and so is why I
answered the request for each field)

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussio

 

[isabelle]

hi Garry,

i do not know i can not see how the whole file works.
that's why I'm go fishing more informations

isabelle

Le 2013-08-09 23:49, GS a écrit :

 

[GS]

I suspected your purpose was statistical in nature and so went with
that. I appreciate the feedback and the additional task details. I hope
I was able to help!

Just curious why you're using the comboboxes since the results from
filtering go irectly onto another worksheet. If you don't have any
other use (ie: user access) for the comboboxes then the task can be
done more efficiently without their added overhead.

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Roninn75]

hi Gary

essentially, this workbook will be used by quite a large number o
employees in our department. this will trace a workflow from level 1 t
4. each level has limited access.
i am sure there might be a more efficient way of doing this but this i
how i envisioned it to work. also the comboboxes allow for limited use
interference.

regards

;1613286']I suspected your purpose was statistical in nature and so wen
with
that. I appreciate the feedback and the additional task details. I hop

I was able to help!

Just curious why you're using the comboboxes since the results from
filtering go irectly onto another worksheet. If you don't have any
other use (ie: user access) for the comboboxes then the task can be
done more efficiently without their added overhead.

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussio

 

[GS]

hi Gary

essentially, this workbook will be used by quite a large number of
employees in our department. this will trace a workflow from level 1 to
4. each level has limited access.
i am sure there might be a more efficient way of doing this but this is
how i envisioned it to work. also the comboboxes allow for limited user
interference.

Well I still don't see the point of the comboboxes by your explanation.
My point is that if they don't serve any other purpose then why not
just put the filtered lists directly into the target sheet. You can do
this with a simple button control (or menuitem)! What else does the
userform get used for? If only to serve as a vehicle to make the lists
then it makes no sense to me!

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion