VBA ActiveX check box value vs box being checked

[zborton]

New to the forms, thanks for the help. I hope I provided enough of the correct information.

As the tittle suggest I am having some issues using ActiveX check boxes. I am working on a setup with a UserForm which can hide column(s) based on if check boxs are checked or not. I have tried this from a few different approaches and have not had any luck with any of them.

The first approach.
I have the userform update how all of the check boxes are marked. The issue I am having is that the check boxes don't remember what they were check at last time I had the userform open, or if I maually hide columns it dosn't update the check boxes either. This works pretty well, I would just like for the click boxes to know if the column(s) that the check box is tied to is hidden and then either have the checkbox checked or unchecked based on that when I open up the userform.

I am not finished with this code, but here is what I have:

Private Sub Ok_Click()
       
    If gouda.Value = True Then
        Sheets("subs").Columns("o:o").Hidden = False
    Else
        Sheets("subs").Columns("o:o").Hidden = True
    End If
    
    If onions.Value = True Then
        Sheets("subs").Columns("L:L").Hidden = False
    Else
        Sheets("subs").Columns("L:L").Hidden = True
    End If
    
    If peppers.Value = True Then
        Sheets("subs").Columns("i:i").Hidden = False
    Else
        Sheets("subs").Columns("i:i").Hidden = True
    End If
    
    If peppers.Value = True Then Range("a1").Value = 1
    If peppers.Value = False Then Range("a1").Value = 0
    
End Sub

The second approach.
I tied each of the click buttons to run the hide command. The issue I have is that I will run the userform and hide the rows I want, then exit out; but when I go back in, the check boxes don't remember the position I left them in and if I try and unhide/hide some of the same things, my check boxes get inverted. This also happens in I go in a manually hide a column, the check box still shows up the default.

Private Sub Gouda_Click()
    If Sheets("subs").Columns("o:o").Hidden = True Then
        Sheets("subs").Columns("o:o").Hidden = False
    Else
        Sheets("subs").Columns("o:o").Hidden = True
    End If
End Sub

Private Sub Onions_Click()
    If Sheets("subs").Columns("L:L").Hidden = True Then
        Sheets("subs").Columns("L:L").Hidden = False
    Else
        Sheets("subs").Columns("L:L").Hidden = True
    End If
End Sub

Private Sub Peppers_Click()
     If Sheets("subs").Columns("i:i").Hidden = True Then
        Sheets("subs").Columns("i:i").Hidden = False
    Else
        Sheets("subs").Columns("i:i").Hidden = True
    End If
End Sub


Private Sub Swiss_Click()
     If Sheets("subs").Columns("f:f").Hidden = True Then
        Sheets("subs").Columns("f:f").Hidden = False
    Else
        Sheets("subs").Columns("f:f").Hidden = True
    End If
End Sub

Private Sub sub1_Click()
'Hides all of sandwhich2 if unclicked, lettuce and tomato
'Makes Letuce and tomato unable to be clicked if sandwich2 is unclicked
    
    Select Case Sub1.Value
        Case True: peppers.Enabled = True
        Case False: peppers.Enabled = False
    End Select
        
    Select Case Sub1.Value
        Case True: swiss.Enabled = True
        Case False: swiss.Enabled = False
    End Select
    
    Select Case Sub1.Value
        Case True: peppers = True
        Case False: peppers = False
    End Select
       
    Select Case Sub1.Value
        Case True: swiss = True
        Case False: swiss = False
    End Select
    
    Select Case Sub1.Value
        Case True: Sheets("subs").Columns("d:e").Hidden = False
        Case False: Sheets("subs").Columns("d:e").Hidden = True
    End Select
    
    Select Case Sub1.Value
        Case True: Sheets("subs").Columns("g:h").Hidden = False
        Case False: Sheets("subs").Columns("g:h").Hidden = True
    End Select
    
        Select Case Sub1.Value
        Case True: Sheets("subs").Columns("j:j").Hidden = False
        Case False: Sheets("subs").Columns("j:j").Hidden = True
    End Select
    
End Sub

Private Sub sub2_Click()
'Hides all of sandwhich2 if unclicked, lettuce and tomato
'Makes Letuce and tomato unable to be clicked if sandwich2 is unclicked
    
    Select Case sub2.Value
        Case True: gouda.Enabled = True
        Case False: gouda.Enabled = False
    End Select
        
    Select Case sub2.Value
        Case True: onions.Enabled = True
        Case False: onions.Enabled = False
    End Select
    
    Select Case sub2.Value
        Case True: gouda = True
        Case False: gouda = False
    End Select
       
    Select Case sub2.Value
        Case True: onions = True
        Case False: onions = False
    End Select
    
    Select Case sub2.Value
        Case True: Sheets("subs").Columns("m:n").Hidden = False
        Case False: Sheets("subs").Columns("m:n").Hidden = True
    End Select
    
    Select Case sub2.Value
        Case True: Sheets("subs").Columns("p:r").Hidden = False
        Case False: Sheets("subs").Columns("p:r").Hidden = True
    End Select
    
End Sub

