How to check if a sheet exists ?

Vivek Samapra

2010-12-28 07:25:29 UTC

Dude ! ! ! ! I LOVE YOU ! ! ! ! I Have searched over the internet for 3 hours.. Only yours worked the best.. Im going to share this to everyone..

This is a multi-part message in MIME format.
------=_NextPart_000_0049_01CA80BE.43826100
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Title says it all.
Luc
------=_NextPart_000_0049_01CA80BE.43826100
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META name=3DGENERATOR content=3D"MSHTML 8.00.6001.18865">
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2 face=3DArial>Title says it all.</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial>Luc</FONT></DIV></BODY></HTML>
------=_NextPart_000_0049_01CA80BE.43826100--

This is a multi-part message in MIME format.
------=_NextPart_000_0008_01CA808F.6957F090
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Function SheetExists(SheetName As String, _
Optional WB As Workbook) As Boolean
Dim W As Workbook
If WB Is Nothing Then
Set W =3D ActiveWorkbook
Else
Set W =3D WB
End If
On Error Resume Next
SheetExists =3D CBool(Len(W.Worksheets(SheetName).Name))
End Function
Title says it all.
Luc
------=_NextPart_000_0008_01CA808F.6957F090
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META name=3DGENERATOR content=3D"MSHTML 8.00.6001.18854">
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2 face=3DArial>Courtexy Chip Pearson:</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT> </DIV>
<DIV>Function SheetExists(SheetName As String, _<BR>    =
Optional WB As=20
Workbook) As Boolean<BR>    Dim W As Workbook<BR>    =
If WB=20
Is Nothing Then<BR>        Set W =3D =
ActiveWorkbook<BR> =20
  Else<BR>        Set W =3D WB<BR>    =
End=20
If<BR>    On Error Resume Next<BR>    SheetExists =
=3D=20
CBool(Len(W.Worksheets(SheetName).Name))<BR>End Function<BR></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT> </DIV>
<DIV><FONT size=3D2 face=3DArial></FONT> </DIV>
<DIV><FONT size=3D2 face=3DArial></FONT> </DIV>
<BLOCKQUOTE=20
style=3D"BORDER-LEFT: #000000 2px solid; PADDING-LEFT: 5px; =
PADDING-RIGHT: 0px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px"=20
wrote in=20
message <A=20
=
<DIV><FONT size=3D2 face=3DArial>Title says it all.</FONT></DIV>
<DIV><FONT size=3D2 =
face=3DArial>Luc</FONT></DIV></BLOCKQUOTE></BODY></HTML>
------=_NextPart_000_0008_01CA808F.6957F090--

Post by Mike H
Hi,
I'd have a seperate fumction and call it to test for the existance of a sheet
Sub nn()
MsgBox SheetThere("sheet3")
End Sub
Function SheetThere(ShtName As String) As Boolean
Dim x
On Error GoTo GetMeOut
x = ActiveWorkbook.Sheets(ShtName).Name
SheetThere = True
Exit Function
SheetThere = False
End Function
Mike

Post by Rick Rothstein
Here is a shorter version of your "on error" method for this function...
Function SheetThere(ShtName As String) As Boolean
On Error Resume Next
SheetThere = Len(ActiveWorkbook.Sheets(ShtName).Name)
End Function
--
Rick (MVP - Excel)
Submitted via EggHeadCafe
Microsoft Silverlight For Beginners
http://www.eggheadcafe.com/training-topic-area/Microsoft-Silverlight/10/Silverlight.aspx