ExcelBanter - How to check if a sheet exists ?

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..

On Saturday, December 19, 2009 9:16 AM Luc wrote:

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=3DArialTitle says it all.</FONT</DIV
<DIV<FONT size=3D2 face=3DArialLuc</FONT</DIV</BODY</HTML

------=_NextPart_000_0049_01CA80BE.43826100--

On Saturday, December 19, 2009 9:41 AM JLGWhiz wrote:

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

Courtexy Chip Pearson:

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=3DArialCourtexy Chip Pearson:</FONT</DIV
<DIV<FONT size=3D2 face=3DArial</FONT </DIV
<DIVFunction 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))<BREnd 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
dir=3Dltr
<DIV"Luc" <<A </A> =
wrote in=20
message <A=20
=
<DIV<FONT size=3D2 face=3DArialTitle says it all.</FONT</DIV
<DIV<FONT size=3D2 =
face=3DArialLuc</FONT</DIV</BLOCKQUOTE</BODY</HTML

------=_NextPart_000_0008_01CA808F.6957F090--

On Saturday, December 19, 2009 10:08 AM Mike H wrote:

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

GetMeOut:
SheetThere = False
End Function

Mike

"Luc" wrote:

On Saturday, December 19, 2009 10:22 AM Rick Rothstein wrote:

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-...lverlight.aspx