protect sheet but allow vlookup

paulinenj

New Member
Joined
Oct 2, 2006
Messages
25
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? :confused:
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
One thing you can do it to unprotect the sheet when you need to, and then protect it.

For example:
Code:
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

New Member
Joined
Oct 2, 2006
Messages
25
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

Board Regular
Joined
Aug 17, 2005
Messages
151
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
 

Forum statistics

Threads
1,136,272
Messages
5,674,749
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top