+ Reply to Thread
Results 1 to 10 of 10

Restricting user access using VBA

  1. #1
    Registered User
    Join Date
    08-16-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    16

    Restricting user access using VBA

    Hello

    I'm not sure if this is even possible but thought I'd put it out there in case you experts knew how. I basically have an Excel document which has very sensitive information. Just password protecting it isn;t enough as the password might be shared by users. What I would like to do is create a master list of user names (ideally using their email addresses as this will ensure unique entries) and only those users will be allowed access to the spreadsheet. Only I will have access and right to edit this master list and adding or removing a name from this list will change access rights for those users automatically, for this document. Is there any way i can do this using VBA? The spreadsheet itself has no code in it and is such a simple table with data.

    Thanks!

  2. #2
    Forum Contributor wakeupcall's Avatar
    Join Date
    02-29-2012
    Location
    Sofia, Bulgaria
    MS-Off Ver
    Excel 2010
    Posts
    155

    Re: Restricting user access using VBA

    Hi there,

    a quick way I can think of is using Application.UserName for filtering out people who should not be seeing the data. What this does is checking the name to which Excel was registered

    Incorporating it into some sort of If-Then structure that loops through your list of names (perhaps through using InStr function or vlookup built in function) could do the trick, I beleive
    Liked the answer given? click * to say so

  3. #3
    Registered User
    Join Date
    08-16-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    16

    Re: Restricting user access using VBA

    Thanks Sofia for your quick response. I'm not familiar with the Application.UserName function. How and where should I use it?

  4. #4
    Forum Contributor wakeupcall's Avatar
    Join Date
    02-29-2012
    Location
    Sofia, Bulgaria
    MS-Off Ver
    Excel 2010
    Posts
    155

    Re: Restricting user access using VBA

    Hello Nmarkit,

    What I would do in your shoes is set the all the tabs with very sensitve information to VeryHidden (including the tab with names that have access to the info), leaving only one sheet with a macro button on it that triggers the following macro:

    Please Login or Register  to view this content.
    What is good about VeryHidden property is that the only way to find out that the very hidden sheet actually exists is to open the VBE editor and change its properties from there

    I am far from the thought that this is the ideal solution that can fully replcae workbook password protection, but if the two are combined, it can provide some strong access control in place

    Hope this helps

  5. #5
    Registered User
    Join Date
    08-16-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    16

    Re: Restricting user access using VBA

    Thanks Sofia! this solution is perfect.

  6. #6
    Registered User
    Join Date
    08-16-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    16

    Re: Restricting user access using VBA

    Sorry if I may ask you one last query on this please. What format are the usernames saved in the Names worksheet? I tried putting my own email address but it's rejecting me as a valid user.

  7. #7
    Forum Contributor wakeupcall's Avatar
    Join Date
    02-29-2012
    Location
    Sofia, Bulgaria
    MS-Off Ver
    Excel 2010
    Posts
    155

    Re: Restricting user access using VBA

    They should match what Excel returns as a registered name for the user. In my case it returns my first name fiollowed by my family name. This, however, may vary from Excel copy to Excel copy, but assuming that all your users use corporate registered software, the registered names should follow the same pattern

    To find out what your Excel application returns, try using the following in any open workbook

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    08-16-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    16

    Re: Restricting user access using VBA

    Got it! thanks again

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Restricting user access using VBA

    You'd be better off with Environ("UserName") rather than Application.Username - this returns the windows log on name.

  10. #10
    Forum Contributor wakeupcall's Avatar
    Join Date
    02-29-2012
    Location
    Sofia, Bulgaria
    MS-Off Ver
    Excel 2010
    Posts
    155

    Re: Restricting user access using VBA

    Kyle123,

    I fully agree with you - your version is defintely the better approach
    Looks like I've learnt something new today, as well

    Thank you for this

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Restricting Multi-user access
    By [email protected] in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM
  2. [SOLVED] Restricting Multi-user access
    By [email protected] in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  3. Restricting Multi-user access
    By [email protected] in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  4. [SOLVED] Restricting Multi-user access
    By [email protected] in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  5. [SOLVED] Restricting Multi-user access
    By [email protected] in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1