Form Checkboxes

**Lonergan** · 10-23-2007, 04:47 PM

Hi. So I have a problem. I have a bunch of checkboxes (forms, not activex) that I would like to put conditions on. I.e if a particular box is checked, then 3 others are automatically grayed out and uncheckable. I think it would look something like this..

Please Login or Register  to view this content.

etc...

But... I always end up with a run time error that says I need an object. What does this mean exactly? Is it a problem with declarations? I should probably know this but I'm a FORTRAN guy winging VBA.

Thanks.

**mikerickson** · 10-23-2007, 07:44 PM

If Check Box 1 is on Sheet1, this is the syntax for changing its .Enabled property.

Please Login or Register  to view this content.

(The capitalization and spaces in the shape name are important.)

Excel thinks that Controls from the Forms menu are Shapes. Most of their properties are accessed via

Please Login or Register  to view this content.

Looking at the Object Browser under ControlFormat or OLEObject will give you a list of which properties go with which format.

About your desire to control the Enabled property of some checkboxes by checking another.
1) You need to write a macro that toggles the Enabled properties you are interested in. Then right click on the controling check-box and assign that macro to the controling check-box.

2) .Enabled = False does not visibly grey Forms check-boxes. You might consider toggleing the .Visible property instead.
The .Visible property is accesed with this syntax

Please Login or Register  to view this content.

As I recall, the With...End With construct was not avaliable in Fortran IV, but that may have changed. Working in VB, it is very useful.

I hope this helps.

**Lonergan** · 10-24-2007, 09:44 AM

That's really helpful actually. I hadn't realized that these types of checkboxes were listed in the shapes family. So now that I can toggle their enable/disable conditions; i still need to write the code.
I assume it's something along the lines of : (my sheet name is calculator; Check Box 373 will be in charge of disabling others such as 303.

Please Login or Register  to view this content.

Should there not be a .Value mentioned in here somewhere? I'm having trouble with a Run-time Error 438: Object doesn't support this property or method.

Any thoughts?

**mikerickson** · 10-24-2007, 10:38 AM

For Forms checkboxes, one checks if their value is xlOn or xlOff.

Please Login or Register  to view this content.

**Lonergan** · 10-24-2007, 10:45 AM

That's awesome. Thank you so much. out of curiosity. Is the idea of With Sheets () to state in the beginning that you are only interested in that particular sheet? Which is why you can then say .Shapes? Also, what does x10n mean exactly? True won't work? That would certainly solve many of my problems.

Thanks again.

BL

**mikerickson** · 10-25-2007, 10:42 AM

The with question: it sounds like you are asking if

Please Login or Register  to view this content.

will work. It won't.
.Shapes(
is not a property of a Sheet object.

It's not x10n, it's XLON. It's a Long constant = 1. Its counter part is xlOff, which equals -4146.

Big Rick · 10-25-2007, 10:53 AM

My 2 cents.
You should be able to try this as is, just need a UserForm with 4 check boxes.

Please Login or Register  to view this content.

**Lonergan** · 10-26-2007, 09:40 AM

It's working like a charm; however I've run into another issue which is probably easily solved but I find myself struggling with syntax once again. I would like a button that makes any of the invisible boxes visible again all at once. Like a reset button.

The obvious choice is to write:

Please Login or Register  to view this content.

... and so on. But... i have many many checkboxes and this would be ugly.

Anybody know of a way to do this for all checkboxes without having to list them all? Or at least all in one line?

THX...

**mikerickson** · 10-26-2007, 10:42 AM

This will make all shapes on the active sheet visible.

Please Login or Register  to view this content.

If you want to restrict the action to only forms checkboxes:

Please Login or Register  to view this content.

If you want only some of the checkboxes visible:

Please Login or Register  to view this content.

**Lonergan** · 10-29-2007, 10:23 AM

So I have a bunch of checkboxes and a drop down. When the drop down is a certain selection, I would like to make some of the checkboxes dissapear (Visible = False). This requires a macro to run each time a new selection is chosen in the dropdown. not sure how to do this?

Is there a function that i can use that will run this macro when a new choice is made in the dropdown?

Any help would be greatly appreciated.

Thanks.

**mikerickson** · 10-29-2007, 01:49 PM

Assuming that the ComboBox is from the forms menu, write a macro and assign that macro to the ComboBox.

If its an ActiveX ComboBox from the Toolbox, write a ComboBox_Change routine.

**Lonergan** · 10-29-2007, 01:57 PM

Hey thanks for the reply... But how do I assign a Macro to a drop down? It's not a ComboBox.. it's a Data Validation List. Does that complicate things? I think the right direction is the Worksheet_Change function but I'm having problems with the syntax

**mikerickson** · 10-29-2007, 03:27 PM

If you want the macro to trigger when one of the critical cells is changed, this syntax works. If you want the user to have the option to change validation and have that new validation location trigger your routine, that is a much bigger fish to fry.

Please Login or Register  to view this content.

**Lonergan** · 10-29-2007, 03:33 PM

Thanks... this is exactly what I'm trying to do. Could you clarify this part?

If 0 < InStr(criticalCells, Target.Address) Then

What does that mean exactly? After this line I would like to put some conditions based on what appears in the drop down. My drop down is in C4...

If C4 = "25-99" Then
.Shapes("Check Box 1").Visible = Not (.Visible)
End If

Will that work? or will I have to reference the cell in some other way?

**mikerickson** · 10-29-2007, 06:42 PM

InStr(oneString,subString) returns the postion of subString within oneString and 0 if subString is not contained in oneString. In the _Change routine, Target is the range that has changed to trigger the event.

Please Login or Register  to view this content.

As to testing, I'm guessing you want to test if 25<= C4<=99. This will toggle the .Visible property of Check Box 1 if it is in that range.

Please Login or Register  to view this content.

**Lonergan** · 10-31-2007, 10:13 AM

Hi. So I have a worksheet that a user will access, and two others that contain calculations and data that i don't want them to see. I know how to hide them so they are not visible; but they are still accessible using ctrl+pgdn. Is there a VBA code i can use so that they can't see them, ever, no matter what?