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

Please Login or Register  to view this content.

Class Module - MyComboBox

Please Login or Register  to view this content.

Macro to Add and Setup ComboBoxes to Sheet1

Please Login or Register  to view this content.

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.

Hello excelrequired,

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

Macro to SubClass ComboBoxes on Sheet1

Please Login or Register  to view this content.

Class Module - MyComboBox

Please Login or Register  to view this content.