Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does Sheet Name Exist in a Workbook
Hello All,
Pretty trivial puzzle for someone I'm sure. Without looping though all the sheets in a workbook is it possible to find out (TRUE or FALSE) if a sheet named "mySheet" exists within the activeworkbook ? I tried setting something up using On Error but still got a run-time error 9! Any help much appreciated Jason |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does Sheet Name Exist in a Workbook
two useful functions
Option Explicit Sub test1() MsgBox SheetExists("Sheet1") End Sub Sub test2() Dim text As String Dim ws As Worksheet text = "sheet1" Set ws = GetSheet(text) MsgBox text & " exists: " & Not (ws Is Nothing) End Sub Public Function SheetExists(sName As String) As Boolean On Error Resume Next Dim ws As Worksheet Set ws = Worksheets(sName) SheetExists = Not (ws Is Nothing) On Error GoTo 0 End Function Public Function GetSheet(sName As String) As Worksheet On Error Resume Next Dim ws As Worksheet Set ws = Worksheets(sName) Set GetSheet = ws On Error GoTo 0 End Function "WhytheQ" wrote: Hello All, Pretty trivial puzzle for someone I'm sure. Without looping though all the sheets in a workbook is it possible to find out (TRUE or FALSE) if a sheet named "mySheet" exists within the activeworkbook ? I tried setting something up using On Error but still got a run-time error 9! Any help much appreciated Jason |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does Sheet Name Exist in a Workbook
If you refer to a sheet that does not exist you get an error so at simple
level, just test for that error. Something like following: Sub CheckSheet() Dim WS As Worksheet On Error Resume Next Set WS = Worksheets("Sheet3") If Err 0 Then MsgBox " Sheet Does Not Exist" Else MsgBox "Sheet Exists" End If End Sub -- jb "WhytheQ" wrote: Hello All, Pretty trivial puzzle for someone I'm sure. Without looping though all the sheets in a workbook is it possible to find out (TRUE or FALSE) if a sheet named "mySheet" exists within the activeworkbook ? I tried setting something up using On Error but still got a run-time error 9! Any help much appreciated Jason |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does Sheet Name Exist in a Workbook
On 16 Sep, 13:18, Patrick Molloy
wrote: two useful functions Option Explicit Sub test1() MsgBox SheetExists("Sheet1") End Sub Sub test2() Dim text As String Dim ws As Worksheet text = "sheet1" Set ws = GetSheet(text) MsgBox text & " exists: " & Not (ws Is Nothing) End Sub Public Function SheetExists(sName As String) As Boolean On Error Resume Next Dim ws As Worksheet Set ws = Worksheets(sName) SheetExists = Not (ws Is Nothing) On Error GoTo 0 End Function Public Function GetSheet(sName As String) As Worksheet On Error Resume Next Dim ws As Worksheet Set ws = Worksheets(sName) Set GetSheet = ws On Error GoTo 0 End Function "WhytheQ" wrote: Hello All, Pretty trivial puzzle for someone I'm sure. Without looping though all the sheets in a workbook is it possible to find out (TRUE or FALSE) if a sheet named "mySheet" exists within the activeworkbook ? I tried setting something up using On Error but still got a run-time error 9! Any help much appreciated Jason- Hide quoted text - - Show quoted text - Nice one Patrick - will add those little beauties to my small collection of functions Kind Regards Jason. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
does a value exist in another workbook | Excel Worksheet Functions | |||
Does a sheet exist | Excel Programming | |||
If Sheet Exist | Excel Programming | |||
Copy Worksheet to a new Workbook creating if it doesn't exist and add more Worksheets if it does exist | Excel Programming | |||
Does sheet exist? | Excel Programming |