Private Sub Meats_Click()
    
    Select Case Sub1.Value
        Case True:      Select Case Meats.Value
                            Case True: Sheets("subs").Columns("d:d").Hidden = False
                            Case False: Sheets("subs").Columns("d:d").Hidden = True
                        End Select
        
        Case False:
    End Select

    Select Case Sub1.Value
        Case True:      Select Case Meats.Value
                            Case True: Sheets("subs").Columns("h:h").Hidden = False
                            Case False: Sheets("subs").Columns("h:h").Hidden = True
                        End Select
        
        Case False:
    End Select
    
    Select Case sub2.Value
        Case True:      Select Case Meats.Value
                            Case True: Sheets("subs").Columns("m:m").Hidden = False
                            Case False: Sheets("subs").Columns("m:m").Hidden = True
                        End Select
        
        Case False:
    End Select

    Select Case sub2.Value
        Case True:      Select Case Meats.Value
                            Case True: Sheets("subs").Columns("p:p").Hidden = False
                            Case False: Sheets("subs").Columns("p:p").Hidden = True
                        End Select
        
        Case False:
    End Select
    
End Sub

I have tried to use some if/then statments to update the value for the check boxes, but that doesn't seem to work either. below is some sample code.

Private Sub setup_options_Initialize()

    'checks all the boxes when the setup options form is opened
    If Sheets("subs").Range("a1").Value = 1 Then peppers.Value = True
    If Sheets("subs").Range("a1").Value = 0 Then peppers.Value = False

End Sub

A picture of the setup might help explain everything a little: http://imgur.com/a/JC6tw

 

[V_Malkoti]

OK. Tell me what you're trying to do and what is going wrong in Update_Click(). What exactly do you mean by "cannot get all of the parts to work at the same time"?

 

[zborton]

Finished! thanks for the help everyone.

Private Sub Update_Click()
'====================================================
'Update sheet based on checkbox selections
'====================================================

    With Sheets("subs")
    
        'hides all the columns that meats are in
        Columns("d:d").Hidden = Not (Meats.Value) Or Not Sub1.Value
        Columns("h:h").Hidden = Not (Meats.Value) Or Not Sub1.Value
        Columns("m:m").Hidden = Not (Meats.Value) Or Not sub2.Value
        Columns("p:p").Hidden = Not (Meats.Value) Or Not sub2.Value
    
        'Hides the indicated column based on single ingredient checkboxes
        Columns("o:o").Hidden = Not (gouda.Value) Or Not sub2.Value
        Columns("L:L").Hidden = Not (onions.Value) Or Not sub2.Value
        Columns("i:i").Hidden = Not (peppers.Value) Or Not Sub1.Value
        Columns("f:f").Hidden = Not (swiss.Value) Or Not Sub1.Value
       
        'Hides all the columns associated with sub1
        Columns("d:d").Hidden = Not (Sub1.Value) Or Not Meats.Value
        Columns("e:e").Hidden = Not (Sub1.Value)
        Columns("f:f").Hidden = Not (Sub1.Value) Or Not swiss.Value
        Columns("g:g").Hidden = Not (Sub1.Value)
        Columns("h:h").Hidden = Not (Sub1.Value) Or Not Meats.Value
        Columns("i:i").Hidden = Not (Sub1.Value) Or Not peppers.Value
        Columns("j:j").Hidden = Not (Sub1.Value)
        
        'Hides all the columns associated with sub2
        Columns("L:L").Hidden = Not (sub2.Value) Or Not onions.Value
        Columns("m:m").Hidden = Not (sub2.Value) Or Not Meats.Value
        Columns("n:n").Hidden = Not (sub2.Value)
        Columns("o:o").Hidden = Not (sub2.Value) Or Not gouda.Value
        Columns("p:p").Hidden = Not (sub2.Value) Or Not Meats.Value
        Columns("q:q").Hidden = Not (sub2.Value)
        Columns("r:r").Hidden = Not (sub2.Value)
                        
    End With
        
        'disables individual checkboxes when the main category is not shown
        swiss.Enabled = Sub1.Value
        peppers.Enabled = Sub1.Value
        onions.Enabled = sub2.Value
        gouda.Enabled = sub2.Value
    
End Sub

Private Sub UserForm_Initialize()
'====================================================
'Setup checkboxes based on what is hidden
'====================================================

    With Sheets("subs")
        'checks/unchecks box for single ingredients based on if they are hidden or shown
        gouda.Value = Not .Columns("o:o").Hidden
        onions.Value = Not .Columns("L:L").Hidden
        peppers.Value = Not .Columns("i:i").Hidden
        swiss.Value = Not .Columns("f:f").Hidden
        
        'checks/unchecks box for multiple ingredients based on if every ingredients involved is hidden or shown
        Sub1.Value = .Columns("d:d").Show And .Columns("E:E").Show And swiss.Value And .Columns("g:g").Show And .Columns("h:h").Show And peppers.Value And .Columns("j:j").Show
        sub2.Value = onions.Value And .Columns("m:m").Show And .Columns("n:n").Show And gouda.Value And .Columns("p:p").Show And .Columns("q:q").Show And .Columns("r:r").Show
        Meats.Value = Not .Columns("d:d").Hidden Or Not .Columns("h:h").Hidden Or Not .Columns("m:m").Hidden Or Not .Columns("p:p").Hidden
        
    End With
    
        'disables individual checkboxes when the main category is not shown
        swiss.Enabled = Sub1.Value
        peppers.Enabled = Sub1.Value
        onions.Enabled = sub2.Value
        gouda.Enabled = sub2.Value
    
End Sub

I know there is a few duplicate lines, I kept them in there for understanding what is going on.