Hello all,

i have a collection (this contains names from a list)
================================================== =====
it is used in a number of comboboxes
================================================== =====
if a selection is made from the list can it do this?
= for the record selected, input the data from combobox1
into column "AX" and the data from textbox (this will
depend on which combobox is being used) into column "AY"
= remove the record selected from the collection
================================================== =====
if a blank selection is made (existing record changed)
= for the record that was already in the combobox the
date in column "AX" needs to return to blank, and time in
column "AY" needs to return to blank
= the record then needs to return to the collection for
selection in another combobox
================================================== =====

if anyone has any queries or would like to let me know
that they are trying to help,

please e-mail me at


Thank you,

Robert Couchman

Robert,

Here's a starter for 10

Dim prevIndex As Long

Private Sub ComboBox1_Change()
If ComboBox1.Value = "" Then
Range("AX1").Value = ""
Range("AY1").Value = ""
myColl.Add ComboBox1.List(prevIndex), ComboBox1.List(prevIndex)
Else
Range("AX1").Value = ComboBox1.Value
Range("AY1").Value = TextBox1.Value
myColl.Remove ComboBox1.List(prevIndex)
prevIndex = ComboBox1.ListIndex
End If

End Sub


Private Sub UserForm_Activate()

ComboBox1.ListIndex = 0

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Robert Couchman" wrote in message
...
Hello all,

i have a collection (this contains names from a list)
================================================== =====
it is used in a number of comboboxes
================================================== =====
if a selection is made from the list can it do this?
= for the record selected, input the data from combobox1
into column "AX" and the data from textbox (this will
depend on which combobox is being used) into column "AY"
= remove the record selected from the collection
================================================== =====
if a blank selection is made (existing record changed)
= for the record that was already in the combobox the
date in column "AX" needs to return to blank, and time in
column "AY" needs to return to blank
= the record then needs to return to the collection for
selection in another combobox
================================================== =====

if anyone has any queries or would like to let me know
that they are trying to help,

please e-mail me at


Thank you,

Robert Couchman

Bob,

the only reference i have to these records is there unique
ID number in column A, i have searched through my records
to find these people, how do i reference to these rows?

thank you,

Robert Couchman

-----Original Message-----
Robert,

Must admit I wondered where the row was coming from<vbg.

It is not automatically stored, a coillection consists of
an index and a
value, you would know what your code stores. You can
store the row number as
the collection index, and retrieve this with

collIndex = myColl(Combobox1.Value)

or fully as

Private Sub ComboBox1_Change()
If ComboBox1.Value = "" Then
Range("AX" & mycoll(ComboBox1.Value)).Value = ""
Range("AY" & mycoll(ComboBox1.Value)).Value = ""
mycoll.Add ComboBox1.List(prevIndex),
ComboBox1.List(prevIndex)
Else
Range("AX" & prevIndex).Value = ComboBox1.Value
Range("AY1" & prevIndex).Value = TextBox1.Value
mycoll.Remove ComboBox1.List(prevIndex)
prevIndex = ComboBox1.ListIndex
End If

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)