Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If page does not exist
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exist or Not. | Excel Discussion (Misc queries) | |||
Am I Looking For Something That Doesn't Exist? | Excel Discussion (Misc queries) | |||
BLANK EXCEL PAGE STILL EXIST AFTER I CLOSE OUT SPREADSHEET? | New Users to Excel | |||
count how often a name exist | Excel Worksheet Functions | |||
Does worksheet exist | Excel Programming |