running macros on protected sheets

[baz0912]

Hi all,

Wondering if someone could help? I have this code that inserts a new form directly below the last one. See attached. However the sheet is protected which doesn't allow macros to work. I found this code that allows the macro to work. The code unprotects the sheet, runs the macro and protects it again. The problem I've got is that it displays the password in the code which is not what I want. I tried protecting the VBA code to stop users from viewing but it doesn't allow users to save work.

So I found another bit of code which should allow me to run macros:

Sheets(“Sheet1”).Unprotect userinterfaceonly:=True

...which I've put at the top of the macro 'insertdata' (in attached), and it is still coming up with an error.

Has anyone got any ideas on what is wrong with the code.

Many thanks
Harry

[royUK]

There's an example workbook here

http://www.excel-it.com/vba_examples.htm

[royUK]

You don't actually set the protection in your code. Take a look at the example & you will see that protection is set as the workbook is opened.

[baz0912]

Roy,

Thanks for this, I follow the example of setting a password for the code. The problem with doing this is that the user can not save the spreadsheet when the VBA project is password protected.

Either I need to prevent users from viewing the password in the code or replace the code (which enables me to run macros on a protected sheet) to exclude the password with something else.

I don't need to protect the sheet when opening as long as it is protected and users can edit and save the unlocked cells.

Thanks
Harry