Pull data from password protected sheet without password

Zanatos1986 · Jul 12, 2010

Hey guys,
So I have a sheet with sensitive information on it that cannot be viewed by everyone at our company, however this sheet is also used to update alot of things within the company on a daily basis.
I currently have two excel spreadsheets, one with all the important data that is password protected and another that pulls data from this sheet that everyone can access and update. Whenever I open the sheet everyone can access it updates by pulling data from the password protected sheet. When you do this it asks for the password. Is there anyway to get around this (autogenerate the password?) We can't turn off the password and we also can't give it out (otherwise it's pointless).
Any suggestions?

Thanks!

HalfAce · Jul 12, 2010

Zanatos1986,
There should be no reason you can't pull data from a protected sheet to another, using formulas or with vba, without having to address the password.

If you're doing something that requires un-protecting the sheet with vba then try something like:

Code:

Sheets("YourSheetName").Unprotect "YourPassword"

and then at the end of the code...

Code:

Sheets("YourSheetName").Protect "YourPassword"

Hope it helps. (If not then I don't understand...)

markadon · Jul 12, 2010

I use the following:

Sub ProtectSheet(pwsSheet As Worksheet)
' Turn Protection back on.
If (Not (pwsSheet.ProtectContents)) Then
pwsSheet.Protect _
Password:=MyPassword, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowFiltering:=True
End If
End Sub ' ProtectSheet

Sub UnprotectSheet(pwsSheet As Worksheet)
If (pwsSheet.ProtectContents) Then
pwsSheet.Unprotect Password:=MyPassword
End If
End Sub ' UnprotectSheet

HalfAce · Jul 13, 2010

For that to work I believe you'll need quotes (" ") around "MyPassword".
I can see the convenience of unprotecting the sheet without having to enter the password but you basically remove any (already lightweight) security offered by the password to begin with if you're using the code to unprotect so people can edit, then again to reprotect.

I still wonder why you wouldn't be able to get your data without unprotecting.

Is your code not working? Where is it hung up when you hit debug?

markadon · Jul 13, 2010

Half:

Three points. First, MyPassword is a string constant that I store in a separate Module that holds all Global variables. Second, I don't allow the users to unprotect the sheet for editing. I use a form that they enter data on and then I unprotect the sheet, make the changes, and reprotect the sheet without the user touching the actual worksheet. Third, I also password protect the VBA code so the user is unable to find out the password without some heavy-duty knowledge.

Hope that clears things up.
--Doug.

Andrew Poulsom · Jul 13, 2010

I imagine it's the workbook that's protected rather than the worksheet. This may help you:

http://www.mrexcel.com/forum/showthread.php?t=168849

Pull data from password protected sheet without password

Zanatos1986

New Member

Excel Facts

HalfAce

MrExcel MVP

markadon

Board Regular

HalfAce

MrExcel MVP

markadon

Board Regular

Andrew Poulsom

MrExcel MVP

Similar threads

Forum statistics

Share this page

Pull data from password protected sheet without password

Zanatos1986

New Member

Excel Facts

HalfAce

MrExcel MVP

markadon

Board Regular

HalfAce

MrExcel MVP

markadon

Board Regular

Andrew Poulsom

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock