Hello,

is there code to verify wether a certain sheet say <sheets("nameS") is
present in an open workbook?


Some help is most welcome.

Thank you


--
Zurn
------------------------------------------------------------------------
Zurn's Profile: http://www.excelforum.com/member.php...o&userid=14645
View this thread: http://www.excelforum.com/showthread...hreadid=520135

not sure if this is the preferred way


Sub test()
On Error GoTo err
Worksheets("sname").Activate
goto cont
err:
MsgBox "sheet does not exist"
exit sub
cont:
'your code
End Sub
--


Gary


"Zurn" wrote in message
...

Hello,

is there code to verify wether a certain sheet say <sheets("nameS") is
present in an open workbook?


Some help is most welcome.

Thank you


--
Zurn
------------------------------------------------------------------------
Zurn's Profile:
http://www.excelforum.com/member.php...o&userid=14645
View this thread: http://www.excelforum.com/showthread...hreadid=520135

Dim TestName as String
dim testWks as worksheet
testname = "NameS"

set testwks = nothing
on error resume next
set testwks = worksheets(testname)
on error goto 0

if testwks is nothing then
'not there
else
'it is there
end if

========
If you don't want to do the test inline, you could use this function from Chip
Pearson:

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0)
End Function

'and you can use it like:
....
if worksheetexists("myname",activeworkbook) then
application.displayalerts = false
worksheets("Myname").delete
application.displayalerts = true
end if

Zurn wrote:

Hello,

is there code to verify wether a certain sheet say <sheets("nameS") is
present in an open workbook?

Some help is most welcome.

Thank you

--
Zurn
------------------------------------------------------------------------
Zurn's Profile: http://www.excelforum.com/member.php...o&userid=14645
View this thread: http://www.excelforum.com/showthread...hreadid=520135

--

Dave Peterson

You could try this...

Dim blnSheetExists as Boolean
dim intCount as integer
dim intTotal as integer

intcount = 1
intsheetexists = false
inttotal = activeworkbook.worksheets.count

do while intcount < inttotal
sheets(intcount).select
if activehseet.name = "nameS" then
blnsheetexists = true
end if

intcount = intcount + 1
loop

msgbox "the existence of sheet nameS is " & blnsheetexists

Haven't tested this, but it should be about right.


"Zurn" wrote:


Hello,

is there code to verify wether a certain sheet say <sheets("nameS") is
present in an open workbook?


Some help is most welcome.

Thank you


--
Zurn
------------------------------------------------------------------------
Zurn's Profile: http://www.excelforum.com/member.php...o&userid=14645
View this thread: http://www.excelforum.com/showthread...hreadid=520135

Function SheetExists(shName As String) As Boolean
On Error Resume Next
SheetExists = Not Sheets(shName) Is Nothing
End Function

You can test the above with
if SheetExists("Herman") then...

Bob Umlas
Excel MVP

"Zurn" wrote in message
...

Hello,

is there code to verify wether a certain sheet say <sheets("nameS") is
present in an open workbook?


Some help is most welcome.

Thank you


--
Zurn
------------------------------------------------------------------------
Zurn's Profile:
http://www.excelforum.com/member.php...o&userid=14645
View this thread: http://www.excelforum.com/showthread...hreadid=520135

Thanks everybody!

Works perfect...


--
Zurn
------------------------------------------------------------------------
Zurn's Profile: http://www.excelforum.com/member.php...o&userid=14645
View this thread: http://www.excelforum.com/showthread...hreadid=520135