Not sure if this is possible!!

  • Thread starter Thread starter Robert Couchman
  • Start date Start date
R

Robert Couchman

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
(e-mail address removed)

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)
 
Thank you Bob,

the problem i am having with your code though is that it
is updating cells "AX1" and "AY1" these cells are headers,
i am trying to get it to update the AX and AY value of the
record,

e.g. my record is located on row 7 so it needs to
update "AX7" and "AY7"

is the row number or location stored in the collection
anywhere?
how do i call this?

******************************************************
MyColl.Add Cells(k, "B").Value + " " + Cells
(k, "D").Value, Cells(k, "B").Text
******************************************************

was the code i used to add to the collection.

Thank you,

Robert Couchman
([email protected])
 
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)
 
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
 
Back
Top