If page does not exist

[Guest]

I have a small piece of code in an Excel sheet which validates whether a
field has been completed and then allow a print if it has been. Here's the
code:

Private Sub CheckPrint_Click()
If Worksheets("Checks").Range("D33").Value = "No" Then
ActiveSheet.PrintOut
Else
MsgBox "There is an error on form - check all required fields (marked in
red) have been completed properly and try again"
End If
End Sub

- Problem is, users are saving a copy of the main sheet (SOS) and then
printing - hence they get an error as the macro cannot complete if they only
save the SOS worksheet (the Checks sheet is hidden). Is there a way of
telling Excel that if the page does not exist, carry on to complete the task
(printing) anyway?

 

[Guest]

Dim WName as String, ChecksThere as Boolean
ChecksThere = False
On Error Goto NoChecks
WName = Worksheets("Checks").Name
ChecksThere = True
NoChecks:
On Error Goto 0
If ChecksThere Then
' Run your current code to print the sheet
Else
' Print only the main sheet
End If

 

[Vasant Nanavati]

I think there is some confusion here. How can a user save only a single
sheet?

 

[Guest]

Dim WName as String, ChecksThere as Boolean
ChecksThere = False
On Error Goto NoChecks
WName = Worksheets("Checks").Name
ChecksThere = True
NoChecks:
On Error Goto 0
If ChecksThere Then
' Run your current code to print the sheet
Else
' Print only the main sheet
End If

I'm not sure if this would do what I was intending. The ActiveSheet is
"SOS", "Checks" is hidden in the background and actually validates the sheet
itself through a series of Yes/No checks culminating in an absolute response.
This option above would only let me print one sheet or the other, and I'm
not sure how to amend it to my needs - would it be as simple as pasting my
code to either print or warn into the area where you have commented "' Run
your current code to print the sheet"...? I tried to add it in but I don't
think my syntax is correct.

In answer to the question from Vasant, it can be done by opening another
workbook and copying the sheet itself into the new workbook

 

[Guest]

Yes, although I was sloppy with the wording of my response, the intent was
simply to see if the "Checks" sheet is there; if so run the existing code to
print the main sheet (which works fine as long as Checks is there), otherwise
write the new code to print the main sheet without first checking to see if
Checks cell D33 was no. So in more detail it would be:
Dim WName as String, ChecksThere as Boolean
ChecksThere = False
On Error Goto NoChecks
WName = Worksheets("Checks").Name
ChecksThere = True
NoChecks:
On Error Goto 0
If ChecksThere Then
If Worksheets("Checks").Range("D33").Value = "No" Then
ActiveSheet.PrintOut
Else
MsgBox "There is an error on form - check all required fields (marked
in red) have been completed properly and try again"
End If
Else
ActiveSheet.Printout
End If

Hopefully I got all the coding right, but I think that should do what you
want.

 

[Guest]

In addition to Aaron's answer, the user could right-click on the worksheet
tab, select "Move or Copy..." and then copy to a new workbook.

 

[Guest]

That works - thanks!

I'm not sure if this would do what I was intending. The ActiveSheet is
"SOS", "Checks" is hidden in the background and actually validates the sheet
itself through a series of Yes/No checks culminating in an absolute response.
This option above would only let me print one sheet or the other, and I'm
not sure how to amend it to my needs - would it be as simple as pasting my
code to either print or warn into the area where you have commented "' Run
your current code to print the sheet"...? I tried to add it in but I don't
think my syntax is correct.

In answer to the question from Vasant, it can be done by opening another
workbook and copying the sheet itself into the new workbook

 

[Vasant Nanavati]

Of course I understand how users could save a worksheet separately. However,
it's not common practice and I wanted to make sure that there was no
confusion between workbooks and worksheets in the OP's mind.