VBA ActiveX check box value vs box being checked

zborton

zborton

New Member
Joined
Jun 25, 2015
Messages
7
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:
Code: 
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.

Code: 
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.

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

JC6tw


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

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Does this help?

Code: 
Private Sub Ok_Click()
    Sheets("subs").Columns("o:o").Hidden = Not (gouda.Value)
    Sheets("subs").Columns("L:L").Hidden = Not (onions.Value)
    Sheets("subs").Columns("i:i").Hidden = Not (peppers.Value)
    Range("a1").Value = Abs(CInt(peppers.Value))
End Sub


Private Sub [COLOR=#0000ff]UserForm[/COLOR]_Activate()
    gouda.Value = Not (Sheets("subs").Columns("o:o").Hidden)
    onions.Value = Not (Sheets("subs").Columns("L:L").Hidden)
    peppers.Value = Not (Sheets("subs").Columns("i:i").Hidden)
End Sub

Replace UserForm word with whatever you've named your userform.
 
Upvote 0
Try this in the Userform Module
Code: 
Private Sub Ok_Click()
With Sheets("subs")
     .Columns("o:o").Hidden = Gouda.Value
     .Columns("L:L").Hidden = Onions.Value
     .Columns("i:i").Hidden = Peppers.Value
End With
End Sub


Private Sub UserForm_Initialize()
With Sheets("Subs")
    Gouda.Value = .Columns("o:o").Hidden
    Onions.Value = .Columns("L:L").Hidden
    Peppers.Value = .Columns("i:i").Hidden
End With
End Sub
 
Upvote 0
Since your using Textbox's you would need to use this:

If Sheets("subs").Range("a1").Value = 1 Then peppers.Value = "True"

Put True inside quotes
If you were using option buttons you could use just True
 
Upvote 0
MickG said:
Try this in the Userform Module
Code: 
Private Sub Ok_Click()
With Sheets("subs")
     .Columns("o:o").Hidden = Gouda.Value
     .Columns("L:L").Hidden = Onions.Value
     .Columns("i:i").Hidden = Peppers.Value
End With
End Sub


Private Sub UserForm_Initialize()
With Sheets("Subs")
    Gouda.Value = .Columns("o:o").Hidden
    Onions.Value = .Columns("L:L").Hidden
    Peppers.Value = .Columns("i:i").Hidden
End With
End Sub
Click to expand...

Thanks everyone for the quick response. I like the suggestions, they have really condensed the code down significantly.

There still seems to be a few problems to iron out though. When I add in the rest of the check boxes, they either don't work (meats) or they don't do anything (peppers, swiss, gouda, onions).

The userform_initialize() and userform_activate() parts of the code don't do anything, at least noticeable. I can delete that part of the code out and I don't notice anything has changed.

I still have my original main problem. I will try to explain it better. When column "I:I" is hidden I would like for when my userform is opened to show that peppers to be unchecked, basically linking hidden columns to the checkboxes directly.

the current code:
Code: 
Private Sub Ok_Click()
    With Sheets("subs")
        Columns("o:o").Hidden = Not (gouda.Value)
        Columns("L:L").Hidden = Not (onions.Value)
        Columns("i:i").Hidden = Not (peppers.Value)
        Columns("f:f").Hidden = Not (swiss.Value)
        Columns("d:j").Hidden = Not (Sub1.Value)
        Columns("L:r").Hidden = Not (sub2.Value)
        Columns("L:d" And "h:h" And "m:m" And "p:p").Hidden = Not (Meats.Value)
    End With
        
    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 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
    
End Sub

Private Sub setup_options_Initialize()

    With Sheets("subs")
        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
        Sub1.Value = Not .Columns("d:j").Hidden
        sub2.Value = Not .Columns("L:r").Hidden
        Meats.Value = Not .Columns("L:d" And "h:h" And "m:m" And "p:p").Hidden
    End With

End Sub
 
Upvote 0
zborton, setting up values of checkboxes is what UserForm_Initialize() and UserForm_Activate() were suggested for. These subroutines are run when your userform is initialized/activated. And the code inside this sub then sets/unsets the checkboxes based on whether or not columns are hidden. If you look at that code, its the reverse of the code in OK_Click() subroutine.

Code: 
Private Sub Ok_Click()
    Sheets("subs").Columns("o:o").Hidden = Not (gouda.Value)
    Sheets("subs").Columns("L:L").Hidden = Not (onions.Value)
    Sheets("subs").Columns("i:i").Hidden = Not (peppers.Value)
    Range("a1").Value = Abs(CInt(peppers.Value))
End Sub




Private Sub [COLOR=#0000ff]UserForm[/COLOR]_Activate()
    gouda.Value = Not (Sheets("subs").Columns("o:o").Hidden)
    onions.Value = Not (Sheets("subs").Columns("L:L").Hidden)
    peppers.Value = Not (Sheets("subs").Columns("i:i").Hidden)
End Sub

You just need to replace the word UserForm in method signature with the actual name of your userform.
 
Last edited:
Upvote 0
V_Malkoti said:
zborton, setting up values of checkboxes is what UserForm_Initialize() and UserForm_Activate() were suggested for. These subroutines are run when your userform is initialized/activated. And the code inside this sub then sets/unsets the checkboxes based on whether or not columns are hidden. If you look at that code, its the reverse of the code in OK_Click() subroutine.

Code: 
Private Sub Ok_Click()
    Sheets("subs").Columns("o:o").Hidden = Not (gouda.Value)
    Sheets("subs").Columns("L:L").Hidden = Not (onions.Value)
    Sheets("subs").Columns("i:i").Hidden = Not (peppers.Value)
    Range("a1").Value = Abs(CInt(peppers.Value))
End Sub


Private Sub [COLOR=#0000ff]UserForm[/COLOR]_Activate()
    gouda.Value = Not (Sheets("subs").Columns("o:o").Hidden)
    onions.Value = Not (Sheets("subs").Columns("L:L").Hidden)
    peppers.Value = Not (Sheets("subs").Columns("i:i").Hidden)
End Sub

You just need to replace the word UserForm in method signature with the actual name of your userform.
Click to expand...

I understand that UserForm_Initialize() and UserForm_Activate() are for setting up values of checkboxes and whatnot else in a user form, but it isn't working. I have found I can go in and change the value by manually going into the properties of the checkboxes, however this leaves whatever I choose ("true" "false" or blank) as the defalt.

for am image: Imgur: The most awesome images on the Internet
step by step of what is happening: Excel issue step by step - Album on Imgur


I am using MS Office 15, not sure that matters.
 
Upvote 0
Whoops! My bad. :biggrin:
Leave the UserForm word as is. I forgot that is the standard method template and name will stay UserForm_Activate() regardless of what the actual name of the form is. The actual name is relevant when referring to userform as a control.

And a couple of questions :
1. aren't you getting an error on lines with Columns("L:d" And "h:h" And "m:m" And "p:p").Hidden? Because this line is mixing string values and boolean operator.
2. For setting checkboxes sub1 and sub2, do you want to check if the checkboxes under them are enabled or checked? Because I don't see any code to enable/disable them.

So fixing my userform name mistake, and changing code for above two questions:
Code: 
Private Sub Ok_Click()    With Sheets("subs")
        Columns("o:o").Hidden = Not (gouda.Value)
        Columns("L:L").Hidden = Not (onions.Value)
        Columns("i:i").Hidden = Not (peppers.Value)
        Columns("f:f").Hidden = Not (swiss.Value)
        Columns("d:j").Hidden = Not (sub1.Value)
        Columns("L:r").Hidden = Not (sub2.Value)
        [COLOR=#0000ff]Range("d:d," & "h:h," & "m:m, " & "p:p").EntireColumn.Hidden [/COLOR]= Not (meats.Value)
    End With
        
    [COLOR=#0000ff]sub1.Value = peppers.Value & swiss.Value[/COLOR]
    [COLOR=#0000ff]sub1.Value = gouda.Value & onions.Value[/COLOR]
End Sub

Private Sub UserForm_Initialize()
    With Sheets("subs")
        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
        sub1.Value = Not .Columns("d:j").Hidden
        sub2.Value = Not .Columns("L:r").Hidden
        meats.Value = Not [COLOR=#0000ff].Range("d:d," & "h:h," & "m:m, " & "p:p").EntireColumn.Hidden[/COLOR]
    End With
End Sub

Code updates highlighted in blue.
 
Last edited:
Upvote 0
And then I realize that I used a wrong operator myself when restructuring the code. :LOL:

Code: 
    sub1.Value = peppers.Value [COLOR=#0000ff]And [/COLOR]swiss.Value
    sub2.Value = gouda.Value [COLOR=#0000ff]And [/COLOR]onions.Value
 
Last edited:
Upvote 0
V_Malkoti, once I updated so the userform_initialize didn't uses the userform name, the initialize stuff started working. So my initial problem is solved!

I am still having some issues with getting everything working all at once. In the code below, under Update_click(), I cannot get all of the parts to work at the same time. I think this is a sequencing issue, but I am not sure how to fix it. Also, under Userform_initialize(), I can't get the multiple columns being hidden to indicate a single checkbox.

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

    With Sheets("subs")
        'Hides the indicated column based on single ingredient checkboxes
        Columns("o:o").Hidden = Not (gouda.Value)
        Columns("L:L").Hidden = Not (onions.Value)
        Columns("i:i").Hidden = Not (peppers.Value)
        Columns("f:f").Hidden = Not (swiss.Value)
        
        'Hides all the columns associated with sub1
        Columns("d:d").Hidden = Not (Sub1.Value)
        Columns("e:e").Hidden = Not (Sub1.Value)
        Columns("f:f").Hidden = Not (Sub1.Value)
        Columns("g:g").Hidden = Not (Sub1.Value)
        Columns("h:h").Hidden = Not (Sub1.Value)
        Columns("i:i").Hidden = Not (Sub1.Value)
        Columns("j:j").Hidden = Not (Sub1.Value)
        
        'Hides all the columns associated with sub2
        Columns("L:L").Hidden = Not (sub2.Value)
        Columns("m:m").Hidden = Not (sub2.Value)
        Columns("n:n").Hidden = Not (sub2.Value)
        Columns("o:o").Hidden = Not (sub2.Value)
        Columns("p:p").Hidden = Not (sub2.Value)
        Columns("q:q").Hidden = Not (sub2.Value)
        Columns("r:r").Hidden = Not (sub2.Value)
        
        'hides all the columns that meats are in
        Columns("d:d").Hidden = Not (Meats.Value)
        Columns("h:h").Hidden = Not (Meats.Value)
        Columns("m:m").Hidden = Not (Meats.Value)
        Columns("p:p").Hidden = Not (Meats.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 = .Columns("d:d").Show And .Columns("h:h").Show And .Columns("m:m").Show And .Columns("p:p").Show
        
    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
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
488
kevin9999
K
O
  • Solved
ActiveX checkbox that changes color
2
Replies
15
Views
271
John_w
J
K0st4din
  • Solved
Macro hide inconsistent selected columns in selected worksheets
Replies
6
Views
147
Alex Blakenburg
A
N
  • Question
ActiveX Checkbox fails after sheetname change
Replies
4
Views
156
bobsan42
bobsan42
G
  • Solved
Macro - Columns Are not Hidding / unhiding correctly
Replies
4
Views
157
GarethCW
G

Forum statistics

Threads
1,215,331
Messages
6,124,311
Members
449,152
Latest member
PressEscape

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top