Results 1 to 7 of 7

Thread: Excel VBA - Does Sheet Exist

  1. #1

    Thread Starter
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    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?

  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
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Excel VBA - Does Sheet Exist

    I'll give it a shot. Thanks Torc!

  4. #4
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    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
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  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
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Excel VBA - Does Sheet Exist

    Thank you koolsid and anhn.....I will give these a try as well.

  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
  •  



Click Here to Expand Forum to Full Width