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..

 If CheckBox313.Value = True Then
    CheckBox314.Value = True
    CheckBox314.Enabled = False
    CheckBox67.Value = True
    CheckBox67.Enabled = False
    CheckBox320.Value = True
    CheckBox320.Enabled = False

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.

Sheets("sheet1").Shapes("Check Box 1").ControlFormat.Enabled = True

(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

Sheets("sheet1").Shapes(controlName).ControlFormat.someProperty
or
Sheets("sheet1").Shapes(controlName).OLEFormat.Object.someProperty

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

With Sheets("sheet1").Shapes("Check Box 1")
    .Visible = Not (.Visible)
End With

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.

 
If Sheets("Calculator").Shapes("Check Box 373") = True Then
Sheets("Calculator").Shapes("Check Box 303").Value = True
Sheets("Calculator").Shapes("Check Box 303").ControlFormat.Enabled = False

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.

With Sheets("Calculator")
   If .Shapes("Check Box 373").ControlFormat.Value = xlOn Then
       .Shapes("Check Box 303").ControlFormat.Enabled = False
       .Shapes("Check Box 303").ControlFormat.Value = xlOn
   Else
       .Shapes("Check Box 303").ControlFormat.Enabled = True 
   End If
End With

**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

With Sheets("Calculator").Shapes(
   If "Check Box 373").ControlFormat.Value = xlOn Then
       "Check Box 303").ControlFormat.Enabled = False
       "Check Box 303").ControlFormat.Value = xlOn
   Else
       "Check Box 303").ControlFormat.Enabled = True 
   End If
End With

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.

Form Checkboxes

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Form Checkboxes

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions