Run BeforeClose Event in One Situation and Ignore or Bypass It In Another

[NorthbyNorthwest]

If anyone can help with code below it would be very much appreciated. I’m at loss as I’ve reworked the code below multiple times with no success. I’m trying to do several three things in sub but can’t get everything to work. Thing 1: I want to determine if person—supervisor or caseworker--opening workbook is authorized based on their network environ or username. Code first checks if person is supervisor. If yes, then code performs several actions before opening for supervisor. (It will also perform some actions before closing.) This part of code works fine. If the person trying to open workbook isn’t a supervisor, code is supposed to check if it’s the workbook’s assigned user. Thing 2: If yes, the workbook should then determine if the workbook is the original or if the workbook is a copy (probably saved on the user’s desktop or “C” drive). If workbook is the original, then it should perform similar but not exact actions as it did for supervisor and open for user. (It will also perform some actions before closing.) However, if the workbook is a copy I want the person to receive a message and the workbook to close. My problem here is I have code in the BeforeClose event, code I do not want to run if the workbook is a copy. So, I’m trying to get code to bypass the BeforeClose event in this situation. Thing 3: If the person trying to open the workbook is neither the supervisor nor the assigned user, then I want person to receive message and workbook to close, again ignoring the coding in the BeforeClose event. In short, the BeforeClose event should only run if the user is authorized and the workbook is the original.

[FONT=Calibri][SIZE=3][COLOR=#000000]Sub AllAccess()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim r As Range[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim fName As Range[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim result As Integer[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim FilePath As String[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim TestStr As String[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.ScreenUpdating = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.DisplayAlerts = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Set fName = Sheet10.Range("N10")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'Check for supervisor environ[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Set r =Sheet10.Range("R:R").Find(What:=Environ("username"),after:=Range("R1"), _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,SearchDirection:=xlNext, _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]MatchCase:=True, SearchFormat:=False)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If Not r Is Nothing Then Call UnhideMultipleSheets[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If Not r Is Nothing Then Sheet10.Activate[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If Not r Is Nothing Then Call RecalcCells[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If Not r Is Nothing Then Sheet10.Range("N8").Value= r[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If Not r Is Nothing Then Call RecordSheets[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If Not r Is Nothing Then MsgBox "Welcome back "& fName & "!", Title:="Greeting"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If Not r Is Nothing Then Exit Sub[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'Check for employee environ[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Set r =Sheet10.Range("G:G").Find(What:=Environ("username"),after:=Range("G1"), _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,SearchDirection:=xlNext, _[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]MatchCase:=True, SearchFormat:=False)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If r Is Nothing Then MsgBox "Access to this workbook isdenied.  See your supervisor forassistance."[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If r Is Nothing Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    On Error GoToenableEventsOn:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Application.EnableEvents = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    ThisWorkbook.Close[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Application.EnableEvents= True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    On Error GoTo 0[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If Not r Is Nothing Then Call UnhideMultipleSheets[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If Not r Is Nothing Then Sheet10.Activate[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If Not r Is Nothing Then Call RecalcCells[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If Not r Is Nothing Then Sheet10.Range("N8").Value= r[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If Not r Is Nothing Then Call RecordSheets[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If Not r Is Nothing Then Call VisibleFalse[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If Not r Is Nothing Then MsgBox "Welcome back "& fName & "!", Title:="Greeting"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]FilePath = Sheet10.Range("SharedLocation")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]TestStr = Application.ThisWorkbook.Path[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If TestStr = FilePath Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'do nothing[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Else[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    GoTo NotOriginal[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Exit Sub[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.DisplayAlerts = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.ScreenUpdating = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]enableEventsOn:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.EnableEvents = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]NotOriginal:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]MsgBox ("This is not the original workbook on theshared drive.  You must enter data intothe original workbook.  If you need helpcreating a shortcut to your workbook, see your supervisor.")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]On Error GoTo enableEventsOn:[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.EnableEvents = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]ThisWorkbook.Close[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.EnableEvents = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]On Error GoTo 0[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub
[/COLOR][/SIZE][/FONT]

 

[JonXL]

If I'm understanding you correctly, I think you could achieve what you're looking for in Thing 3 by using a global boolean variable (like AuthorizedUser) and setting it to True when an authorized user opens the workbook and false if the user opening the book is not authorized.

In your BeforeClose, you can check the value of AuthorizedUser and nest the code in the conditional that checks for it being True.

 

[NorthbyNorthwest]

Thanks Jon for responding so quickly. I've never done this before, but I do follow what you're saying and will give it a try! Thanks for the direction.