Detect existence of a sheet
 

Hi,

I'm having problems when trying to acess a sheet in a workbook if this sheet
does'nt exist. I tried some "On Error" tricks....but it dodn't worked.
Here is the piece of code where I try to activate the sheet:
....
genesis_wb.Activate
genesis_wb.Sheets(nome_al).Activate 'nome_al is a string
....

Is there a way to check if the sheet(nome_al) exist before activating it?
(without on error techniques please).

Thanks in advance!

Cleber

Detect existence of a sheet
 

I'm didnt search in past topics for a answer before posting...sorry. i found
this function:

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0)
End Function


i will test it.

"Cleber Inacio" escreveu:

Hi,

I'm having problems when trying to acess a sheet in a workbook if this sheet
does'nt exist. I tried some "On Error" tricks....but it dodn't worked.
Here is the piece of code where I try to activate the sheet:
...
genesis_wb.Activate
genesis_wb.Sheets(nome_al).Activate 'nome_al is a string
...

Is there a way to check if the sheet(nome_al) exist before activating it?
(without on error techniques please).

Thanks in advance!

Cleber

Detect existence of a sheet
 

Here is a function which you would use like this...

sub test()
if SheetExists(nome_al, genesis_wb) then
genesis_wb.Activate
genesis_wb.Sheets(nome_al).Activate 'nome_al is a string
end if
end sub

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function
--
HTH...

Jim Thomlinson


"Cleber Inacio" wrote:

Hi,

I'm having problems when trying to acess a sheet in a workbook if this sheet
does'nt exist. I tried some "On Error" tricks....but it dodn't worked.
Here is the piece of code where I try to activate the sheet:
...
genesis_wb.Activate
genesis_wb.Sheets(nome_al).Activate 'nome_al is a string
...

Is there a way to check if the sheet(nome_al) exist before activating it?
(without on error techniques please).

Thanks in advance!

Cleber

Detect existence of a sheet
 

I'm having problems when trying to acess a sheet in a workbook if this
sheet
does'nt exist. I tried some "On Error" tricks....but it dodn't worked.
Here is the piece of code where I try to activate the sheet:
...
genesis_wb.Activate
genesis_wb.Sheets(nome_al).Activate 'nome_al is a string
...

Is there a way to check if the sheet(nome_al) exist before activating it?
(without on error techniques please).

Since you asked for a solution that doesn't use On Error techniques, here is
a function that should do that for you (the header is modeled after the
function from Chip Pearson that Cleber Inacio posted)...

Public Function SheetExists(SheetName As String, _
Optional ByVal WB As Workbook) As Boolean
Dim WS As Worksheet
If WB Is Nothing Then Set WB = ThisWorkbook
For Each WS In WB.Sheets
If StrComp(WS.Name, SheetName, vbTextCompare) = 0 Then
SheetExists = True
Exit For
End If
Next
End Function

However, I just want to point out that there is nothing wrong with using On
Error techniques to help in coding. Perhaps you are put off by the word
"error"; but you shouldn't be, there is nothing inherently "bad" about using
On Error techniques to filter out undesirable results... it's just another
way to get information for your code to work with.

Rick

Detect existence of a sheet
 

I agree whole heartedly. That being said I understand Clerb's reluctance. It
is almost always better to avoid the error than it is to deal with an error.
It all depends on the nature of the error. Before you start using an error
handler you need to fully understand the error that is being generated and
determine if you could reasonably avoid it.
--
HTH...

Jim Thomlinson


"Rick Rothstein (MVP - VB)" wrote:

I'm having problems when trying to acess a sheet in a workbook if this
sheet
does'nt exist. I tried some "On Error" tricks....but it dodn't worked.
Here is the piece of code where I try to activate the sheet:
...
genesis_wb.Activate
genesis_wb.Sheets(nome_al).Activate 'nome_al is a string
...

Is there a way to check if the sheet(nome_al) exist before activating it?
(without on error techniques please).

Since you asked for a solution that doesn't use On Error techniques, here is
a function that should do that for you (the header is modeled after the
function from Chip Pearson that Cleber Inacio posted)...

Public Function SheetExists(SheetName As String, _
Optional ByVal WB As Workbook) As Boolean
Dim WS As Worksheet
If WB Is Nothing Then Set WB = ThisWorkbook
For Each WS In WB.Sheets
If StrComp(WS.Name, SheetName, vbTextCompare) = 0 Then
SheetExists = True
Exit For
End If
Next
End Function

However, I just want to point out that there is nothing wrong with using On
Error techniques to help in coding. Perhaps you are put off by the word
"error"; but you shouldn't be, there is nothing inherently "bad" about using
On Error techniques to filter out undesirable results... it's just another
way to get information for your code to work with.

Rick

Detect existence of a sheet
 

Sorry Cleber. I spelled your name incorrectly in my last post... Purely
unintentional.
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

I agree whole heartedly. That being said I understand Clerb's reluctance. It
is almost always better to avoid the error than it is to deal with an error.
It all depends on the nature of the error. Before you start using an error
handler you need to fully understand the error that is being generated and
determine if you could reasonably avoid it.
--
HTH...

Jim Thomlinson


"Rick Rothstein (MVP - VB)" wrote:

I'm having problems when trying to acess a sheet in a workbook if this
sheet
does'nt exist. I tried some "On Error" tricks....but it dodn't worked.
Here is the piece of code where I try to activate the sheet:
...
genesis_wb.Activate
genesis_wb.Sheets(nome_al).Activate 'nome_al is a string
...

Is there a way to check if the sheet(nome_al) exist before activating it?
(without on error techniques please).

Since you asked for a solution that doesn't use On Error techniques, here is
a function that should do that for you (the header is modeled after the
function from Chip Pearson that Cleber Inacio posted)...

Public Function SheetExists(SheetName As String, _
Optional ByVal WB As Workbook) As Boolean
Dim WS As Worksheet
If WB Is Nothing Then Set WB = ThisWorkbook
For Each WS In WB.Sheets
If StrComp(WS.Name, SheetName, vbTextCompare) = 0 Then
SheetExists = True
Exit For
End If
Next
End Function

However, I just want to point out that there is nothing wrong with using On
Error techniques to help in coding. Perhaps you are put off by the word
"error"; but you shouldn't be, there is nothing inherently "bad" about using
On Error techniques to filter out undesirable results... it's just another
way to get information for your code to work with.

Rick