Combo down box

G

gavmer

Hello all,

I have a series of combo-boxes that relate to options within
worksheet. The combo boxes are side by side and have the sam
listfillrange. What i am trying to achieve is if one of the boxes i
used to select, then the other cannot be used. There are approx 5
combo boxes and they run in pairs, side by side.

Any ideas????

Cheers!!!
 
D

Dave Peterson

John Walkenbach has some code that groups commandbuttons on a userform at:
http://j-walk.com/ss/excel/tips/tip44.htm

It could be modified to work with a comboboxes (from the control toolbox
toolbar) on a worksheet:


In a general module:

Option Explicit
Dim cBox() As New Class1
Sub auto_open()
Dim cBoxCtr As Long
Dim OLEObj As OLEObject

cBoxCtr = 0
For Each OLEObj In Worksheets("sheet1").OLEObjects
If TypeOf OLEObj.Object Is MSForms.ComboBox Then
cBoxCtr = cBoxCtr + 1
ReDim Preserve cBox(1 To cBoxCtr)
Set cBox(cBoxCtr).ComboBoxGroup = OLEObj.Object
End If
Next OLEObj
End Sub

In a Class module (named Class1):

Option Explicit
Public WithEvents ComboBoxGroup As MSForms.ComboBox
Private Sub ComboBoxGroup_Change()

Dim OLEObj As OLEObject

For Each OLEObj In Worksheets("Sheet1").OLEObjects
If TypeOf OLEObj.Object Is MSForms.ComboBox Then
If OLEObj.Name = ComboBoxGroup.Name Then
'do nothing
Else
If ComboBoxGroup.Object.ListIndex <> 0 Then
OLEObj.Enabled = False
OLEObj.Object.BackColor = &H80000013
Else
OLEObj.Enabled = True
OLEObj.Object.BackColor = &H80000005
End If
End If
End If
Next OLEObj
End Sub


The name of my worksheet that held the comboboxes is Sheet1.

I figured that there had to be a way to let the user change his mind and choose
a different combobox. I used the first value in the listfillrange as that
indicator. In my example, I had: "Choose An Item" in that first position.

As soon as you select a value different from the first item, then the other
comboboxes on that sheet are disabled and greyed out.

If you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top