Excel VBA - Does Sheet Exist-VBForums
Results 1 to 7 of 7

Thread: Excel VBA - Does Sheet Exist

  1. #1

    Thread Starter
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Excel VBA - Does Sheet Exist

    I can do
    Code:
    If Blah = True Then
         Sheets("My Sheet Name").Visible = True
    End If
    And that works fine, but I want to alter this to check whether or not the sheet even exists. I don't know what it does like, but I can tell you it does not like this
    Code:
    If Sheets("My Sheet Name").Name = True Then
    So, how to I determine whether or not a sheet exists in my workbook?
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  2. #2
    Hyperactive Member
    Join Date
    Jun 2006
    Location
    Best Place on Earth
    Posts
    363

    Re: Excel VBA - Does Sheet Exist

    Hi Hack,

    I have been using the following.

    VB Code:
    1. Function WorksheetExists(TWbk As String, WName As String) As Boolean
    2. ' Returns True if a worksheet WName exists in Workbook TWbk
    3. Dim Counter As Long
    4.     WorksheetExists = False
    5.     Counter = 1
    6.     Do
    7.         ' The Worksheet names are Case Insensitive, hence the
    8.         ' Upper Case conversion in the comparison.
    9.         If UCase(WName) = UCase(Workbooks(TWbk).Worksheets(Counter).Name) Then
    10.             WorksheetExists = True
    11.             Exit Do
    12.         End If
    13.         If Counter = Workbooks(TWbk).Worksheets.Count Then
    14.             Exit Do
    15.         End If
    16.         Counter = Counter + 1
    17.     Loop
    18. End Function

    Someone else might be able to come up with a more elegant solution but I know this one works.
    Signature Under Construction

  3. #3

    Thread Starter
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Excel VBA - Does Sheet Exist

    I'll give it a shot. Thanks Torc!
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  4. #4
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Excel VBA - Does Sheet Exist

    Hi Hack

    Another way to do it is...

    vb Code:
    1. Private Sub CommandButton1_Click()
    2.  
    3. Dim WSName As String, Found As Boolean
    4. Found = False
    5.  
    6. 'Replace Sheet3 with the name of the relevant sheet
    7. WSName = "Sheet3"
    8.  
    9. 'loop thru the names of the sheets
    10. For i = 1 To ActiveWorkbook.Sheets.Count
    11.  
    12.     If UCase(WSName) = UCase(ActiveWorkbook.Sheets(i).Name) Then
    13.         Found = True
    14.         Exit For
    15.     Else
    16.         Found = False
    17.     End If
    18.  
    19. Next i
    20.  
    21. 'Display Status
    22. If Found = False Then
    23.     MsgBox "Sheet Doesn't exist"
    24. Else
    25.     MsgBox "Sheet exists"
    26. End If
    27.  
    28. End Sub
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  5. #5
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: Excel VBA - Does Sheet Exist

    Quote Originally Posted by Torc
    ' The Worksheet names are Case Insensitive, hence the
    ' Upper Case conversion in the comparison.
    Yes, worksheet names are Case "Insensitive" (ie. Not Case Sensitive), so do not need to use Upper Case conversion to compare, unless the Option Compare Binary are used.

    @Hack, This is a much simpler way to check whether a worksheet name exist without a loop:
    Code:
    Dim ws As Worksheet
    
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("YourSheetName")
    If Err > 0 then
       MsgBox "Worsheet 'YourSheetName' Does Not Exist!"
       Err.Clear
    Else
       MsgBox "Worsheet 'YourSheetName' Found!"
    End If
    On Error Goto
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    Baby Steps a guided tour
    IsDigits() and IsNumber() functions Wichmann-Hill Random() function >> and << functions for VB CopyFileByChunk

  6. #6

    Thread Starter
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Excel VBA - Does Sheet Exist

    Thank you koolsid and anhn.....I will give these a try as well.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  7. #7
    New Member
    Join Date
    Aug 2012
    Posts
    1

    Re: Excel VBA - Does Sheet Exist

    Hack, here is another option...

    Option Explicit
    Option Base 0
    Option Compare Binary
    DefLng A-Z
    Function DoesSheetExist()

    Dim SheetName As String, DoesWorkSheetExist As Boolean

    SheetName = "My Sheet Name"

    DoesWorkSheetExist = CycleThroughSheets(SheetName)

    If DoesWorkSheetExist = True Then MsgBox """" & SheetName & _
    """ worksheet does exist.", vbOKOnly, "Sheet Exists!" Else _
    MsgBox """" & SheetName & """ worksheet does not exist.", vbOKOnly, _
    "Sheet Does Not Exists!"

    End Function

    Function CycleThroughSheets(ByRef SheetName As String) As Boolean

    Dim Wks As Excel.Worksheet

    For Each Wks In Worksheets

    If Wks.Name = SheetName Then: _
    CycleThroughSheets = True: Exit Function

    Next Wks
    End Function

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.