How to suppress ActiveX checkboxes based on a macro

betezadi

New Member
Joined
Jan 30, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

severynm

Board Regular
Joined
Jan 8, 2021
Messages
56
Office Version
  1. 365
Platform
  1. Windows
Welcome! Set the .Visible property of the checkboxes to False. To have them show again, set that same property to True.
 

betezadi

New Member
Joined
Jan 30, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 8, 2021
Messages
56
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 30, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 8, 2021
Messages
56
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

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
Top