Option buttons in protected sheet get unselected

[MukulPurohit]

Hello Team,
I have created one group box and added two Option Buttons (form Control) inside it. And I have Linked the option buttons to cell $I$6.
I want to show 2nd option button as selected and so I set the value off $I$6 to 2. (This makes the second option box selected)
Then I mark Cell $I$6 as locked as I do not want user to be able to change the option selection.

When I mark the sheet as protected and I click on 1st Option Button I get an expected error message -

But when I click OK on this both the option boxes get unselected and the linked cell value is still showing 2.
Expected behavior is that the second option should still be shown as selected ... but it is not !!!

Can you please help me with this?
Regards
Mukul

[#2]

@MukulPurohit Unprotect the sheet, right-click the cell the option buttons are attached to and press control+1. Click the Protection tab and uncheck locked. Now protect the sheet again.

[#3]

@JKPieterse ,

Thank you for your reply ... it seems it still does not work..

I tried marking all the cells in the excel as Unlocked except for linked cell.( I do not want user to be able to change the option selection.).. I still observe the same behavior!!!

Regards

Mukul

[#4]

@MukulPurohit If you do not want the users to change the option, why are they there? I suggest to "just" use a data validation drop-down in a cell with the two choices that are currently shown on the option buttons. For that protection works as expected.

[#5]

@JKPieterse 

It is our business requirement. Basically in our case, we are creating a application form which user partially fills online and then we give them an excel to fill it offline.

This Excel will contain some prefilled data which user has filled online and which will not be editable in the excel. For Example - certain using option buttons like Gender - Male, Female, Transgender. (These fields are shown using option buttons in online form).

We want to make sure these prefilled fields look as similar as online form and thus we want to render option buttons as well which are pre selected and non editable.

Also we can not use macros as we want to keep this Excel as static as possible.

So is there a way we can achieve this with option buttons ? As changing them to list / dropdown will give different UI look and feel

[#6]

@MukulPurohit One possible workaround to achieve what you're asking for is to insert a text box overtop of the group box and option buttons, then format the text box with "No fill" and "No line". When protecting the worksheet, make sure the option to "Edit objects" is unchecked, which will make the text box, as well as the objects beneath it, inaccessible to the user.

Text Box Overlay

Note: this can also be done by inserting a shape (rectangle), but you would need to format the shape with "Solid fill" and 100% Transparency.