Dim I As Integer
Dim SheetExsits As Boolean
SheetExsits = False
'Check if SheetName Exsits
For I = 1 To Worksheets.Count
If Worksheets(I).Name = "Sheet3" Then
SheetExsits = True
'display messagebox with position
' 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
' 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
If SheetExists("Sheet1") Then
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
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.