PDA

View Full Version : Unlocking Options Buttons, Forms and Checkeboxes



PSL
01-06-2011, 10:46 AM
Greeting,

I am facing a very tedious problem that can be done manually but am hoping it could be done with the help of a macro.

I have created a survey with various Option Buttons, Checkboxes and have merged cells to create spaces where respondents can enter subjective information.

Now, before floating this survey I need to password protect it to ensure that the options are not fiddled with. While protecting the sheet I am intending to allow to respondents to be able to edit unlocked cells. Therefore I would be required to unlock the cell where all the checkboxes and option buttons are linked. And also the cells I have merged to give space for subjective answers.

Now this can be done manually ofcourse. But due to the size of the survey that would take hours to say the least.

Was hoping someone could help. Have attached a sample file.

Regards,

Bob Phillips
01-06-2011, 11:46 AM
Dim cb As CheckBox
Dim opt As OptionButton

For Each cb In ActiveSheet.CheckBoxes

With Range(cb.LinkedCell)

.Value = ""
.Locked = False
End With
Next cb
For Each opt In ActiveSheet.OptionButtons

With Range(opt.LinkedCell)

.Value = ""
.Locked = False
End With
Next opt

PSL
01-06-2011, 03:02 PM
Hey,

Thanks!

This works for the Checkboxes and OptionButtons. However it won't work for the grey boxes I have created to allow respondents to fill in subjective details.

I'm not quite able to understand how the ".locked" works. In my case, all the cells provided for users to fill are grey in color. Thus a simple Interior.colorindex gave me 15.

However I'm not able to get the If ActiveCell.Interior.ColourIndex = 15 Then
ActiveCell.Locked = False

Gives an error. Any suggestions as to how I can work around this?

Thanks and cheers

Bob Phillips
01-06-2011, 04:26 PM
We have to have a way of identifying the grey boxes. You could give them all defined names, and we could code for that.

PSL
01-07-2011, 12:47 PM
We have to have a way of identifying the grey boxes. You could give them all defined names, and we could code for that.

Yes, there is a way of identifying them. ColorIndex = 15
But not able to get the .Unlock property to work in this case. The code that would simply unlock all cells that have Interior.Colorindex = 15.

How to go about that?

Regards,

Bob Phillips
01-07-2011, 12:50 PM
No, that is no good, you would have to loop all cells and that would take forever.

PSL
01-07-2011, 01:12 PM
No, that is no good, you would have to loop all cells and that would take forever.

Hmmm. Any suggestions? I could name the range. Would have to do it separately for all the sheets.

Bob Phillips
01-07-2011, 02:16 PM
Yes you would, but it shouldn't be too onerous.