Combo Box - Enabled and Disabled based on another Combo Box Entry

Hello excelrequired,

Welcome to the Forum!

It would be beneficial if you posted your workbook. It will reduce the need for questions and allow any solutions to be fully tested on the actual workbook data.

How To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

Please find attached the spreadsheet. Do not use Scoring spreadsheet. Please use Marks Spreadsheet.

I have a 200 row spreadsheet where C2 to C200, all rows have combo boxes. The possible selections within the combo box are "Yes" or "No".

I want a combo box to be enabled in D2 to D200, only if the corresponding C row value is a "yes". If the value is no, the combo box in D column should be disabled and not available for selection.

For Example:

C37 D37
Yes Combo Box available

C38 D38
No Should be No combo box available

Hello excelrequired,

There is only 1 combo box on the worksheet in cell C2. What are the selections for the combo box that will be in D2?

Hello excelrequired,

The attached workbook contains 2 macros and 1 class. The easiest way to reduce the code needed for all these ComboBoxes is to use a technique called SubClassing. This allows a group on controls to share the same events and properties.

The macro called AddComboBoxes removes all the existing comboboxes on Sheet1 and replaces them. The comboboxes are are added to column "C" and then to column "D" keeping the names sequential which makes referencing the controls much easier. The macro also sizes the combobox to fit the cell and adds the appropriate ListFillRange. The last row of the grid is determined by the borders of the last cell in column "A". So, if you change the grid size all you need to do is run this macro.

The main macro subclasses all the comboboxes on Sheet1. This happens when the workbook is first opened. This allows the comboboxes in column "C" to enable or disable the adjacent combobox in column "D" based on combobox "C"'s selection.

Macro to SubClass the ComboBoxes on Sheet1

Public SheetComboBoxes As Collection

Sub SubClassComboBoxes()

    Dim CBO As MyComboBox
    Dim Obj As Object
    Dim Wks As Worksheet
    
        Set SheetComboBoxes = New Collection
        
        Set Wks = Sheet1
        
            For Each Obj In Sheet1.OLEObjects
                If TypeName(Obj.Object) = "ComboBox" Then
                    Set CBO = New MyComboBox
                    Set CBO.ActiveXCombo = Obj.Object
                    Set CBO.Parent = Obj
                    SheetComboBoxes.Add CBO, Obj.Name
                End If
            Next Obj
    
End Sub

Class Module - MyComboBox

Public WithEvents ActiveXCombo As MSForms.ComboBox

Dim i As Long
Dim n As Long
Dim pvtParent As OLEObject

Private Sub ActiveXCombo_Click()
    
    Dim ComboName As String
    
        ComboName = pvtParent.Name
        i = Len(ComboName)
        
        Do While i > 0
            n = Asc(Mid(ComboName, i, 1))
            i = i - 1
            If i < Asc("0") Or i > Asc("9") Then Exit Do
        Loop
        
        If i Then
            n = CLng(Right(ComboName, Len(ComboName) - i))
            n = n + (SheetComboBoxes.Count \ 2)
        End If
    
        If UCase(ActiveXCombo.Value) = "NO" Then
            SheetComboBoxes("ComboBox" & n).Parent.Enabled = False
        Else
            SheetComboBoxes("ComboBox" & n).Parent.Enabled = True
        End If
        
End Sub

Property Set Parent(ByRef ParentObj As Object)
    If pvtParent Is Nothing Then
        Set pvtParent = ParentObj
    End If
End Property

Property Get Parent() As Object
    Set Parent = pvtParent
End Property

Macro to Add and Setup ComboBoxes to Sheet1

