protecting workbook

[z0rpia]

Hi all,
I've got about 50 worksheets in a workbook and I'd like to protect them all.
Problem however is that I've got buttons in my worksheets that insert rows, delete rows.
I've also got a dynamic chart with listboxes that select data.
When I protect my worksheets all this is disabled.

Is there a way around this?

This is what I've done so far :
http://www.ozgrid.com/VBA/excel-work...rotect-all.htm

[z0rpia]

I've also tried allowing addition and deleting rows when I protect a single sheet, but I think because I have loops that go through cells my program crashes cause my code cant access the cells (I think).

[shg]

See the UserInterfaceOnly argument to the Protect method in Help.

[z0rpia]

Hi the following is my code but it doesn't work . I'm using excel Version 2003

Please Login or Register  to view this content.

[z0rpia]

Hi sorry, I've tried the code again and it worked. However by list boxes and chart dont seem to work?
Buttons work fine

[royUK]

There's an example workbook here

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

[z0rpia]

Yeah sorry, this code doesn't work too. Its the ***** list boxes that don't seem to work once I protect my worksheets. The buttons work fine though.

[royUK]

Not sure what your problem is, maybe you should attach the workbook.

[z0rpia]

HI,

Well I understand that you need my workbook to understand my problem better. However, I'm not very comfortable with publishing my workbook over the internet as it is work related.

Also I've uploaded another workbook on this site before but no-one ever replied to that. (No disrespect intended, as I've recieved heaps of helpful tips and solutions from many others.)

Can anyone please help me? This is just holding back my project.

[royUK]

Remove any sensitive data before attaching

[z0rpia]

G'day royUk, I'm attaching my workbook to this message. HOpe you can help me. Thanks matey.

[cart0250]

Not sure if our situation is same or not, but I have a workbook with many macros that I need to protect against user structural changes.

What I did was unprotect the workbook in the code at the beginning of each macro, allowing it to run, and then protect it again at the end of each macro. As far as user is concerned, sheet is always protected, but macros also run.

[royUK]

I've added the code to protect with userinterface in your protection module. It is called when the workbook opens. Seems to work fine to me

[z0rpia]

Hi royUk sorry mate, still doesnt work . Everytime I try to pick a date from the listboxes i get an error message saying "The workbook is protected. Please unprotect and then try to change" something along those lines. If you want I can run it and send you the exact error message. Please I really need to get this right to finish off my project.

[z0rpia]

"The cell or chart you are trying to change is protected and therefore read only.
TO modify a cell or chart, first remove protection ...etc"

THats the error message I get.
I was trying a new approach where the unprotect module is called when the listbox dropdown is clicked. And then the listbox values are chosen by the user. After which the listbox_change module calles the protect module to put it back in protection.

However I couldnt realy get my head around it. Any help will be greatly appreciated.