Check if a worksheet exists

Hi all

I am looking for a way to get VBA to check if a worksheet exists using a
named range as the source and if the sheet does not exist then add the
required sheet name... the only examples I have been able to find use the
following code
If SheetEsists("sheetname") = True Then.

However, this does not appear to be a known function within Excel 2000.....
Is there another way or am I missing something?

Regards and Thanks

Mort_kombat

Function SheetExists(ByRef SheetName As String) As Boolean
Dim ws As Worksheet
SheetExists = False
For Each ws In Worksheets
If ws.Name = SheetName Then SheetExists = True
Next
End Function
HTH. --Bruce

"Mort_Komabt" wrote:

> Hi all
>
> I am looking for a way to get VBA to check if a worksheet exists using a
> named range as the source and if the sheet does not exist then add the
> required sheet name... the only examples I have been able to find use the
> following code
> If SheetEsists("sheetname") = True Then.
>
> However, this does not appear to be a known function within Excel 2000.....
> Is there another way or am I missing something?
>
> Regards and Thanks
>
> Mort_kombat

Thanks for that..... It works great

"bpeltzer" wrote:

> Function SheetExists(ByRef SheetName As String) As Boolean
> Dim ws As Worksheet
> SheetExists = False
> For Each ws In Worksheets
> If ws.Name = SheetName Then SheetExists = True
> Next
> End Function
> HTH. --Bruce
>
> "Mort_Komabt" wrote:
>
> > Hi all
> >
> > I am looking for a way to get VBA to check if a worksheet exists using a
> > named range as the source and if the sheet does not exist then add the
> > required sheet name... the only examples I have been able to find use the
> > following code
> > If SheetEsists("sheetname") = True Then.
> >
> > However, this does not appear to be a known function within Excel 2000.....
> > Is there another way or am I missing something?
> >
> > Regards and Thanks
> >
> > Mort_kombat

Hi Mort,

You could add a new sheet automatically if the value in your named range
changes. Try this in the worksheet module of the changing cell.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim sName As String
Dim bShtAdded As Boolean

If Target(1).Address = Range("myName").Address Then
sName = Range("myName").Value
If Len(sName) > 1 Then
On Error Resume Next
Set ws = Worksheets(sName)
On Error GoTo errH
If ws Is Nothing Then
Set ws = Worksheets.Add
bShtAdded = True
ws.Name = sName
End If
End If
End If

Exit Sub
errH:
If bShtAdded Then
MsgBox "Cannot name new sheet : " & sName
End If
End Sub

If your named range is a formla cell, amend the above to check for changes
in whatever value cell changes the result in your formula, eg your formula
=A!

If Target(1).Address = "$A$1" Then

If you only want to add a new sheet by calling a normal macro,

Sub MyMacro()
' all the code as above but delete
If Target(1).Address = Range("myName").Address Then

end if

End Sub

Regards,
Peter T

"Mort_Komabt" <[email protected]> wrote in message
news:[email protected]...
> Hi all
>
> I am looking for a way to get VBA to check if a worksheet exists using a
> named range as the source and if the sheet does not exist then add the
> required sheet name... the only examples I have been able to find use the
> following code
> If SheetEsists("sheetname") = True Then.
>
> However, this does not appear to be a known function within Excel
2000.....
> Is there another way or am I missing something?
>
> Regards and Thanks
>
> Mort_kombat

More efficiently


'-----------------------------------------------------------------
Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mort_Komabt" <[email protected]> wrote in message
news:[email protected]...
> Thanks for that..... It works great
>
> "bpeltzer" wrote:
>
> > Function SheetExists(ByRef SheetName As String) As Boolean
> > Dim ws As Worksheet
> > SheetExists = False
> > For Each ws In Worksheets
> > If ws.Name = SheetName Then SheetExists = True
> > Next
> > End Function
> > HTH. --Bruce
> >
> > "Mort_Komabt" wrote:
> >
> > > Hi all
> > >
> > > I am looking for a way to get VBA to check if a worksheet exists using
a
> > > named range as the source and if the sheet does not exist then add the
> > > required sheet name... the only examples I have been able to find use
the
> > > following code
> > > If SheetEsists("sheetname") = True Then.
> > >
> > > However, this does not appear to be a known function within Excel
2000.....
> > > Is there another way or am I missing something?
> > >
> > > Regards and Thanks
> > >
> > > Mort_kombat