Open A File From Dialog Box

I am trying to automate a process.

The area of concern for this question is validating the active workbook is the proper workbook from the proper folder in our shared drive. Otherwise, the user will save data and it will not be shared. A safety measure to ensure data entry.

Some users save a copy of the aforementioned ‘active workbook’ to their PC off of the shared drive, which leads to data loss.

These days, people are IT insecure but terrified of automated functions unfamiliar with. To cater to these circumstances, I wanted to use a message box to alert the user they will not save data in the group file, then ask if they would like to open that file. Upon hitting "Yes", the other file would open while leaving the existing document open. The selection "No" or "Cancel" would end sub.

GOAL:

• Use a message box to alert the user that they are not working on the shared document. Upon selection, the yes button would open the correct document.

CODE (in Workbook):

Private Sub Workbook_Open()

Dim Sheet1 As Worksheet
Set Sheet1 = Sheets("Invoices")
Dim folpath As String
Dim mypath As String

    Application.ScreenUpdating = False
    
    folpath = "K:\Purchasing_Utilities\1_UTILITIES\4_VENDOR_INVOICES\GHOST_CARD\Active_Pay_Tracker_22.xlsm"
    mypath = Application.ActiveWorkbook.FullName
    
    If mypath = folpath Then
        GoTo Skip
    Else
        MsgBox "This file source is a locally saved file. To share changes, please open the Tracker in the K: drive." _
            & " Would you like the system to open this file now?", VbMsgBoxStyle = vbOKCancel + vbCritical
            
            'Here is where I am trying to get the message box to open the document
    

Skip
        Sheet1.Range("P:P").Sort Key1:=Sheet1.Range("P:P"), Order1:=xlAscending, Header:=xlYes
    
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Activate
    
    Application.ScreenUpdating = True

>Solution :

Just an example:

Result = MsgBox("This file source is a locally saved file. To share changes, please open the Tracker in the K: drive. Would you like the system to open this file now?", vbOKCancel + vbCritical)
If Result = vbOKCancel Then
MsgBox "You clicked OK"
Else: MsgBox "You clicked Critical"
End If

This looks as follows:

If you press "Ok", this is what you see:

So, if you want a vbYes, you’ll need to add this to the original messagebox (which currently does not have a "Yes" button) and add the corresponding Result handling (if Result = vbYes then ... (open file)).