Unlock/lock form objects with vba

Question

Hello,

I have an excel worksheet with 2 comboboxes, and 3 scrollbars. I want to protect all the sheet except this form objects (and one cell). When i try to protect the sheet, i can't use the scrollbars and comboboxes. How can i unlock them, keepin the protection for the rest of the sheet with vba ? I tried to unlock the cells linked to the form objects but it still doesn't work.

Thank u

AB

Answer 1

What about Menu option Data --> Allow Users to Edit Ranges?

Answer 2

Why not put the cells that need to be modified on a very hidden worksheet.

Create a named range e.g. "InputCell" in a separate worksheet e.g. "SheetWithInputCell". Set the cell link of the combobox to the named range using =InputCell.

Then set the worksheet to VeryHidden. VeryHidden means that users can't right-click on the sheet tabs and unhide it.

To set the sheet to very hidden, go to the VBA IDE (Alt+F11) and look at the Properties window. If you can't see the Properties window, select View > Properties Window.

In the Project Explorer window (View > Project Explorer), select the worksheet to hide and set the Visible property to xlSheetVeryHidden.

Then you can leave the hidden sheet unprotected and lock the sheet with the controls.