Lock cells in Excel - activity only allowed using Userform

[Pierrejza]

Hi all,
I have created a userform with multiple Comboboxes and textboxes that captures data onto a spreadsheet.
I would like to Lock the spreadsheet becuase people manually bypassing the userform.

I know where to Lock all the fields in the spreadsheet by Protecting it, however the problem that I am facing with is if I do this then the Userform can also not update the spreadsheet.

I hope this make sense.

Please help.

Thank you

[OllieB]

An elegant solution would be to:

a. protect the specific worksheet against manual updates with a password;

b. add the following code to the workbook_open event routine

Please Login or Register  to view this content.

the above line of code will keep your worksheet protected but will allow all VBA code to update the worksheet as if it was not locked

c. protect your VBA code from being viewed (project properties)

[Pierrejza]

Hi OllieB,
Thank you for the post.
I have done option a but that also blocks the VB from entrering anything

I have done option b but got an error

I am using office 2010.

Compile error.PNG

Sorry..Still a newby at this ...Thank you for the help.

[OllieB]

Ji Pierrejza,

You cannot just put the statement on its own in a module. It needs to be part of a routine, see below

Please Login or Register  to view this content.

You will also need to change the "password" with the actual password value (case sensitive) and change "Sheet1" into the actual name of the worksheet. Finally you will need to close and re-open the workbook for this statement to be executed.