Sub AddComboBoxes()

    Dim CBO     As Object
    Dim Cell    As Range
    Dim FoundIt As Range
    Dim Item    As Variant
    Dim n       As Long
    Dim Wks     As Worksheet
    
        Set Wks = Sheet1
                        
      ' Find the last row in the grid by borders.
        With Application.FindFormat
            .Clear
            .Borders(xlEdgeLeft).LineStyle = xlContinuous
            .Borders(xlEdgeLeft).Weight = xlMedium
            .Borders(xlEdgeLeft).ColorIndex = xlColorIndexAutomatic
            
            .Borders(xlEdgeRight).LineStyle = xlContinuous
            .Borders(xlEdgeRight).Weight = xlThin
            .Borders(xlEdgeRight).ColorIndex = xlColorIndexAutomatic
            
            .Borders(xlEdgeTop).LineStyle = xlContinuous
            .Borders(xlEdgeTop).Weight = xlThin
            .Borders(xlEdgeTop).ColorIndex = xlColorIndexAutomatic
            
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
            .Borders(xlEdgeBottom).Weight = xlMedium
            .Borders(xlEdgeBottom).ColorIndex = xlColorIndexAutomatic
        End With
        
        Set FoundIt = Wks.Columns(1).Cells.Find("", Wks.Cells(1, 1), xlValues, xlWhole, xlByRows, xlNext, False, False, True)
        If FoundIt Is Nothing Then Exit Sub
        
          ' Remove any previous Combo Boxes.
            For Each Obj In Wks.OLEObjects
                Obj.Delete
            Next Obj
            
            Application.ScreenUpdating = False
        
          ' Add the Combo Boxes in sequential order to columns "C:D".
            For Each Item In Array(Array(Wks.Range("C2"), "Yes"), Array(Wks.Range("D2"), "Rating"))
                Set Cell = Item(0)
                For n = Cell.Row To FoundIt.Row
                    With Cell
                        l = .Left + 1
                        t = .Top + 1
                        h = .Height - 2
                        w = .Width - 2
                    End With
                    Set CBO = Wks.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Left:=l, Top:=t, Height:=h, Width:=w)
                    CBO.ListFillRange = Item(1)
                    Set Cell = Cell.Offset(1, 0)
               Next n
            Next Item
            
            Application.ScreenUpdating = True
        
End Sub

Hello excelrequired,

Okay, got the problem fixed. Use this workbook. The modified code is below.

Macro to SubClass ComboBoxes on Sheet1

Public SheetComboBoxes As Collection

Sub SubClassComboBoxes()

    Dim CBO As MyComboBox
    Dim Obj As Object
    Dim Wks As Worksheet
    
        Set SheetComboBoxes = New Collection
        
        Set Wks = Sheet1
        
          ' SubClass only the CheckBoxes in column "C".
            For n = 1 To Wks.OLEObjects.Count \ 2
                Set Obj = Wks.OLEObjects(n)
                If TypeName(Obj.Object) = "ComboBox" Then
                    Set CBO = New MyComboBox
                    Set CBO.ActiveXCombo = Obj.Object
                    Set CBO.Parent = Obj
                    SheetComboBoxes.Add CBO, Obj.Name
                End If
            Next n
            
          ' Add CheckBoxes in column "D" for referencing.
            For n = Wks.OLEObjects.Count \ 2 + 1 To Wks.OLEObjects.Count
                Set Obj = Wks.OLEObjects(n)
                x = Obj.Name
                SheetComboBoxes.Add Obj, Obj.Name
            Next n
    
End Sub

Class Module - MyComboBox

Public WithEvents ActiveXCombo As MSForms.ComboBox

Dim i As Long
Dim n As Long
Dim pvtParent As OLEObject

Private Sub ActiveXCombo_Click()
    
    Dim ComboName As String
    Dim i As Long
    Dim n As Long
    
        ComboName = pvtParent.Name
        i = Len(ComboName)
        
        Do While i > 0
            n = Asc(Mid(ComboName, i, 1))
            If n < Asc("0") Or n > Asc("9") Then Exit Do
            i = i - 1
        Loop
        
        If i Then
            n = CLng(Right(ComboName, Len(ComboName) - i))
            n = n + (SheetComboBoxes.Count \ 2)
        End If
    
        If UCase(ActiveXCombo.Value) = "NO" Then
            SheetComboBoxes("ComboBox" & n).Enabled = False
        Else
            SheetComboBoxes("ComboBox" & n).Enabled = True
        End If
        
End Sub

Property Set Parent(ByRef ParentObj As Object)
    If pvtParent Is Nothing Then
        Set pvtParent = ParentObj
    End If
End Property

Property Get Parent() As Object
    Set Parent = pvtParent
End Property

Hello excelrequired,

Thanks, but I found a problem in code. It is not working with all check boxes in column "D'. I am working on correcting it now.