Object required for comboboxes/checkboxes that already exist on my worksheet?

Question

Okay, totally noob coder here. I'm working on a sheet that has a repeating series of 1 combo box linked to one other combobox and a checkbox. Basically, the first box has four selectable options. If either of the first two options is selected, both the second combobox and the checkbox should be disabled. If either of the last two options is selected, the second box and checkbox should both become enabled. When I first set up this code, everything worked like a dream. Then I accidently threw myself into an endless loop and had to start with the "excel recovered" worksheet, which scrubbed all my controls. I re-did all the controls, but now when I open the workbook, I have to click through fifty (yes, no joke, five-zero) instances of an "object required" run-time error. The same thing happens when I close the workbook. But when I clear all the errors, the code runs just fine. Using "Option Explicit" gives me a "variable not defined" error. I have been researching both errors for several hours now with no working results, as my objects required are obviously already there and I can't seem to be able to identify any variables which would need defining.

Here's my code:

Sub ComboBox1_Change()
    If ComboBox1.ListIndex = 2 Or ComboBox1.ListIndex = 3 Then
        ComboBox6.Enabled = True
        CheckBox1.Enabled = True
    Else: ComboBox6.Enabled = False
        CheckBox1.Enabled = False
    End If
End Sub

This is for one instance. I have twenty-five of these repeated, one after the other, in my full project, along with some buttons and other fun stuff. That's basically the barebones of what I have now. I'm probably missing something obvious, but I just don't know what right now. Hopefully someone can point me in the right direction?

Answer 1

FYI you can avoid most of that replicated code by using a naming scheme which allows you to associate your sets of 3 controls: eg see below. Then you can have one single procedure which performs the checks, given the first combobox as an argument.

e.g. (you can probably come up with more-meaningful names based on what your controls actually represent):

ComboBox1 - ComboBox1_2 - chkComboBox1
ComboBox2 - ComboBox2_2 - chkComboBox2
ComboBox3 - ComboBox3_2 - chkComboBox3

Then your code can look like this:

Option Explicit

Private Sub ComboBox1_Change()
    ProcessChange ComboBox1
End Sub

Private Sub ComboBox2_Change()
    ProcessChange ComboBox2
End Sub

'process the supplied combobox value and set properties of
'  any related controls
Sub ProcessChange(cmbo As ComboBox)
    Dim en As Boolean
    en = cmbo.Value = 2 Or cmbo.Value = 3
    Me.OLEObjects(cmbo.Name & "_2").Object.Enabled = en
    Me.OLEObjects("chk" & cmbo.Name).Object.Enabled = en
End Sub