Little task

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

Robert Couchman

i am after help,

i am trying to make a list for my comboboxes.

the list needs to conatin all data where column "AX" "AY"
and "AZ" are equal to "".

also, this list needs to be updated by all the comboboxes
on the page.

if i take a value from the list, the value needs to be
removed from the list.

Can anyone help??

Thank you,

Robert Couchman
 
So which column does the information in the list come from?

Do you mean that all comboboxes on the sheet need to share the same list and
that if you select an item from any single combobox, it will be removed from
the list of all comboboxes - will that row also be deleted from the sheet.

Are these activeX comboboxes?
 
Hi Tom,

the information needed is as follows,
if it is possible reference from column "A" (but needs to
be hidden)=>
first name from column "B"==>
last name from column "C"==<END>

Yes, i mean all comboboxes need to share the same list!
(if this is possible)

Yes, the data needs to be deleted off of the list,
BUT, NO the data does not need deleting from workbook!

i believe these are activeX comboboxes (the ones used in
VB as a standard combobox button).

Thank you,

Robert Couchman
 
In a general module:
Public Sub AdjustList(cbox As MSForms.ComboBox)
Dim cbox1 As MSForms.ComboBox
Dim obj As OLEObject
Dim idex As Long
On Error GoTo ErrHandler:
If bBlockEvents Then Exit Sub
bBlockEvents = True
idex = cbox.ListIndex
If idex <> -1 Then
For Each obj In Worksheets("Sheet1").OLEObjects
If TypeOf obj.Object Is MSForms.ComboBox Then
Set cbox1 = obj.Object
cbox1.ListIndex = -1
cbox1.Value = ""
cbox1.RemoveItem idex
End If
Next
End If
ErrHandler:
bBlockEvents = False
End Sub


in the sheet module of the sheet with the comboboxes:


Private Sub Combobox1_Click()
If bBlockEvents Then Exit Sub
AdjustList ComboBox1
End Sub

Private Sub Combobox2_Click()
If bBlockEvents Then Exit Sub
AdjustList ComboBox2
End Sub

Private Sub Combobox3_Click()
If bBlockEvents Then Exit Sub
AdjustList ComboBox3
End Sub

' one event for each combobox

In the Thisworkbook Module

Private Sub Workbook_Open()
On Error GoTo ErrHandler:
bBlockEvents = True
SetBoxes
ErrHandler:
bBlockEvents = False
End Sub

Each time you open the workbook, the lists will be re intialized and include
what is in columns A, B, C.

--
Regards,
Tom Ogilvy




Robert Couchman said:
Hi Tom,

the information needed is as follows,
if it is possible reference from column "A" (but needs to
be hidden)=>
first name from column "B"==>
last name from column "C"==<END>

Yes, i mean all comboboxes need to share the same list!
(if this is possible)

Yes, the data needs to be deleted off of the list,
BUT, NO the data does not need deleting from workbook!

i believe these are activeX comboboxes (the ones used in
VB as a standard combobox button).

Thank you,

Robert Couchman
Click to expand...
 
Back
Top