assistance to make a vba code run on macro file

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

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

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

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.

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

Attached is your sample workbook ... for your tests ...

Hope this will help

P.S. KjBox just gave you all the necessary explanations ...:wink:

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.

Private Sub Workbook_Open()
    Dim PermittedUsers
    
    PermittedUsers = Array("dell", "abc", "xyz") '// Edit, remove or add names of computers that can access this workbook


    If IsError(Application.Match(Environ("COMPUTERNAME"), PermittedUsers, 0)) Then ThisWorkbook.Close 0
    
    ' Your othe Workbook_Open code can go here


End Sub

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

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"

Private Sub Workbook_Open()
    Dim PermittedUsers
     
    PermittedUsers = Array("dell", "VB_J") '// Edit, remove or add names of computers that can access this workbook
    
    If IsError(Application.Match(Environ("COMPUTERNAME"), PermittedUsers, 0)) Then ThisWorkbook.Close 0


    ' Your other Workbook_Open code can go here
     
End Sub

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

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

Private Sub Workbook_Open()
    Dim PermittedUsers
    
    ' Create an array of computer names that are permitted to open this workbook
    'Edit, remove or add names of computers that can access this workbook
    
    PermittedUsers = Array("dell", "VB_J")
    
    ' Check if computer being used is a registered computer
    If IsError(Application.Match(Environ("COMPUTERNAME"), PermittedUsers, 0)) Then
        MsgBox "This computer is not registered to open " & ThisWorkbook.Name
        ThisWorkbook.Close 0
    Else
        On Error Resume Next 'in case name doesn't exist
         'check value stored for computer
        sComp = Evaluate(ThisWorkbook.Names("dell").RefersTo)
         'range name doesn't exist so create it & set value
        If sComp = "" Then sComp = CompName '
        ThisWorkbook.Names.Add Name:="dell", RefersTo:=sComp
    End If
     
End Sub

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

Hello,

You can test following code ...

Private Sub Workbook_Open()
    Dim PermittedUsers
     
     ' Create an array of computer names that are permitted to open this workbook
     'Edit, remove or add names of computers that can access this workbook
     
    PermittedUsers = Array("dell", "VB_J")
     
     ' Check if computer being used is a registered computer
    If IsError(Application.Match(Environ("COMPUTERNAME"), PermittedUsers, 0)) Then
        MsgBox "This computer is not registered to open " & ThisWorkbook.Name
        ThisWorkbook.Close 0
   End If
     
End Sub

Hope this will help

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.

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 ???

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

Private Sub Workbook_Open()
    Dim PermittedUsers, sComp As String
    
    ' Create an array of computer names that are permitted to open this workbook
    'Edit, remove or add names of computers that can access this workbook
    
    PermittedUsers = Array("dell", "carim_PC", "carim_Laptop", "VB_J", "VB_W10o16")
    
    ' Check if computer being used is a registered computer
    If IsError(Application.Match(Environ("COMPUTERNAME"), PermittedUsers, 0)) Then
        MsgBox "This computer is not registered to open " & ThisWorkbook.Name
        ThisWorkbook.Close 0
    Else
        On Error Resume Next 'in case name doesn't exist
         'check value stored for computer
        sComp = Evaluate(ThisWorkbook.Names(Environ("COMPUTERNAME")).RefersTo)
         'range name doesn't exist so create it & set value
        If sComp = "" Then sComp = Environ("COMPUTERNAME")
        ThisWorkbook.Names.Add Name:=sComp, RefersTo:=sComp
    End If
     
End Sub

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.