• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Checkbox Issue in VBA

smiley

New Member
I have an issue with ActiveX checkboxes. I have a checkbox for the heading RESOLVED, and another checkbox for the heading UNRESOLVED. Beneath RESOLVED I have 3 more checkboxes for 3 scenarios. Under UNRESOLVED I have 2 checkboxes along with a text box for an ID.

It looks like the following:

__ Resolved (Checkbox 36 / Cell aa12)
__ Walked the customer through (Checkbox 39 / Cell ad12)
__ Provided information requested (Checkbox 40 / Cell ae12)
__ Further help declined (Checkbox 41 / Cell aa13)

__ Unresolved (Checkbox 37 / Cell ab12)
__ Customer to call later (Checkbox 42 / Cell ab13)
__ Issue escalated (Checkbox 43 / Cell ac13)
ID ______________

I have the checkboxes linked to cells in the spreadsheet.

FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​

So it works like this:

_x Resolved

TRUE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​

_x Resolved
_x Walked the customer through

TRUE​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​

And like this:

_x Unresolved

FALSE​
TRUE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​

_x Unresolved
_x Customer to call later

FALSE​
TRUE​
FALSE​
FALSE​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​

I have the code set up so that I can click the checkboxes individually, or I can click a sub-category (like “Walked the customer through”) and it will automatically check the category box as well (“Resolved”). Also, if I check on an “Unresolved” sub-category (like “Customer to call later”), the category box for “Unresolved” will be checked.

The problem I’m having is when I click on a sub-category, like “Customer to call later”.

FALSE​
TRUE​
FALSE​
FALSE​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​

And then I change my mind and select a sub-category in the other group, like “Walked the customer through.”

The screen looks like this:

_x Resolved
_x Walked the customer through

But the spreadsheet looks like the following, as though nothing was selected:

FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​

Following is my code:

Code:
Private Sub CB36_click()  'Resolved.
    If [aa12] Then  'If RESOLVED is true....
        [ab12] = False  'Clear unresolved.
        [ab13] = False  'Clear unresolved.
        [ac13] = False  'Clear unresolved.
        [d70] = ""  'Clear unresolved.
    Else  'If RESOLVED is false....
        [aa12] = False  'Clear resolved.
        [ad12] = False  'Clear resolved.
        [ae12] = False  'Clear resolved.
        [aa13] = False  'Clear resolved.
    End If
End Sub

Private Sub CB37_click()  'Unresolved.
    If [ab12] Then  'If UNRESOLVED is true....
        [aa12] = False  'Clear resolved.
        [ad12] = False  'Clear resolved.
        [ae12] = False  'Clear resolved.
        [aa13] = False  'Clear resolved.
    Else  'If UNRESOLVED is false....
        [ab12] = False  'Clear unresolved.
        [ab13] = False  'Clear unresolved.
        [ac13] = False  'Clear unresolved.
        [d70] = ""  'Clear unresolved.
    End If
End Sub

Private Sub CB39_click()
    If [ad12] And Not [aa12] Then
        [aa12] = True
    End If
    If Not [ad12] And [aa12] Then
        [aa12] = False
    End If
    [ab12] = False  'Clear unresolved.
    [ab13] = False  'Clear unresolved.
    [ac13] = False  'Clear unresolved.
    [d70] = ""  'Clear unresolved.
End Sub

Private Sub CB40_click()
    If [ae12] And Not [aa12] Then
        [aa12] = True
    End If
    If Not [ae12] And [aa12] Then
        [aa12] = False
    End If
    [ab12] = False  'Clear unresolved.
    [ab13] = False  'Clear unresolved.
    [ac13] = False  'Clear unresolved.
    [d70] = ""  'Clear unresolved.
End Sub

Private Sub CB41_click()
    If [aa13] And Not [aa12] Then
        [aa12] = True
    End If
    If Not [aa13] And [aa12] Then
        [aa12] = False
    End If
    [ab12] = False  'Clear unresolved.
    [ab13] = False  'Clear unresolved.
    [ac13] = False  'Clear unresolved.
    [d70] = ""  'Clear unresolved.
End Sub

Private Sub CB42_click()
    If [ab13] And Not [ab12] Then
        [ab12] = True
    End If
    If Not [ab13] And [ab12] Then
        [ab12] = False
    End If
    [aa12] = False  'Clear resolved.
    [ad12] = False  'Clear resolved.
    [ae12] = False  'Clear resolved.
    [aa13] = False  'Clear resolved.
End Sub

Private Sub CB43_click()
    If [ac13] And Not [ab12] Then
        [ab12] = True
    End If
    If Not [ac13] And [ab12] Then
        [ab12] = False
    End If
    [aa12] = False  'Clear resolved.
    [ad12] = False  'Clear resolved.
    [ae12] = False  'Clear resolved.
    [aa13] = False  'Clear resolved.
End Sub
Any assistance would be greatly appreciated!
 

smiley

New Member
That's VERY similar, but not exactly ... I don't want ALL of the sub-categories initially selected if I click on either RESOLVED or UNRESOLVED. And I'm using checkboxes which map to various cells, and it's these cells which don't seem to be updating properly....
 

vletm

Excel Ninja
smiley
The 1st don't want is possible to modify.
Your snapshots checkboxes don't work at all.
... and I won't use ActiveX-components at all - those would do same.
Your original writing should be more clear - You should able to explain - what should happen?
 
Top