"Unable to set visible propery" error with checkbox collection

[Jamesm601]

Hi All.

I had a months long project 100% finished, when I suddenly encountered "Run-time error '1004': Unable to set the Visible property of the CheckBoxes class."

This is occuring on the line:

ActiveSheet.CheckBoxes.Visible = False

This line of code has worked beautifully up until now. I can't imagine why it suddenly errors. I'm not using any kind of protection, and I can even set the visible property within a for loop:

for a = 1 to activesheet.checkboxes.count
   ActiveSheet.CheckBoxes(a).Visible = False
next

But this takes several seconds to hide all 62 checkboxes on my sheet, which is unmanagable in the project. My only clue is that I have assigned these checkboxes to custom collections, and have set the visible property from within those collections. Could that be the problem? I am totally lost, and will be back to square 1 if I can't get around this.

Any help would be GREATLY appreciated.

Thank you so much.

James M.

 

[PHV]

To speed up the process, have you tried this ?
Application.ScreenUpdating = False
For a = 1 To ActiveSheet.CheckBoxes.Count
ActiveSheet.CheckBoxes(a).Visible = False
Next
Application.ScreenUpdating = True

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Jamesm601]

Hi PH
Yes. I generally turn off screen updating on most macros, which does speed things up a lot. Unfortunately, this particular one still takes a long time to run. When I could hide all of the controls with one command, it took no time at all. It's the loop that takes forever.

Thanks for the help though.

James

 

[krinid]

James,
Just to confirm, you're saying that this 100% finished project was working perfectly for months, and then suddenly with no changes to the code one day had an error on the line you indicated?

Anything change in the program itself or in your environment (Office, Windows, etc) recently? I experienced something similar using the StrConv command - it worked fine in my finished project for months, then one day errored out on the StrConv command. In my case, it turned out to be an OS level language display setting dependency. I can't imagine the visibility of checkboxes having any OS level dependencies, but I guess I can't rule it out either.

In any case, what I'm getting at is that the problem isn't necessarily with the code, but rather with something outside the physical code.

 

[Jamesm601]

Thanks, Krinid

That's a good point, and something I'll definitely check out. Let me clarify my poorly worded post. The project was only completed yesterday, but I'd been working on it for months. The mishap with the .visible property happened while I was loading it up with live data, but not as a result of any code change. I'm thinking that must be the reason, though it doesn't make sense to me. Is there maybe a limit to how many checkboxes you can address collectively? After adding all the data, I had over 60, and that's when it started acting funny. Anyway, thanks for the input. Wish me luck.

James

 

[TonyJollans]

Hi Jamesm601,

I must be missing something here. Why does adding data affect the number of checkboxes?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Jamesm601]

Tony
The tool I'm creating is a project tracking list. Each project has two checkboxes. One to show a chart, and one to expand rows of text notes. I wrote all the macros in advance, but when I add a project, I add two checkboxes, and assign the predefined macros to them.

With that said, I think I just figured out the problem. I went back to an earlier saved version, and started adding checkboxes. It worked fine until there were more than 50. Then the visible property change failed. Apparently, there's some kind of built-in limitation. Wish I'd known that up front. Anybody know of a workaround?

Thanks.
James

 

[krinid]

Are those checkboxes Excel objects or ActiveX objects? I don't know if it makes a difference, but it's worth considering.