hi good day

i have two workbooks, the one is used as a register the second has a form with some comboboxes. i want :
1. the combobox on the form to be go look in the 1st workbook at a 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 know application.events = false or true will open that workbook with out the user knowing it opened then close it again.

any assistance would be appreciated....

this has been solved he
http://www.excelforum.com/excel-prog...-workbook.html

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 :
hi good day

i have two workbooks, the one is used as a register the second has a
form with some comboboxes. i want :
1. the combobox on the form to be go look in the 1st workbook at a
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 know
application.events = false or true will open that workbook with out the
user knowing it opened then close it again.

any assistance would be appreciated....

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 i am referring to... however, i now want to copy those values back to a different sheet in that order, but the unique values of the last column needs to be printed one below the other...
column A = District, column B = Town, column C = Name, Column D = Number

hope you can assist.

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

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

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

hi Roninn75,

is that Gary's response responding at your needs?

isabelle

thank you both Gary and Isabelle...

i got the dependant comboboxes to function as i want thank you, but i 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 variable or an array (whatever works best), then print it to the resultant sheet 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.

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

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

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 :
thank you both Gary and Isabelle...

i got the dependant comboboxes to function as i want thank you, but i
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 variable
or an array (whatever works best), then print it to the resultant sheet
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.

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

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 am trying to make the operation as simple as possible.
the source sheet is populated from a different workbook on a different server, the resultant sheet looks into the source sheet and only those unique values under NAME is processed and marked as complete.(that will be the second part of the workflow)
so i do not necessarilly want to filter the source sheet on the sheet as that will be protected. on the resultant sheet i am also adding a 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 that wasnt processed.

regards and thank you for your help :)

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 :
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)

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

hi Gary

yes this is for statistical purposes.
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.

regards

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