Lock down but allow mod of link from radio button (solved)

[Samnuni]

Hi, I made a form for users to input data, but I wanted it lock except for the inputting part. Now in this sheet, I have used radio buttons that links to one of the cells but if I lock it down then the user can't modify it. If I allow the user to modify it, they can input whatever they want too and that is what I don't want. Can someone help me out? Thanks.

 

[erik.van.geit]

Samnuni,

I have used radio buttons that links to one of the cells but if I lock it down then the user can't modify it. If I allow the user to modify it, they can input whatever they want too and that is what I don't want

so you want to protect the sheet, but allow users to click the buttons
did you try to change the cellproperties of the linked cell(s) to "unlocked"

see menu Format / Cellproperties / Protection (last tab)

if it isn't this what ou were after please clarify
an example would help

kind regards,
Erik

 

[Samnuni]

Basically I want to lock the cell where it displays the result so that it can only be modified by the option buttons. The problem is that when I use protection on it, it won't change the result because it is controlled by the user. And if I allowed the user to change that cell, the user can input whatever he wants into that cell besides the options linked to it.

Book1
	A	B	C	D
2
3		Button 1	Result 1
4
5		Button 2	Result 2
6
7		Button 3	Result 3
8
9
10		Result:	Result 1
11
Sheet1

 

[erik.van.geit]

different solutions are possible
formulabased (recommended in this situation)
buttons linked to one cell (forms-toolbar-buttons would be the easiest)
INDEX or CHOOSE formula in "result"cell ( linked cell is A1)
=CHOOSE(A1,C3,C5,C7) according to your sheet
=INDEX(C3:C5,A1) if results in C3,C4,C5

VBA
solution1
unprotect sheet
set value in "result"cell
solution2
set protection of your sheets using "userinterfaconly" like this:
Sheets(1).Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
protect sheet

kind regards,
Erik

 

[Samnuni]

Thanks for the help, but I don't think the formulas will make it... Once I lock up the worksheet, the user cannot modify anything because the link cell is locked also. The reason why the link cell is important and I need to control the input is because that is the control for a formula. I want the user to only have three choices.

 

[erik.van.geit]

why don't you just unlock the linked cell ??
when proposing this solution, I made up the same situtation on a sheet and it works nicely - when you unlock the cell of course !

 

[Samnuni]

yes, I figured I needed that cell unlocked, but the user can change the information in there too if I do that...

 

[Andywiz]

You could always unlock the linked cell, but then apply data validation on that cell, so that the user cannot enter anything he/she wishes, only the values that would come from the radio buttons anyway....

Just a thought.

Andywiz

 

[Samnuni]

Cool, I didn't think about that, thanks.

 

[erik.van.geit]

you can put that cell within a hidden column or row
or under a shape (button, ...)
+
you could validate it to "impossible criterion"
(between 0.00001 and 0.00002)

this would be rather safe

if you still don't like this, you've received VBA solutions ...

best regards,
Erik