For To Loop to Increment ActiveX [EDIT - Now 'Form Control'] CheckBox Numbers

**Zedcars** · 01-02-2022, 01:55 PM

Hello,

I've set up 39 Active X checkboxes which, if the same row in B contains data it formats a strikethrough in A2:C39 if checked.

[EDIT]If it does not contain data then the checkbox hides.

Here is my code for just the first and last one:

Please Login or Register  to view this content.

Is there a way I can use a For To Loop through all the Checkbox numbers 1-39 so I don't have to type the above code out 39 times?

I searched for an answer and have given this a go myself but nothing I've tried works.

**Zedcars** · 01-02-2022, 02:27 PM

Sorry, I don't think I was quite clear enough in my initial post. The checkboxes should hide when there is no data in another cell in the same row. And my code above does what I wish, but I would like to streamline the code so I don't have to repeat the code for all the other rows in which I have checkboxes.

I've tried this code for the last sub, but it isn't hiding the checkbox in D5 say when I delete data from cell B5:

Please Login or Register  to view this content.

I have got further along though because it's no longer throwing up an error like my other attempts!

**Fluff13** · 01-02-2022, 02:38 PM

Try

Please Login or Register  to view this content.

**Zedcars** · 01-02-2022, 03:00 PM

Originally Posted by Fluff13

Try

Please Login or Register  to view this content.

Oh wow! That small change and now it works perfectly! Thank you very much.

I don't suppose there is any way I can do a similar For To Loop for the 39 Private Sub CheckBox1_Click() subs is there?

I tried:

Please Login or Register  to view this content.

but it said expected identifier at the first bracket. Maybe this just isn't possible to do for each object.

**Fluff13** · 01-02-2022, 03:03 PM

If you used Form Control checkboxes instead, you could assign the same macro to each of them

**Zedcars** · 01-02-2022, 03:13 PM

Originally Posted by Fluff13

If you used Form Control checkboxes instead, you could assign the same macro to each of them

Ah ok. Would I still be able to hide the checkboxes in the same way I've been doing? I think maybe I have to change the OLEObjects entry to Controls instead or something like that.

**Fluff13** · 01-02-2022, 03:23 PM

Yes you can do it like

Please Login or Register  to view this content.

**Zedcars** · 01-02-2022, 05:10 PM

Originally Posted by Fluff13

Yes you can do it like

Please Login or Register  to view this content.

Thank you, but that does not work for me. Not sure why. Shouldn't "Check Box" be "CheckBox"? Also, I've seen a similar question answered on Stack Overflow and they use "Me.Controls" instead of "Me.Shapes". Not sure if that makes any difference, but it doesn't work either way.

I tried this:

Please Login or Register  to view this content.

But it's still not working - the checkbox remains visible.

**Fluff13** · 01-02-2022, 05:13 PM

Shouldn't "Check Box" be "CheckBox"?

Not unless you have renamed the checkboxes. The default name for a Form Control checkbox is "Check Box"
Can you supply a workbook?

**Zedcars** · 01-02-2022, 06:03 PM

The forum attachment feature doesn't work for me. I will use my Dropbox instead - I hope that is ok:

https://www.dropbox.com/s/85hg30pxh5...s-06.xlsm?dl=0

I wasn't able to get the Strikethrough of A:C or the Hide working with the Form Controls.

I will have to return to ActiveX controls if I'm not able to get this working.

[Edit: It seems to have attached anyway, without me realising it!!! Haha]

**Zedcars** · 01-03-2022, 08:27 AM

Hello,

I have the ActiveX working just fine, but would rather not use it as I have to have a separate macro for each checkbox. Also, I've read ActiveX can cause many other problems.

So I've switched to the Form Control checkboxes (which look better anyway!)

I've got the strikethrough font format working now.

But hiding the Checkboxes when the adjacent cell in B is empty isn't working.

I don't know if anyone can help me solve this? It feels like I'm close to an answer, but so far it's stumped me!

I will attach an updated Excel file with just the barebones.

Many thanks.

**Fluff13** · 01-03-2022, 09:38 AM

This code

Please Login or Register  to view this content.

needs to go in the sheet module, not a regular module.

**Zedcars** · 01-03-2022, 09:55 AM

Originally Posted by Fluff13

This code

Please Login or Register  to view this content.

needs to go in the sheet module, not a regular module.

Oh you absolute beauty! What you said fleetingly crossed my mind but I thought I had done that. Clearly not. Rookie mistake.

Wasted 2 hours on this this morning and lots of time yesterday but at least I'm learning!

Best wishes to you.

**Fluff13** · 01-03-2022, 09:57 AM

Glad to help & thanks for the feedback.

For To Loop to Increment ActiveX [EDIT - Now 'Form Control'] CheckBox Numbers

LinkBack

Thread Tools

Rate This Thread

Display

For To Loop to Increment ActiveX [EDIT - Now 'Form Control'] CheckBox Numbers

Re: For To Loop to Increment ActiveX CheckBox Numbers

Re: For To Loop to Increment ActiveX CheckBox Numbers

Re: For To Loop to Increment ActiveX CheckBox Numbers

Re: For To Loop to Increment ActiveX CheckBox Numbers

Re: For To Loop to Increment ActiveX CheckBox Numbers

Re: For To Loop to Increment ActiveX CheckBox Numbers

Re: For To Loop to Increment ActiveX CheckBox Numbers

Re: For To Loop to Increment ActiveX CheckBox Numbers

Re: For To Loop to Increment ActiveX CheckBox Numbers

Re: For To Loop to Increment ActiveX CheckBox Numbers

Re: For To Loop to Increment ActiveX [EDIT - Now 'Form Control'] CheckBox Numbers

Re: For To Loop to Increment ActiveX [EDIT - Now 'Form Control'] CheckBox Numbers

Re: For To Loop to Increment ActiveX [EDIT - Now 'Form Control'] CheckBox Numbers

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Non UserForm - Checkbox Loop to add numbers and concatenate labels - Part B

[SOLVED] Non UserForm - Checkbox Loop to add numbers and concatenate labels

Help needed for ActiveX checkbox VBA

VBA to find the position of an Activex checkbox, copy the last checkbox and position it?

[SOLVED] How to see the ActiveX Checkbox is checked ?

[SOLVED] Loop through numbered ActiveX CheckBox

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions