macro to test if file is saved on pc, if so, hide certain sheets

[Alwinaz]

Hi there

I am looking for a vba that will check if file Funmathics.xlsm exist on pc and if so, hide the "Instructions" sheet when open. It the file does not exist hide all sheets, except the Instructions sheet.

 

[ParamRay]

.
.

Try the following code, which should be placed into the code module for ThisWorkbook (in the same workbook that contains your "Instructions" sheet).

Private Sub Workbook_Open()

    Dim FFold As String
    Dim FName As String
    Dim FPath As String
    Dim Exists As Boolean
    Dim Sht As Object
    
    'Set filename of workbook.
    'Change as necessary
    FName = "Funmathics.xlsm"
    
    'Set location of workbook
    'Change as necessary
    FFold = "C:\Users\gpeacock\Desktop"
    
    'Get path of workbook
    FPath = FFold & Application.PathSeparator & FName
    
    'Unhide all sheets in this workbook
    For Each Sht In ThisWorkbook.Sheets
        Sht.Visible = True
    Next Sht
    
    'Check if file exists
    Exists = (Dir(FPath) <> vbNullString)
    
    'If file exists then hide
    '"Instructions" sheet...
    
    If Exists Then
        ThisWorkbook.Sheets("Instructions").Visible = False
        Exit Sub
    End If
    
    'If file does not exist then hide
    'all sheets except "Instructions"...
    
    For Each Sht In ThisWorkbook.Sheets
        If Sht.Name <> "Instructions" Then
            Sht.Visible = False
        End If
    Next Sht

End Sub

 

[Alwinaz]

Hi there

The file will always be saved under My Documents\Funmathics. I have a macro that save the file under my documents and then create a folder Funmathics and save the file Funmathic.xlsm in that folder. How do i change your code to always find the path of My documents as sometimes My Documents can be on the C: or D: drive. Will change according to the user's set up.

 

[ParamRay]

Hi there

The file will always be saved under My Documents\Funmathics. I have a macro that save the file under my documents and then create a folder Funmathics and save the file Funmathic.xlsm in that folder. How do i change your code to always find the path of My documents as sometimes My Documents can be on the C: or D: drive. Will change according to the user's set up.

Change this line:

From:

FFold = "C:\Users\gpeacock\Desktop"

To:

FFold = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & Application.PathSeparator & "Funmathics"

 

[Alwinaz]

hi there.

I paste your code in ThisWorkbook. If i open the file then i get Unable to set the visable property of the Workbook class.