Solved: Checkbox help

[mferrisi]

Is there a way to run a macro when a cehckbox is selected? Either directly from the box or when a value in a cell is changed as a result?

Thanks

[lucas]

created from the forms toolbar or the vb toolbar?

[lucas]

from a checkbox on the control toolbox(vb toolbar)
Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Run "GSVHide"
Else
Run "GSVUnHide"
End If
End Sub

[lucas]

Hi mferrisi,
did you understand the difference in the two types of checkboxes?

[mferrisi]

I initially created them this way. So, for instance, if chx10 is checked, I want another check box to appear, either one that becomes unhidden, newly created, or whatever.

For stu = 1 To 4
With Worksheets(1).Shapes.AddFormControl(xlCheckBox, 270, 300 + (addShapes) * 16, 100, 10)
.Name = "cbx" & InitialShapes + addShapes
.ControlFormat.LinkedCell = "U" & 21 + addShapes
End With


Is it possible to use something like this:


Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Run "GSVHide"
Else
Run "GSVUnHide"
End If
End Sub

Thank you for your help

[lucas]

Yes but it's going to get really complicated because the second code will not know the name of the new checkbox you have created.....

I posted a simple solution for your problem in an earlier thread where all you have to do is check col A(or any column you chose) to see if there is a checkmark and if so run your code....remember?

[Bob Phillips]

If he uses the code I gave him, that sets an object variable to the checkbox added, so he will know it via the variable.

[lucas]

And then he will have to have a sub for each? I mean they are not all going to run the same macro are they or what would be the point in adding more checkboxes.....I'm slighly confused on usage here.

I just don't like the idea of adding controls on the fly.....where does this idea come from...I also think adding lots of checkboxes makes the file larger for no good reason. If each checkmark has something to do with an action on a row then it is much simpler to use the checkmark.

[Bob Phillips]

I can't argue with that, it is not good practice IMO.

But the checkmark is a different solution, not better, not worse, just different, so if they want checkboxes I would add themn up-front and hide/unhide them as required.

[mferrisi]

Hi Lucas,

Thank you for your responses (and please bear with me). I'm still a little bit confused. If I have four checkboxes a b c and d, if I just click on b, a macro will run?

My apologies if you answered this already

[lucas]

I have attached a simple example using the code we have been using to hide columns as an example.....check it out and post your specific questions if you don't understand and we will go from there.....don't give up...

[mferrisi]

Perfect. Thank you for all of your help.

[lucas]

It's a little confusing if you don't know that there are 2 types of checkboxes that you can add to a sheet.....if your fixed up don't forget to mark your thread solved. You can always post questions back later if something comes up.

I am attaching a sheet to delete rows with a checkMARK in column a for your review also.......just another idea as Bob points out.

[mferrisi]

Thanks. Two quick questions: Where do I click to label the thread solved,

2. What would be the line of code to determine if a checkbox is True or False?

[lucas]

1 use thread tools at the top of the page to mark your thread solved.
2 Look in the code module for sheet1 to see the code for whether it's true or false.....from post #11

to view the code for a sheet you can right click on its tab and select "view code"