While working on various sheets some times i get a error message if the sheet
is not there i would like to check prior to activating it. Is there any
command available to check the sheets e.g. book7 or book9 etc.

Vijay,

Use a function like

Function SheetExists(SheetName As String, Optional WBook As Workbook) As
Boolean
On Error Resume Next
SheetExists = CBool(Len(IIf(WBook Is Nothing, _
ThisWorkbook, WBook).Sheets(SheetName).Name))
End Function

You can then call this as

If SheetExists("Sheet1") = True Then

or

If SheetExists("Sheet1", Workbooks("Book2.xls")) = True Then

If you omit the workbook reference, it tests for the sheet in the same
workbook that contains the code, as shown in the first example. To test for
a sheet in another workbook, include the workbook reference as shown in the
second example.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)



"Vijay Kotian" <(E-Mail Removed)> wrote in message
news:117C1ECE-0B70-4C23-B6F5-(E-Mail Removed)...
> While working on various sheets some times i get a error message if the
> sheet
> is not there i would like to check prior to activating it. Is there any
> command available to check the sheets e.g. book7 or book9 etc.

Sub wbtest()
Dim w As Workbook
Dim s As String
s = "Book"
For Each w In Workbooks
If w.Name = s Then
MsgBox (w.Name & " is open")
Exit Sub
End If
Next
MsgBox (s & " is not open")
End Sub


--
Gary's Student


"Vijay Kotian" wrote:

> While working on various sheets some times i get a error message if the sheet
> is not there i would like to check prior to activating it. Is there any
> command available to check the sheets e.g. book7 or book9 etc.

The most efficient way is to trap the resulting error and process
accordingly, e.g.

On Error Resume Next
Sheets("book7").Activate
If Err.Number = 9 then
Msgbox "book7 not found"
End If
On Error Goto 0

If this is not desired then you will have to loop through all sheets in the
active workbook to see if one of them is named "book7"

Function WorkSheetExists(strName as String) As Boolean
Dim w as Object

For Each w In ActiveWorkbook.Sheets
If w.Name = strName Then
WorkSheetExists = True
End If
Next w
End Function

Cheers,
Dave
"Vijay Kotian" wrote:

> While working on various sheets some times i get a error message if the sheet
> is not there i would like to check prior to activating it. Is there any
> command available to check the sheets e.g. book7 or book9 etc.

Hi,

I need to check only once (not loop) whether particular sheet is already
opened or not. I would like to use If else condition based on above
mentioned sheet. How can i do this.

I have tried your code and found that even if the file is not there is
proceeds instead of going to endif.

Pl. help.

Regards

"Dave Ramage" wrote:

> The most efficient way is to trap the resulting error and process
> accordingly, e.g.
>
> On Error Resume Next
> Sheets("book7").Activate
> If Err.Number = 9 then
> Msgbox "book7 not found"
> End If
> On Error Goto 0
>
> If this is not desired then you will have to loop through all sheets in the
> active workbook to see if one of them is named "book7"
>
> Function WorkSheetExists(strName as String) As Boolean
> Dim w as Object
>
> For Each w In ActiveWorkbook.Sheets
> If w.Name = strName Then
> WorkSheetExists = True
> End If
> Next w
> End Function
>
> Cheers,
> Dave
> "Vijay Kotian" wrote:
>
> > While working on various sheets some times i get a error message if the sheet
> > is not there i would like to check prior to activating it. Is there any
> > command available to check the sheets e.g. book7 or book9 etc.