ExcelBanter - check whether worksheet's there or not

Hi
You can test with this function, which applies to the Active WorkBook

'Tests to see if a worksheet with the given name exists in the active workbook
Function IsSheetThere(shName As String) As Boolean
Dim DummyWks As String
IsSheetThere = False
On Error Resume Next
DummyWks = ActiveWorkbook.Worksheets(shName).Name
If Err.Number = 0 Then IsSheetThere = True
End Function

Usage:
If not IsSheetThere("Results") then
Sheets.Add
ActiveSheet.Name = "Results"
End If

Other handy functions/subs in the same stable a

Function IsNameInWorkBook(stName As String) As Boolean
'Tests to see if the name stName exists in this workbook
'Adapted from sub in John Green's book

Dim x As String
IsNameInWorkBook = False
On Error Resume Next
x = ActiveWorkbook.Names(stName).Name
If Err.Number = 0 Then IsNameInWorkBook = True
End Function

Public Sub AddSheet(TheSheetName As String)
'Replace an existing sheet or insert if not there

Dim WsNew As Worksheet 'we will insert a fresh worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets(TheSheetName).Delete
On Error GoTo 0
Application.DisplayAlerts = True
'If worksheet is not there the error in deletion is ignored
'Add a new sheet
Set WsNew = Worksheets.Add 'Makes wsNew the active sheet
WsNew.Name = TheSheetName
Set WsNew = Nothing
End Sub

Public Function IsFileOpen(filename As String)
'Lifted from Microsoft KB
' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns
' False. Otherwise, a run-time error will occur because there is
' some other problem accessing the file.

Dim filenum As Integer, errnum As Integer

On Error Resume Next ' Turn error checking off.
filenum = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum ' Close the file.
errnum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.
' Check to see which error occurred.
Select Case errnum
' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False
' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True
' Another error occurred.
Case Else
IsFileOpen = False
End Select
End Function

Function IsWorkBookOpen(WorkBookName As String) As Boolean
'See Green p81. Checks workbook is open on the machine
Dim Wkb As Workbook
On Error Resume Next
Set Wkb = Workbooks(WorkBookName)
If Not Wkb Is Nothing Then
IsWorkBookOpen = True
End If
Set Wkb = Nothing
End Function

regards
Paul
fabalicious wrote in message ...
Hi ya

I want to activate a certain Worksheet 'Results' using a macro. Because
I don't know for you whether it exists yet, I want to check that with
an If-statement and then act accordingly (create the sheet if it's not
there yet). The expression below (.Activate) doesn't work, nor did
using 'Not Sheets(...) Is Nothing' but I look for something of this
kind as I would like to avoid a loop to check for the sheets'
existence

If (Sheets("Results").Activate) Then
..
Else
Sheets.Add
ActiveSheet.Name = "Results"
End If

Any ideas are highly appreciated, cheers!

fabalicious

---
Message posted from http://www.ExcelForum.com/