1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Link to password protected sheet in Excel

Discussion in 'Business Applications' started by CJSquibb, Apr 27, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. CJSquibb

    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?
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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.
     

    Attached Files:

  3. CJSquibb

    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
    ==
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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.
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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 :)
     

    Attached Files:

  6. CJSquibb

    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
    ==
     
  7. Sponsor

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 733,556 other people just like you!

Loading...
Similar Threads - Solved Link password
  1. Robm1955
    Replies:
    5
    Views:
    367
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/919523

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice