Checkbox enabled depending on dropdown list choice

[nelsonsix]

Hi everyone,

I have a spreadsheet with a form that has a dropdown list with a number of options. Underneath the dropdown box are 5 checkboxes. Depending on the choice from the dropdown, certain checkboxes are disabled. After lots of trial and error, the only way I can get it to sort of work is via the following code.

'Please Select
    If cboReport.Value = "Please Select" Then
        chkCashflow.Enabled = False
        chkInvestment.Enabled = False
        chkPension.Enabled = False
        chkIncome.Enabled = False
        chkComplex.Enabled = False
    End If
    
    'Investment EAR
    If cboReport.Value = "Investment EAR" Then
        chkInvestment.Enabled = False
        chkPension.Enabled = True
        chkCashflow.Enabled = True
        chkIncome.Enabled = True
        chkComplex.Enabled = True
    End If
    
     'Pension EAR
    If cboReport.Value = "Pension EAR" Then
        chkInvestment.Enabled = True
        chkPension.Enabled = False
        chkCashflow.Enabled = True
        chkIncome.Enabled = True
        chkComplex.Enabled = True
    End If
    
    'Cashflow / DB Report / New Pension / Cash Top Ups /
    If cboReport.Value = "Cashflow" Or cboReport.Value = "Cashflow Update/LFR" Or cboReport.Value = "New Pension (Simple)" Or cboReport.Value = "Top Up Report" Or cboReport.Value = "Cash Report (New Client)" Then
        chkCashflow.Enabled = False
        chkIncome.Enabled = True
        chkInvestment.Enabled = False
        chkPension.Enabled = False
        chkComplex.Enabled = True
    End If
    
    
    'Withdrawals / Protection
    If cboReport.Value = "Withdrawal Letter" Or cboReport.Value = "Protection" Then
        chkCashflow.Enabled = False
        chkIncome.Enabled = False
        chkInvestment.Enabled = False
        chkPension.Enabled = False
        chkComplex.Enabled = True
    End If
    
    
    'Ad Hoc / Income / Annual Review / Estate Planning
    If cboReport.Value = "Income Report" Or cboReport.Value = "Ad Hoc Report" Or cboReport.Value = "Annual Review" Then
        chkCashflow.Enabled = True
        chkIncome.Enabled = True
        chkInvestment.Enabled = True
        chkPension.Enabled = True
        chkComplex.Enabled = True
    End If
    
    'Tax Led
    If cboReport.Value = "Tax Led" Or cboReport.Value = "Trust Report" Then
        chkCashflow.Enabled = False
        chkIncome.Enabled = False
        chkInvestment.Enabled = True
        chkPension.Enabled = False
        chkComplex.Enabled = True
    End If
    
    'Drawdown Review / Addendum Letter / Change of Risk
    If cboReport.Value = "Drawdown Review" Or cboReport.Value = "Addendum Letter" Or cboReport.Value = "Change of Risk" Or cboReport.Value = "SSAS Review" Then
        chkCashflow.Enabled = False
        chkIncome.Enabled = False
        chkInvestment.Enabled = False
        chkPension.Enabled = False
        chkComplex.Enabled = True
    End If

The issue I am having is, sometimes the choice from the dropdown list is not showing the correct number of disabled checkboxes. E.G. If I choose DB Report immediately after loading the form all the checkboxes are enabled, but if I then switch to another option on the dropdown and then back to DB reports the correct checkboxes are disabled!

Any ideas on how I can better code this form?

Appreciate any help.

p.s. I am a novice VBA coder so please go easy on me.

Thanks
Neil

 

[dmt32]

Hi,
To reduce all that code, probably better to use the combobox listindex property.

Try this update to your code

make a backup of your workbook & place both following codes in your Userforms code page (ensure that you delete any existing procedure with same name)

Private Sub cboReport_Change()


    chkInvestment.Enabled = CBool(Not IsError(Application.Match(cboReport.ListIndex, Array(2, 10, 11, 12, 13, 14), False)))
    chkPension.Enabled = CBool(Not IsError(Application.Match(cboReport.ListIndex, Array(1, 10, 11, 12), False)))
    chkCashflow.Enabled = CBool(Not IsError(Application.Match(cboReport.ListIndex, Array(1, 2, 10, 11, 12), False)))
    chkIncome.Enabled = CBool(Not IsError(Application.Match(cboReport.ListIndex, Array(1, 2, 3, 4, 5, 6, 7, 10, 11, 12), False)))
    chkComplex.Enabled = CBool(cboReport.ListIndex > 0)


End Sub


Private Sub UserForm_Initialize()
    Dim arr As Variant
    arr = Array("Please Select", "Investment EAR", "Pension EAR", _
                 "Cashflow", "Cashflow Update/LFR", "New Pension (Simple)", "Top Up Report", "Cash Report (New Client)", _
                 "Withdrawal Letter", "Protection", _
                 "Income Report", "Ad Hoc Report", "Annual Review", _
                 "Tax Led", "Trust Report", _
                 "Drawdown Review", "Addendum Letter", "Change of Risk", "SSAS Review")
                 
    With Me.cboReport
        .RowSource = ""
        .List = arr
        .ListIndex = 0
    End With
End Sub

I have done this very quickly but way this should work is that when you make a selection the change event is triggered. Each of your checkboxes then tests the listindex value of the control using Match against listed array - If the value in the array is matched then True is returned and checkbox enabled otherwise false.

Hopefully, I have the indexing correct but you can update each array as required. Just be aware that Listindex starts at 0 for the first item.

You did not share how you were populating the combobox so I have added an array of list items to the Intialize event.


Hope Helpful

Dave

 

[nelsonsix]

Hi Dave,

Absolutely perfect again, thanks so much for your help.

Neil

 

[dmt32]

Hi Dave,

Absolutely perfect again, thanks so much for your help.

Neil

most welcome - glad solution helped.
Your Feedback very much appreciated

Dave