About Us

freddy

Thanks to all who have assisted me. Good show!

"Tushar Mehta" wrote:

Others have focused on helping you resolve your problem as you framed it.
Here's a different take on how you could (should, IMO) approach it. It also
connects to your initial question about the On Error clause.

To test a worksheet exists, with error handling all you need is
Option Explicit

Function ActiveWBSheetExists(aSheetName As String) As Boolean
On Error Resume Next
SheetExists = Not (ActiveWorkbook.Sheets(aSheetName) Is Nothing)
End Function

or the more general purpose routine
Function SheetExists(aSheetName As String, _
Optional aWB As Workbook) As Boolean
If aWB Is Nothing Then Set aWB = ActiveWorkbook
On Error Resume Next
SheetExists = Not (aWB.Sheets(aSheetName) Is Nothing)
End Function

So, to print a sheet if it exists, one would add the procedu

Sub printIfPresent()
If SheetExists("sheet 1") Then ActiveWorkbook.Sheets("sheet 1").PrintOut
End Sub

But, that leads to the next improvement. Why bother testing if a sheet
exists? Why not simply print it if it's possible with

Sub PrintIfPossible()
On Error Resume Next
ActiveWorkbook.Sheets("sheet 1").PrintOut
End Sub

--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu

"Freddy" wrote:

Chip, I think I moved ahead a bit. The following code executes but does not
print anything and there is a worksheet named "Invoice" in the active
workbook and there is a working network printer. Please review and advise.

Public Function bExists(s As String)
For Each sh In ActiveWorkbook.Worksheets
If LCase(s) = LCase(sh.Name) Then
bExists = True
Exit Function
End If
Next
bExists = False
End Function

'Usage of Function named bExists
Sub InvoiceCheck()
If bExists("Invoice") Then
Worksheets("Invoice").PrintOut
Else
End If
End Sub

"Chip Pearson" wrote:

Both Sub and Function are generally called procedures, and you cannot nest
one procedure within another. You must structure the code as

Sub A()
' blah blah blah
End Sub
Function X()
' blah blah
End Function

A Function or Sub statement may not occur within the body of another
procedure.

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

"Freddy" wrote in message
...
Thank you for that explanation. By the way, can you respond to another
question that apparently Tom Ogilvy has not had time to review?

I do not understand how the relationship works between the Public Function
code and the usage portion of your sample code. Can I not use the Function
within a sub procedure? If I attempt to run the sub below I get an error
which reads: Compile error: Expected End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''
Sub InvoiceCheck()
Public Function bExists(s As String)
For Each sh In ActiveWorkbook.Worksheets
If LCase(s) = LCase(sh.Name) Then
bExists = True
Exit Function
End If
Next
bExists = False
End Function

'usage
If bExists("sheet1") Then
Worksheets("sheet1").PrintOut
Else

End If
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''

"Jim Thomlinson" wrote:

On Error GoTo 0

Return to the default error handling in VBA. Most instances when you see
this are the result of setting the system to ignor errors while a
potentially
problematic line of code executes. After that line is passed you want the
system to start catching errors again so you turn error handling back on
to
the default settings.
--
HTH...

Jim Thomlinson

"Freddy" wrote:

What does "On Error GoTo 0" mean? I see this on many of the posts.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
On Error Goto 0	Chip Pearson	Excel Programming	0	January 6th 07 12:04 AM
On Error Goto 0	Bob Phillips	Excel Programming	0	January 4th 07 09:46 PM
Error Handling - On Error GoTo doesn't trap error successfully	David	Excel Programming	9	February 16th 06 05:59 PM
On Error Goto doesn't goto	Paul	Excel Programming	1	October 15th 04 03:51 PM
On Error Goto doesn't goto	Paul	Excel Programming	0	October 15th 04 03:05 PM

Menu

About Us