Project One - Nearly done - Protection issues

[Dean Goodmen]

Well I am nearly finished with my first ever Excel spreadsheet
using VB! Thanks to all of you here who have helped me out sooo much!
I could have picked a much easier project but I would not learn as
much as I have already. I am also looking around for good books to
help me along further.

I have one last problem with “Project One” It adds and removes
data lines quite nicely, it all was working great, until…I wanted to
protect all the cells, rows and formatting from being messed up by a
user, so I protected the worksheet. The problem is now that my code is
not being allowed to Insert, delete, and reformat the cells as need.
It crashes with a “Run Time error 1004”

Any suggestions?

 

[Bob Phillips]

Unprotect before the inserts and deletes, do that, the protect again.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jake Marx]

Hi Dean,

You can set the UserInterfaceOnly argument to the Protect method to True
(works for Sheet-level protection only) via code. This will allow your code
to modify the sheet, but will protect it against changes by the user. This
setting only lasts as long as the workbook is open - when you reopen, it
will revert to being "fully-protected". If you want this behavior every
time the workbook opens, you could put the following line of code in the
Workbook_Open event subroutine:

Sheet1.Protect Password:="test", UserInterfaceOnly:=True

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Dean Goodmen]

Excellent! That is exactly what I need I expected the solution to
be more complex.. Thanks.

BTW I notice with that on and the sheet protected, a user can still
"View code", and then of course see the password...anyway to prevent
that?

 

[Bob Phillips]

Password protect the VBA project. Go to the VBE, select the project in the
explorer list, right-click and select Properties. There you can lock it and
set a password. Hardly fool[proof, but a discouragement.

--

HTH

RP
(remove nothere from the email address if mailing direct)