assistance to make a vba code run on macro file

  • Hi Excel Gurus


    I am looking to restrict workbook to access to specific computers, because I am getting a lot of data mismatch due that users are moving files from pc to pc by email.


    the user are low users


    Under some research on site http://www.ozgrid.com/forum/showthread.php?t=159712


    I am trying to make the codes below work but no success



    Can any Gurus assist me


    thank you




    and



    Code
    Option Explicit 
     
    Function CompName() As String 
        CompName = Environ("USERNAME") 
    End Function
  • Re: assistance to make a vba code run on macro file


    Hello,


    The first step would be to establish a reference list of the Computer Names you want to authorize ...


    Code
    Function CompName() As String
        CompName = Environ("COMPUTERNAME")
    End Function


    The above function will retrieve the actual current computer name ... which can ... then ... be compared to your initial reference list ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: assistance to make a vba code run on macro file


    Hi Carim


    See attach my computer name but not working, computername and username changed to dell

    Images

    • COMPUTERNAME .jpg
  • Re: assistance to make a vba code run on macro file


    One way to achieve this is to make a list of permitted computers (this list can be on a new sheet that is hidden). Make the list a Named Range (ideally, a Dynamic Named Range), name the Named Range "PermittedUsers".


    Then place this code in the ThisWorkbook Object Module

    Code
    Private Sub Workbook_Open()
        If IsError(Application.Match(Environ("COMPUTERNAME"), [PermittedUsers], 0)) Then ThisWorkbook.Close 0
    End Sub


    If a computer who's Name is not in the list is used to try and open the file then the file will fail to open.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: assistance to make a vba code run on macro file


    Alternatively, if you do not want to use a list on a worksheet, the same can be achieved using an array within the code.


    In this case the elements that are added to the array will need to be maintained/updated rather than a list on a worksheet.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: assistance to make a vba code run on macro file


    By the way ... during your tests ...


    If you ever need to launch your workbook while disabling your Workbook_Open() macro ...


    Just press the Shift key ... while opening your workbook ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: assistance to make a vba code run on macro file


    I have edited my posts because there was a typo in the code, make sure you test with the edited code.


    I have just tested this code on 2 different virtual machines, one of which is named "VB_J"


    The test file opened fine on the VB_J machine but not on the other.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: assistance to make a vba code run on macro file


    You had 2 Workbook_Open procedures.


    Replace everything in the Thisworkbook Object Module with

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: assistance to make a vba code run on macro file


    Hello,


    You can test following code ...



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: assistance to make a vba code run on macro file


    I am attaching 2 files.


    The first (restrictacc1test_del) has "del", "VB_J" as the array elements, you should not be able to open this file.


    The second(restrictacc1test_dell) has "dell", "VB_J" as the array elements and should open for you, assuming you are testing on the computer with the name "dell".

  • Re: assistance to make a vba code run on macro file


    Quote

    As per your instruction, still no success changed to dell it opened , changed vb j , it still opened


    Changing "VB_J" to "VB J" will still allow the file to open when your computer is used.


    The code checks the name of the computer that is opening the file, you are still using the computer with the name "dell" so the code allows access because "dell" is a permitted name. The change you made would prevent me (using machine with name "VB_J") from opening the file.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: assistance to make a vba code run on macro file


    ok I test the files it worked ,even I changed to VB_J , it still worked ,now I understand the logic


    If I correctly understand


    module 1 remains same whether dell, vb_j, aaa


    Code
    Option Explicit
     
    Function CompName() As String
        CompName = Environ("USERNAME")
    End Function



    so the module in workbook if I duplicate the file , I just change those in red, array Array("dell", "VB_J", aaa"),should I also change those in blue


    Suppose for user aaa, should I change those in blue in the code below to aaa sComp = Evaluate(ThisWorkbook.Names("VB_J").RefersTo)& ThisWorkbook.Names.Add Name:="VB_J" to aaa or let it as it is in each file for different pc


    I will test in tommorrow on concerned pc and revert back


    Thank you for help



  • Re: assistance to make a vba code run on macro file


    Hi,


    Is your idea to have : Blue Names are Allowed ... and Red Names are NOT Allowed ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: assistance to make a vba code run on macro file


    All you need to do is load the array with the names of the computers that can access the file.


    So if the names of those computers are (for example):


    dell
    carim_PC
    carim_Laptop
    VB_J
    VB_W10o16


    Then the full code would need to be


    After saving the file with the modified code only those 5 computers would be able to open your workbook.


    The function in Module 1 is not used at all with this code. Unless you need it for another procedure you can remove it.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!