You are here:

Excel/Add Code for Unprotect Sheet then Protect Sheet to Spin Button and Check Box (form control)


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)

Activesheet.Unprotect Password:="dog"
Activesheet.Protect Password:="dog"
End Sub

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)

Sub spinclick()
Dim sp As Spinner
Set sp = ActiveSheet.Spinners(Application.Caller)
ActiveSheet.Unprotect Password:="dog"
ActiveSheet.Range("K6").Value = sp.Value
ActiveSheet.Protect Password:="dog"
End Sub

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.

Tom Ogilvy

---------- 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  
G10: =if(F10=True,"Home","")

That is just an example - I have no idea what you are trying to accomplish with your checkbox.  

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.