Hide/Show worksheet based on portion of filepath

[George1972]

I would like to use VBA to hide/show a worksheet based on a portion of the filepath name.

For example:
If the filepath was D:\Data\My Documents\Projects\excelfile.xlsm

I want the worksheet to be visible if the beginning part of the path is D:\Data\My Documents, but then hide if the user saves the file to another location and the path changes

I am sure this can be done was hoping someone could help me out.

 

[John_w]

Welcome to MrExcel forums. Put this code in the ThisWorkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim SaveAs As FileDialog
 
    'The next If statement means the code below it is run only if File - Save As is clicked.
    'Remove this If statement if you want the code to also run when the Save icon (or File - Save) is clicked
    
    If Not SaveAsUI Then Exit Sub
    
    Set SaveAs = Application.FileDialog(msoFileDialogSaveAs)
    With SaveAs
        .Title = "Save Workbook"
        .InitialFileName = ThisWorkbook.FullName
        .InitialView = msoFileDialogViewDetails
        If .Show Then
            
            'Hide the sheet named "Sheet1" if workbook is not saved in "D:\Data\My Documents\" or a subfolder thereof
            
            If InStr(1, .SelectedItems(1), "D:\Data\My Documents\", vbTextCompare) = 0 Then
                ThisWorkbook.Worksheets("Sheet1").Visible = False
            End If
            
            Application.DisplayAlerts = False
            Application.EnableEvents = False
            .Execute
            Application.EnableEvents = True
            Application.DisplayAlerts = True
        End If
    End With
    
    Cancel = True
    
End Sub

Change Sheet1 in the code to the name of the sheet you want to be hidden.

 

[George1972]

Thank you very much. This does exactly what I needed. I appreciate the help.