Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the Sheet name exist before running Macro code
Hello all,
Could anyone know how to check the sheet name (e.g. "mySheet") exist before running the rest macro code ? Thanks, tlee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the Sheet name exist before running Macro code
Copy the below function and use that in code as below
Sub Macro() 'If sheet do not exist then exit procedure If Not IsSheetExists("mySheet") Then Exit Sub End Sub Function IsSheetExists(strSheet As String) As Boolean On Error Resume Next Dim ws As Worksheet Set ws = Sheets(strSheet) If Not ws Is Nothing Then IsSheetExists = True End Function -- Jacob "tlee" wrote: Hello all, Could anyone know how to check the sheet name (e.g. "mySheet") exist before running the rest macro code ? Thanks, tlee . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the Sheet name exist before running Macro code
Hi Jacob,
Thank you very much for your help ! tlee ----------------------------------------------------------------------------------------------------------- Copy the below function and use that in code as below Sub Macro() 'If sheet do not exist then exit procedure If Not IsSheetExists("mySheet") Then Exit Sub End Sub Function IsSheetExists(strSheet As String) As Boolean On Error Resume Next Dim ws As Worksheet Set ws = Sheets(strSheet) If Not ws Is Nothing Then IsSheetExists = True End Function -- Jacob "tlee" wrote: Hello all, Could anyone know how to check the sheet name (e.g. "mySheet") exist before running the rest macro code ? Thanks, tlee . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the Sheet name exist before running Macro code
Try a function like the following:
Function SheetExists(SheetName As String, Optional ByVal WB As Workbook) As Boolean If WB Is Nothing Then Set WB = ThisWorkbook End If On Error Resume Next SheetExists = CBool(Len(WB.Worksheets(SheetName).Name)) End Function Set SheetName to the name of the worksheet to test, and set WB to the workbook in which the sheet might exist. If you omit the WB parameter, the code looks in the workbook that contains the code: If SheetExists("Sheet5") = True Then ' do something End If ' OR If SheetExists("Sheet5",Workbooks("Book4.xls")) = True Then ' do something End If Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Tue, 5 Jan 2010 12:53:44 +0800, "tlee" wrote: Hello all, Could anyone know how to check the sheet name (e.g. "mySheet") exist before running the rest macro code ? Thanks, tlee |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check the Sheet name exist before running Macro code
Hello Chip,
Thank you so much for giving me alternative. tlee Try a function like the following: Function SheetExists(SheetName As String, Optional ByVal WB As Workbook) As Boolean If WB Is Nothing Then Set WB = ThisWorkbook End If On Error Resume Next SheetExists = CBool(Len(WB.Worksheets(SheetName).Name)) End Function Set SheetName to the name of the worksheet to test, and set WB to the workbook in which the sheet might exist. If you omit the WB parameter, the code looks in the workbook that contains the code: If SheetExists("Sheet5") = True Then ' do something End If ' OR If SheetExists("Sheet5",Workbooks("Book4.xls")) = True Then ' do something End If Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Tue, 5 Jan 2010 12:53:44 +0800, "tlee" wrote: Hello all, Could anyone know how to check the sheet name (e.g. "mySheet") exist before running the rest macro code ? Thanks, tlee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check Auto Filter - if on, turn off before running macro | Excel Programming | |||
Want to check if cell is blank before running macro | Excel Programming | |||
Check values before running macro | Excel Programming | |||
How to check a cell for content before running macro. | Excel Programming | |||
Check cell for data before running code | Excel Programming |