code to run or exit a macro based on criteria

[amit6488]

Hello Members,
Below is the detail of my idea that I want to accomplish:

I have created an excel with number of macros that will be used by different users across the organization. These users needs to be granted permissions (Read Only, Read Write, Full Access etc.) and updated in a sheet names "Access Sheet" of this same excel.

What I want to achieve is that when a user tries to run a macro, based on the users access level assigned in the "Access Sheet" the macro should either run or displays a message of access denied and macro exits.

How I want to achieve this:
1. When a user hits a button to run the macro, the script will run and get the username (using UserName = Environ$("UserName") command.
2. code then compares this system username against the usernames updated in the "Access Sheet" and lookup the access level granted to the user in that "Access Sheet".
3. if the retrieved username does not exists in the "Access Sheet" OR the Access Level Granted is "Read Only", then the code should stop execution after displaying the "ACCESS DENIED" message.

Can anyone provide me with a code for the same. Thanks a lot in advance.

 

[Chihiro]

I'd recommend uploading sample file of your set up.

But typically, you'd use Application.Match() or Range.Find() method.

Using match, if number isn't returned, you'd display your message and exit sub. Or when number is returned, use INDEX or some other method to find value stored in corresponding access level column.

Range.Find() can be combined with offset function.

 

[amit6488]

I'd recommend uploading sample file of your set up.

But typically, you'd use Application.Match() or Range.Find() method.

Using match, if number isn't returned, you'd display your message and exit sub. Or when number is returned, use INDEX or some other method to find value stored in corresponding access level column.

Range.Find() can be combined with offset function.

This is what I have done till now: Created the below mentioned public function, which retrieves the username (system Login ID) and with the help of vlookup gets the value against the username from the "Access Sheet" (which is the access level, Read Only, Read Write etc.).

The code in bracket and bold works well within this function and displays proper required message based on access level value retrieved.

Public Function UserName()
UserName = Environ$("UserName")
Dim sres As String
    sres = Application.VLookup(UserName, Sheet5.Range("B2:C40"), 2)
  ( If sres = "Read Only" Then
    MsgBox ("ACCESS DENIED")
    Else
    MsgBox ("Access Granted")
    End If )
End Function

I am calling this function from another code but what i want to achieve is that the code in (brackets and bold) should do the calculation in the code from where this function is called. The challenge I am facing is that the value of "sres" is not getting transferred for the if,else code to work.

This is the code where I am calling the function and want the if else statement to work.

Sub NewTrkrItem()
UserName
If sres = "Read Only" Then
    MsgBox ("ACCESS DENIED")
    Else
carryon = MsgBox("Is this an Entry which requires creation of New SOW/RFC#? Click YES to proceed or NO to Cancel.", vbYesNo)
If carryon = vbYes Then
Sheets("Tracker New Entry").Visible = True
Sheets("Tracker Selection").Visible = xlSheetVeryHidden
Sheets("Tracker New Entry").Activate
ActiveSheet.Range("A1").Select

 

[Chihiro]

Uploading sample workbook with desensitized data makes it so much easier for us to help you.

 

[amit6488]

Uploading sample workbook with desensitized data makes it so much easier for us to help you.

attaching the sample excel for your reference. the original one is too big to be attached.

 

[Chihiro]

Few things:

1. Don't use same name for sub and variable. It can cause ambiguity in returned result.
2. Function should be used to return value rather than perform operation (that should be role of subs).
3. You are currently using Approximate match rather than exact match for your Vlookup. This can return unexpected results.

You can do something like below for an example.

Public Function UserAccess() As String
Dim UserName
UserName = Environ$("UserName")
Dim sres
sres = Application.VLookup(UserName, Sheet5.Range("B2:C40"), 2, False)
If IsError(sres) Then sres = "Error"
UserAccess = sres
End Function


Sub NewTrkrItem()
Dim x
x = UserAccess
Select Case x
    Case Is = "Read Only"
        MsgBox ("ACCESS DENIED")
        Exit Sub
    Case Is = "Error"
        MsgBox ("NOT A VALID USER")
        Exit Sub
    Case Else
        'Do something
End Select

'Worksheets("Access Sheet").Range("E2").ClearContents
End Sub

 

[amit6488]

Few things:

1. Don't use same name for sub and variable. It can cause ambiguity in returned result.
2. Function should be used to return value rather than perform operation (that should be role of subs).
3. You are currently using Approximate match rather than exact match for your Vlookup. This can return unexpected results.

You can do something like below for an example.

Public Function UserAccess() As String
Dim UserName
UserName = Environ$("UserName")
Dim sres
sres = Application.VLookup(UserName, Sheet5.Range("B2:C40"), 2, False)
If IsError(sres) Then sres = "Error"
UserAccess = sres
End Function


Sub NewTrkrItem()
Dim x
x = UserAccess
Select Case x
    Case Is = "Read Only"
        MsgBox ("ACCESS DENIED")
        Exit Sub
    Case Is = "Error"
        MsgBox ("NOT A VALID USER")
        Exit Sub
    Case Else
        'Do something
End Select

'Worksheets("Access Sheet").Range("E2").ClearContents
End Sub

Works like a charm .. thanks a lot for the code chihiro .