How to suppress ActiveX checkboxes based on a macro

[betezadi]

I have a macro that I'd like to use to reset a form to original conditions

- All rows/columns shown
- Cell updated with today's date
- Background colors reset
- Clears contents from multiple ranges

I'd like for this same macro to hide lines and the checkboxes within those lines. This section of code is currently suppressed with a " ' " since I can't seem to get past a different hurdle.

Each potential line has 1-3 checkboxes that I'd like to suppress from being shown when the form is reset.

How do I hide the checkboxes within these rows before I hide the rows using Rows("x:y").Hidden=True ?

Also, when I select a different macro to show the rows I hide, how do I get these checkboxes to show-up again?

 

[severynm]

Welcome! Set the .Visible property of the checkboxes to False. To have them show again, set that same property to True.

 

[betezadi]

Welcome! Set the .Visible property of the checkboxes to False. To have them show again, set that same property to True.

Hi Severynm! Thanks! Excited to learn a lot more here and appreciate your feedback!

How do I set checkbox visibility within VBA? Not even sure what the checkboxes are even called now. I deleted the text to the right of the checkbox and have the checkbox linked to the cell. Did I change the name of the checkbox to the cell that's linked to when I did that?

Also, am I doing this wrong by putting code in a Module vs a Sheet ? Saw this in another post and it's similar to what I want to kind of do... hide activex checkboxes conditionally

Here's an example of what I want to do:

W17 linked checkbox visible = No
Y17 linked checkbox visible = No
AA17 linked checkbox visible = No
Rows("17:18").Hidden = True


---

Different Macro

Rows("17:18").Hidden = False
W17 linked checkbox visible = Yes
Y17 linked checkbox visible = Yes
AA17 linked checkbox visible = Yes

 

[severynm]

Are you using ActiveX checkboxes or Form Control checkboxes? (a good writeup on the differences between the two) Based on your terminology of "linking to cells", I suspect Form Controls. Form Controls are a little easier to use up front but if you want any more in depth customization or easier interaction within VBA, ActiveX forms are the way to go.

Nevertheless we can get it done with Form Controls too. By default, Form Control check boxes are named "Check Box 1" "Check Box 2" etc. as you add them into your workbook. To better keep track of things, the name can be changed from the Name box to the left of the formula bar.

Edit: The code does not necessarily need to be in the same worksheet, but I'd recommend doing that anyway. The VBA is ThisWorkbook.Worksheets("worksheetName").Shapes("checkBoxName").Visible = False.

 

[betezadi]

Shooooot... I made form controls.

Is there a way to hide more than one box at a time within the one line of code you shared? I was able to hide the checkboxes using the script you provided, but each box has it's own line at this time.

Also, appreciate the link you sent me. I'm going to read more of it soon. I suspect I don't need ActiveX checkboxes for the purposes of what I am doing, but out of curiosity... Is it easy to convert Form checkboxes to ActiveX checkboxes without losing linkage to conditionally formatted and linked cells?

For anyone else reading into this... I figured out my error with severynm's input. I initially made the form with ActiveX Controls checkboxes but got tired of creating new buttons so I found a VBA script to quickly generate checkboxes and now suspect the generated checkboxes are not ActiveX Control but are instead Form Control. Wish I could update the title of the post. Sorry for the confusion :[.

 

[severynm]

Is there a way to hide more than one box at a time within the one line of code you shared? I was able to hide the checkboxes using the script you provided, but each box has it's own line at this time.

Yep. The controls are basically treated as shapes (the above link goes into this), so you just need a loop to iterate over each shape in the worksheet. Interestingly, this works for ActiveX objects too, which I just learned.

Dim shp As Shape
For Each shp In ActiveSheet.Shapes
    shp.Visible = True
Next

Is it easy to convert Form checkboxes to ActiveX checkboxes without losing linkage to conditionally formatted and linked cells?

Unless there's some method I'm unaware of, no.

ActiveX controls (checkboxes, etc) aren't "linked" in the same way - any reading from or writing to cells needs to be done in VBA. Personally I almost exclusively use ActiveX controls, so I can't comment too much in depth about how Form controls work.