protect sheet but allow vlookup

[paulinenj]

Basically I have a log in sheet that will bring up the user's report. The report refers to another sheet (referencesheet) that looks up data from 3 other sheets. The 3 sheets of data are protected and hidden, but if I protect the referencesheet it will not look up new values which are selected from A) the username and B) from drop down lists in the report sheet.

I need all of the sheets to be as protected/hidden as possible, but I need the reference sheet to be able to change values....I would also like to protect the report sheet as much as possible (ie, the buttons, macros attached to buttons, etc.).

ANY suggestions?

 

[tmcfadden]

One thing you can do it to unprotect the sheet when you need to, and then protect it.

For example:

Worksheets(1).Unprotect
Worksheets(1).Range("A1")="Hello World"
Worksheets(1).Protect

If you use a password, you can put it in there too. The problem is that anyone can look at that in VBA.

-Tim

 

[paulinenj]

ah, thanks!

That's the problem I am facing now. Most of the passwords I am using right now are on protected sheets and I am just referring to the name of the cell (ie, password) in the code. But, I would like to protect the code. Do you know how to do this?

 

[tmcfadden]

Just found it ( never actually looked for it before ).

If you open up the VBA editor, you can right click the VBAProject for your workbook and select 'VBAProject Properties...' from the menu.

If you go to the 'Protection' tab, you can lock the project and enter a password.

-Tim

 

[paulinenj]

awesome! thank you!