VBA Code to Check if Sheet Exists and Exit Sub if Not
Results 1 to 6 of 6

Thread: VBA Code to Check if Sheet Exists and Exit Sub if Not

  1. #1
    Board Regular
    Join Date
    Dec 2016
    Posts
    172
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code to Check if Sheet Exists and Exit Sub if Not

    Thanks in advance for any suggestions as I will give feedback accordingly.

    Part of the response to my question has previously been posted, but since I've never used Functions in VBA and I am still new with VBA coding, I need some additional assistance.
    https://www.mrexcel.com/forum/excel-...et-exists.html

    I would like to check if the following sheet in the external workbook exists, and if not, terminate the program. I only posted the pertinent code.

    Code:
    Sub Check_Sheet()
    Dim i as Long
    
    For i = 3 to LastRow
    
    'The Path is "DirFolder"
    'The File Name is "File_Name"
    
    'If "Invoice Details" does not exist then (how to write this code?)
    
    Exit Sub
    
    EndIf
    
    Next i
    
    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,341
    Post Thanks / Like
    Mentioned
    440 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Code to Check if Sheet Exists and Exit Sub if Not

    Something like
    Code:
    Public Function ShtExists(ShtName As String, Optional Wbk As Workbook) As Boolean
        If Wbk Is Nothing Then Set Wbk = ActiveWorkbook
        On Error Resume Next
        ShtExists = (LCase(Wbk.Sheets(ShtName).Name) = LCase(ShtName))
        On Error GoTo 0
    End Function
    Sub chk()
       ShtName = "Sheet1"
       Set Wbk = Workbooks(File_Name)
       If Not ShtExists(ShtName, Wbk) Then Exit Sub
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Dec 2016
    Posts
    172
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to Check if Sheet Exists and Exit Sub if Not

    Thank you very much Fluff! Please note I have never used Functions and I am very new to writing Macros.

    First question is if I make this function Private, does that basically make it to where you look at the Macros menu, it will not appear?

    Second of all, since this is a closed workbook, don't I need to specify the directory?

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,341
    Post Thanks / Like
    Mentioned
    440 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Code to Check if Sheet Exists and Exit Sub if Not

    The function will not appear in the Macros menu, as it needs to be passed arguments.
    However if you change it to Private it must be in the same module as the code that is calling it.

    Also the workbook must be open.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Dec 2016
    Posts
    172
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to Check if Sheet Exists and Exit Sub if Not

    Thanks Fluff!

    Understood.

    How can I do it where if the workbook is closed?

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,341
    Post Thanks / Like
    Mentioned
    440 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Code to Check if Sheet Exists and Exit Sub if Not

    You could try this
    Code:
    Function ShtExistsClosed(Pth As String, Fname As String, ShtName As String)
       Dim FullPth As String
       
       FullPth = "'" & Pth & "[" & Fname & "]" & ShtName & "'!R1C1"
       ShtExistsClosed = Not IsError(Application.ExecuteExcel4Macro(FullPth))
    End Function
    Sub chk()
       Dim Pth As String, Fname As String, ShtName As String
       Pth = "C:\Mrexcel\"
       Fname = "+book1.xlsm"
       ShtName = "List"
       Debug.Print ShtExistsClosed(Pth, Fname, ShtName)
    End Sub
    But if the path or filename are wrong it will open up a dialogue box.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •