How can I check to see if a sheet exists?

C

Cruzian_Rain Girl

Hello.
I have to be able to write a code in Visual Basic that can check to see if a
sheet exists and post a msgbox saying 'true' or 'false', depending on the
answer.

Example- Check to see if 'Bob' exists.
if it does then Msgbox 'true'

Can anyone help me please?
 
M

Mike

Sub sheetExist()
Dim ws As Worksheet
Dim foundSheet As Boolean
foundSheet = False
For Each ws In Worksheets
If ws.Name = "Sheet10" Then
foundSheet = True
End If
Next ws
MsgBox foundSheet
End Sub
 
C

Chip Pearson

You can write a generic procedure that will work for any worksheet in
any workbook.

Function SheetExists(SheetName As String, _
Optional WB As Workbook) As Boolean
Dim W As Workbook
If WB Is Nothing Then
Set W = ActiveWorkbook
Else
Set W = WB
End If
On Error Resume Next
SheetExists = CBool(Len(W.Worksheets(SheetName).Name))
End Function

Then, you can call it with code like



Dim B As Boolean
B = SheetExists("Sheet1")
If B = True Then
MsgBox "Sheet exists"
Else
MsgBox "Sheet does not exist"
End If

If you omit the WB parameter to SheetExists, the code tests the active
workbook for the presence of the worksheet. You can specify any open
workbook by including the WB parameter:

B = SheetExists("Sheet1",Workbooks("Test.xls"))

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sun, 14 Dec 2008 11:29:01 -0800, Cruzian_Rain Girl <Cruzian_Rain
 
L

Lars-Åke Aspelin

On Sun, 14 Dec 2008 11:29:01 -0800, Cruzian_Rain Girl <Cruzian_Rain
Hello.
I have to be able to write a code in Visual Basic that can check to see if a
sheet exists and post a msgbox saying 'true' or 'false', depending on the
answer.

Example- Check to see if 'Bob' exists.
if it does then Msgbox 'true'

Can anyone help me please?
Click to expand...

Here is another way you may try;

Sub sheet_check()
On Error GoTo sheet_missing
MsgBox Worksheets("Bob").Name = "Bob"
Exit Sub
sheet_missing: MsgBox "false"
End Sub

Hope this helps / Lars-Åke
 
C

Cruzian_Rain Girl

Thank you so so much! The other codes helped a little, but this made it
really simple for me, thank you again!
 
Top