Results 1 to 7 of 7
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    XL 2K: Using VBA to check if a sheet is present

    I want to check if sheet is present in my workbooks. This will be a condition for running (or not running) various macros.
    Any suggestions?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL 2K: Using VBA to check if a sheet is present

    Do you mean something like:
    <pre>For Each w In ActiveWorkbook.Worksheets
    If w.Name = "sheetname" Then ....
    Next
    </pre>

    ?

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: XL 2K: Using VBA to check if a sheet is present

    Yeah exactly, but what if I want something to happen if the sheet is not in the workbook?
    After all if there are multiple sheets in the workbook, then some of them won't have the sheetname I'm looking for. But I don't want to have actions triggered before all the sheets are looked at.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL 2K: Using VBA to check if a sheet is present

    Maybe:
    <pre>x=0
    For Each w In ActiveWorkbook.Worksheets
    If w.Name = "sheetname" Then x = 1
    Next
    If x = 1 Then ...
    If x = 0 Then ...
    </pre>

    ?

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: XL 2K: Using VBA to check if a sheet is present

    Ha! that was it exactly.
    Thank you very much.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL 2K: Using VBA to check if a sheet is present

    Though in real life I'd probably use something like

    blnWorkbookExists = false

    rather than

    x = 0

    For readability in case somebody has to do something later
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL 2K: Using VBA to check if a sheet is present

    A faster way than using a loop:

    <pre>Dim oSheet as Worksheet
    On Error Resume Next
    Set oSheet = Worksheets("Sheet1")
    On Error GoTo 0
    If oSheet Is Nothing then
    Msgbox "Sheet1 does not exist."
    End If
    Set oSheet = Nothing
    </pre>

    Legare Coleman

Posting Permissions

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