Excel/Add Code for Unprotect Sheet then Protect Sheet to Spin Button and Check Box (form control)
QUESTION: Hi Tom,
Thanks for your prompt reply.
It helped me.
Yes, you rightly pointed in the last reply that unprotecting and then protecting sheet helped me for "Button (form control)" but how can I do the same regarding spin button and check box? "but I don't see how that is going to help you use your spin button."
These two lines helped me in button (Form control)
Is there any other way to do so for Spin button and check box (form control)?
First, remove the link from your spinbutton and the cell where you want to display the number.
Then assign this macro to your spinbutton (assume a forms spinbutton, not an activeX control)
Dim sp As Spinner
Set sp = ActiveSheet.Spinners(Application.Caller)
ActiveSheet.Range("K6").Value = sp.Value
That worked for me. So now you have no link between the cell and the spinner and the macro updates the cell with the value of the spin button.
of course all changes should be made with the sheet unprotected, then protect the sheet when you are ready to test.
---------- FOLLOW-UP ----------
Yes I thought something like that. So the same is the way for check box, right?
I suppose you could use code like that, but for a checkbox, you are just putting true or false in a cell and generally you don't want to see the true or false because the checkbox will visibly display whether it is checked or not. I would just link the checkbox to the cell underneath the checkbox and have the checkbox cover that cell. Then your formulas can query the cell but it will not be visible to the user (the cell would be unlocked). While the user can navigate to the cell using keyboard keys, I would guess that would be less likely to happen.
example. Say I have a checkbox over cell F10 and if it is checked I want the word Home to appear in G10 otherwise G10 is blank. So I link the checkbox to F10 and in G10 I have a formula like
That is just an example - I have no idea what you are trying to accomplish with your checkbox.