Call vba if the workbook is saved in a specific location

[StillUnderstanding]

Hello Everyone,

So I am working on a workbook that will be saved in 2 locations, 1 location will allow for full access and the other will allow access to only select tabs.

When I save the unrestricted file to the restricted location I want for it to call a sub that restricts parts of the workbook.

Here is what I tried but it wont work, can anyone help with this?

Thanks!

Private Sub Workbook_Location_Lock()
If Dir("https://file-location/filename.xlms") <> "" Then
Call call_the_sub
Else
MsgBox "Admin Mode Activated"
End If
End Sub

 

[rollis13]

How come you are using a HTTP address ?
I'd believe that this macro, pasted in the ThisWorkbook module, which exploits event Workbook_Open would react better.

Option Explicit
Private Sub Workbook_Open()
    Stop
    If ThisWorkbook.Path = "\\Server_Name_Or_IP_Address\File_Location" Then
        Call call_the_sub
    Else
        MsgBox "Admin Mode Activated"
    End If
End Sub

Private Sub call_the_sub()
    MsgBox "Passes here"
End Sub

 

[StillUnderstanding]

How come you are using a HTTP address ?
I'd believe that this macro, pasted in the ThisWorkbook module, which exploits event Workbook_Open would react better.

Option Explicit
Private Sub Workbook_Open()
    Stop
    If ThisWorkbook.Path = "\\Server_Name_Or_IP_Address\File_Location" Then
        Call call_the_sub
    Else
        MsgBox "Admin Mode Activated"
    End If
End Sub

Private Sub call_the_sub()
    MsgBox "Passes here"
End Sub

Hi, Thank you for your help!!!


So I tried this and it would not work for me. The only way it would work is if I put in "If Not ThisWorkbook.Path" but this is not correct.

The problem was that it was in the correct workbook path but it still called the sub when listed as "If Not" but would not call is on "If".

I can see you set the sub to "Workbook_Open" but if I am saving the file from a secure location to a non secure location then I need it to call the sub on selecting a cell or tab.

Any thoughts on how I can get this working @rollis13

 

[rollis13]

You can use event Workbook_BeforeSave for that.

 

[StillUnderstanding]

You can use event Workbook_BeforeSave for that.

@rollis13 thanks again for your help with this.

Yes thats an idea, but a bit of a pain if you are working on the workbook and periodically save as it will lock the tabs.

Ideally it should call the sub if its not saved in the determined location. The first option looked good and would be the ideal one if I can get it to work.

I tried to update it and save it locally to see if it was the sharepoint address being the issue but I still found it doing the same thing.

 

[rollis13]

Let me put it this way, honestly I have not yet fully understood the real problem. That said, since I have no experience with Sharepoint, I leave the discussion to make room for the experts.

 

[StillUnderstanding]

Thanks @rollis13 for your help to this point.

Just in the event that my post is not clear i will explain again in the hope that someone is able to help!

I have a single workbook with 10 tabs that will be accessed by multiple users at any single time using Teams Sharing in the Excel App (This allows the Macro to run).
7 Tabs contain sensitive data and should have restricted access
3 Tabs have data that anyone can view.

At the end of the day the workbook will be saved in the same directory (secure location), we will then save the same workbook in a second directory that we will be available to anyone (unsecure ).

The aim is to have a sub called if the file is saved in any location that is not the specified location. So for example, if the file is not saved in "\\Sharepoint\Files" then it will always call the sub.

The sub it calls will lock the 7 sensitive tabs away from view and secure the workbook as required.

I hope that helps and that someone can help me to work this out!

Thanks!!