checkbox change event in form

Hi,

I have 3 checkboxes added to a form. I am using excel 2003. The problem is, I am not sure what is triggered when a user changes a checkbox in the form. I see the form itself has events like click, dbclick, error, etc. When I tested out form1_click, it actually is not triggered if i select the checkbox. There is no form1_change event either.

When i tried creating a checkbox1_change or checkbox1_click event, it does not exist.

What can i use to detect a change in those checkboxes? Thanks.

Hello bluegirl01,

There are 2 types of controls for the Worksheet. Those from the Forms Toolbar and those from the Control Toolbox (sometimes called ActiveX controls). Yours is the former (Forms type). The Forms type only respond when clicked and execute the macro code that is attached to or called by the object.

Sincerely,
Leith Ross

I see...

originally I had the checkboxes as a separate control that I wanted to make read only when the sheet was protected. The only property though that was available was the enabled one, but that changed the colour of the checkboxes. Ok, so now I have just reverted back to my old non form wrapped checkboxes, and just used macros to change the colours.

Thanks for the help.

nevermind.. i thought that would work, but it actually doesnt.

Hello Bluegirl01,

To better answer your questions, I think it would help if you posted your code or the workbook.

Sincerely,
Leith Ross

Hi Leith,

I want a worksheet with checkboxes that become read only when the sheet is protected. The user who is using the worksheet may or may not have macros enabled. The user is only ever using the sheet in protected form. These checkboxes specify to the user which parts of the sheet has to be filled in, so I do not want them to look greyed out, which is what occurs when the checkboxes become disabled. Also when a checkbox is checked, it changes the colours of the sections the user has to fill out. This allows who ever is using the sheet in unprotected form to generate a few different kinds of forms based on one template.

This is what I have tried:
1) Using the control toolbox, I added my checkboxes, and linked them to a cell in the sheet, and also set locked to true. Now I use Worksheet_SelectionChange and Worksheet_Activate to check if the sheet is protected and if so, disable the checkboxes.

Example of my code:

Please Login or Register  to view this content.

The thing I dont like about this method is that the checkboxes become grayed out when it is disabled. There is no such ActiveSheet.Directions.ReadOnly property that I can use instead. Since the users of the sheets may not have macros enabled, I cannot leave the checkboxes enabled, and use Directions_Change to "unchange" it so it emulates a read only behaviour.

As mentioned before, I have tried changing the ActiveSheet.Directions.Backcolor and ActiveSheet.Directions.Forecolor but having the checkboxes disabled overwrites it.

2) So the next thing I tried was instead use a form and add checkboxes to it. I read elsewhere that you can disable the form instead of the checkboxes, and now the checkboxes becomes read only and is not grayed out. The problem with is that when I use the sheet in unprotected mode, I cannot get to the equivalent of Directions_Change event, so any actions that should occur when the checkboxes are modifed are not happening. Instead the events that are fired are associated with the form itself, but simply clicking on the checkboxes inside the form does not seem to fire those form events.

Anyways this is a minor issue, and I am done dealing with it. I shall just set the checkboxes to disabled.

Hello bluegirl01,

The check boxes from the Control Toolbox have a Locked property. Setting this property to true will prevent the user from changing the check box. The color of the box won't change like when its disabled. The locked property can be set to true or false by the checking the protection state of the worksheet. If you want an example, I'll create a sample workbook.

Sincerely,
Leith Ross

All my checkboxes are set to locked already.

what happens is this:
1) If user disableds macros, then it works fine.
2) If user enables macros, then the user gets prompt that it is read only BUT the checkbox still changes, and then macro in the change sub tries to execute.

I have written code in there to detect if the sheet is locked, but that is in the change sub. I know it needs to be in the click sub, but I am not sure how to cancel the change sub from being triggered.

for example, in Workbook_BeforeClose(Cancel As Boolean), I can just set cancel to false and it will stop.. but there is no BeforeChange for checkboxes.