Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    18

    Unanswered: check for existing sheet in excel ..

    Hi, may i know how do i check whether a certain worksheet exists in the excel ?

    thanks alot ..

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Try This

    Code:
     Dim I As Integer
        Dim SheetExsits As Boolean
        
        'initalise SheetExsits
        SheetExsits = False
        
        'Check if SheetName Exsits
        For I = 1 To Worksheets.Count
            If Worksheets(I).Name = "Sheet3" Then
                SheetExsits = True
                Exit For
            End If
        Next I
        
        'display messagebox with position
        MsgBox I
    Dave

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    Here's another way, using a Function.

    Code:
    ' add this code
    
    Function SheetExists(sheetname) As Boolean
    '   Returns TRUE if sheet exists in the active workbook
        Dim x As Object
        On Error Resume Next
        Set x = ActiveWorkbook.Sheets(sheetname)
        If Err = 0 Then SheetExists = True _
            Else: SheetExists = False
    End Function
    
    ' Call it from your procedure in the same or another module.
    ' like this
    ' This will work using the sheet name or an index number of the sheet
    
    Sub myproc()
    
        If SheetExists("Sheet1") Then
            Sheets("Sheet1").Activate
        Else
            Exit Sub
        End If
    
    End Sub
    ~

    Bill

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    much better than my way, cheers Bill can i ask a quick question why did you set x as an object in your code rather than a worksheet, I know it doesn't matter for code execution but is there any reason you did this? Just satisfying my curiosity

  5. #5
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by DavidCoutts
    why did you set x as an object in your code rather than a worksheet, I know it doesn't matter for code execution but is there any reason you did this? Just satisfying my curiosity
    This is a canned function that I use as needed in various projects. Using the general 'Object' Type allows you to swap out any object in the 'Set' statement to make a new function to test for existance; Range, Workbook, etc. I originally found this function in the book Excel Power Programming Techniques You can find this example and other usefull functions on the Spreadheet page website.

    worksheet is not actually a "defined" supported data type, that is if you go by the excel documentation. You can find a Table of Data types by looking up "Data Type Summary" in the Excel VB Help. 'Worksheet' is a member of the 'Object' Type classification, so using it will only make you're code more specific and less forgiving.
    Last edited by savbill; 09-07-04 at 02:59.
    ~

    Bill

Posting Permissions

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