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