Solved: Link to password protected sheet in Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

CJSquibb

Thread Starter
Joined
Feb 8, 2010
Messages
40
I have a workbook for Orders with data retrieved from a Customers workbook that I want to password protect. At the moment, when the user wants to place a new order, he clicks on a button and a macro fills in the cells for the next row, actually entering the link in the form

Code:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],'C:\path\Customers.xlsx'!CustomerData,3,FALSE)"
The cells in the worksheet thus look up the data from the Customers workbook and offer these as suggested details for the new order - the user may then manually overtype e.g. delivery address etc. Once the order has been finalised, another command button removes the links from this row, pasting the entered values back into their own cells, and entirely deletes the sensitive data so no record of credit card data remains on this sheet.

My problem is that I want to secure the Customers workbook with a password. It contains credit card information, including authorisation code, as well as the customer's full name and address. But if I set a password on this workbook, the user is asked to supply the password not only when updating the workbook links but also for EACH instance of the macro filling in a linked cell - some 15 or so times per transaction.

I would like to perhaps set a global variable asking for the password when the orders sheet is first opened, and store it for the duration of the session, then lose it when the workbook is closed.

How do I trap the password dialog box and enter the password from a global variable?
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
Maybe you should try it anotherway.
You could set the restriction in the calling worksheet.
I have attached a vba module I found and here I set the password to 1234, it's hard coded in the vba project.
If you set the VBA projects'parameters to hidden and password protected you cannot see the actual password.
The code will query for the password and if it's correct you continue.
You can add a Sheet that is hidden via VBA coe as xlSheetVeryHidden so that it can only be revealed via vba code and ther you can put a list of usernames (make them equal to the system environment variable %USERNAME% and you can even give these users different rights

Just add this line of code to your already present code as first line:

If AskPassword() = False then exit Function or Sub depending on your macro

BTW: You must of course remove the password from the linked sheet, I don't think there is a way to pass the password via code. I'll see if I find anything though.
 

Attachments

CJSquibb

Thread Starter
Joined
Feb 8, 2010
Messages
40
Hello Hans

Thanks for this - it's certainly an avenue to explore - though not tonight! I didn't know about "veryhidden"!

In the short term, I've used "Sendkeys" to type in the password each time the cell is being filled in with a vlookup(). The procedure starts by setting the password as a variable from an inputbox, so the user only enters it once each time an order is placed. I'd still like to make this a global variable so the user only has to enter it once per session, rather than once per order.

Anyway, again thank you and I'll have a look at this way of doing it tomorrow.

Claire
==
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
Hi Claire,
I haven't tried the send keys option myself.If sendkeys allows a variable to be sent, you could store this value in the VBA project as a constant, call it up if the person opening the workbook is authorized and then maybe it will open the linked sheet automatically without him / her having to verify it every time.
Is the data (order) entered locally after verification sent to the Customer's workbook and added there?
If you have a sample with one ficticious customer and nonesense data and a user workbook I would like to take a look at it for you.
It's easier if I 'see' the problem thatn trying to visualize it from text.
Let me know, we can always exchange info via a private message.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
Here is a sheet with some tips (nothing yet for external sheets) for local settings like disabling Cut and Copy, hiding sheets and VeryHidden sheets.

Maybe there is something there you can use too :)
 

Attachments

CJSquibb

Thread Starter
Joined
Feb 8, 2010
Messages
40
Dear Hans

Thanks so much for the file and the help. I'm pretty sure that I'll use the VeryHidden method - it's elegant and simple. Yes, nothing is completely foolproof against determined hackers, but for reasonable security it's perfectly adequate. It just means he can show that he hasn't got his client data freely available and unprotected.

I liked the sample sheet and code - very kind of you to send it. Saves me a bit of homework!

Thanks again

Claire
==
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top