VBA and Protect Worksheet Dilemma

[Brian]

Hi,

I am a novice at VBA and have struck a little problem. I have made a table
in Excel that compares various Insurance Companies covers. I have checkboxes
for the user to select which companies he/she wishes to compare and then a
Compare button which takes the user to the worksheet with the comparisons on
it showing only the chosen insurers.

Here is my problem: I cannot protect the worksheets without getting a
VBAerror. VBA cannot show columns that are hidden, or hide columns that are
shown if the worksheet is protected. Can anyone assist me in how to overcome
this issue, please? It is imperative that the worksheets are locked to
prevent anything being changed by the user, intentionally or otherwise.

Best regards,
Brian.

 

[hall.jeff]

You need to unprotect the worksheet and then protect it back

If you need to unprotect the whole workbook then its

ThisWorkbook.unprotect Password

If it's just the sheet then

ThisWorkbook.Worksheets("Sheet1").Unprotect Password

For ease of use, I would recommend setting up a global constant that
contains the password

Then, to protect it, you'll need to password protect your VBA code:
Tools->VBA Project Properties

 

[Brian]

Hi Jeff,

thank you so much for not only responding to me as quickly as you did, but
for telling me exactly what I needed to know! You have no idea how much I
appreciate it.

Best regards,
Brian.

 

[siekrun]

I have a workbook im working on. Within the work book, there will be
10 worksheets and with it 10 users. I am trying to password protect
each work sheet with specific passwords. I have the format locked, I
am trying to password protect the unlocked boxes only.

I am thinking there is nothing excel can do to help. But I am have
been working on this for 1 week now and have not figured it out. Can
anyone help?

 

[Gord Dibben]

Not enough detail to get a good idea of what you want.

One sheet per user but all sheets visible?

One sheet per user and all sheets but user's is hidden?

Each user knows only his password to unprotect?

What do you mean by "the format locked"?

Why would you want to protect the "unlocked boxes only"?

I would think you would want to protect the sheet but allow users to enter data
in the unlocked cells.

Check out this thread to see if you get some ideas then post back.

http://tinyurl.com/ysj6dw


Gord Dibben MS Excel MVP