Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default If page does not exist

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?
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,163
Default If page does not exist

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

"Aaron Howe" wrote:

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?

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 23
Default If page does not exist

"K Dales" wrote:

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

"Aaron Howe" wrote:

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?


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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,163
Default If page does not exist

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.

"Aaron Howe" wrote:

"K Dales" wrote:

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

"Aaron Howe" wrote:

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?


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



  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 23
Default If page does not exist

That works - thanks!

"Aaron Howe" wrote:

"K Dales" wrote:

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

"Aaron Howe" wrote:

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?


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

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,080
Default If page does not exist

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.

--

Vasant


"K Dales" wrote in message
...
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.

"Vasant Nanavati" wrote:

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

--

Vasant

"Aaron Howe" <Aaron wrote in message
...
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?






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Exist or Not. ldiaz Excel Discussion (Misc queries) 3 September 6th 06 09:31 PM
Am I Looking For Something That Doesn't Exist? seanryann Excel Discussion (Misc queries) 11 April 1st 06 06:10 PM
BLANK EXCEL PAGE STILL EXIST AFTER I CLOSE OUT SPREADSHEET? still learning New Users to Excel 2 May 22nd 05 11:50 AM
count how often a name exist hans Excel Worksheet Functions 4 January 27th 05 02:44 PM
Does worksheet exist Robert S Excel Programming 3 May 18th 04 07:59 PM


All times are GMT +1. The time now is 10:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"