Excel VBA - does a worksheet exist

[Skob]

I'm trying to write some code that determines whether a worksheet exists and directs the user to different user forms depending on whether it does or it doesn't. The trouble is I can't seem to find a way of checking the existence of a sheet in a workbook. HELP!

The code I am using is something like

Private Sub WorkSheetCheck()

If IsObject(Sheets("DataEntrySheet")  Then
    ReminderForm.Show
Else
    DataEntryForm.Show
End If

End Sub

The IsObject doesn't work, along with many other variations I have tried.

I'm sure someone must have done this before ...

Andy

 

[SkipVought]

Hi,

Try this...

for each ws in activeworkbook.worksheets
  if ws.name = TheName Then
   'Houston, we have a match
...
   exit for
  end if
next

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Loomah]

Hi
Another way of seeing if a sheet exists

On Error Resume Next
Worksheets("NoSuchSheet").Activate
If Err.Number <> 0 Then MsgBox &quot;sheet doesn't exist!&quot;
On Error GoTo 0

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244