Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned macro in Dialog giving error
Hello,
I have a Dialog box which contains 3 Check Boxes & 2 Buttons. I am using this code to bring up the Dialog. Sub Dialog() ' DialogSheets("Dialog1").Show End Sub The 3 Check Boxes place a TRUE or FALSE in specific cells depending on if the box is checked and the 2 Buttons are PRINT & Cancel. I have assigned this code to the PRINT button. Sub PRNT() ' Sheets("Sheet2").Select If Range("A1") = True Then MyString = Sheets("Sheet1").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A2") = True Then MyString = Sheets("Sheet2").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A3") = True Then MyString = Sheets("Sheet3").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If MsgBox "All checked boxes have been printed." End Sub The problem comes when any of the Check Boxes is checked and the above macro is activated by clicking PRINT in the Dialog. I get; Method 'PrintOut' of object 'Sheets' failed I've looked at all of the attributes I know of, which is admittedly few, and tried finding a solution via MS Help to no avail. Can some one help me with this? I want to be able to print only the selected sheets based on which Check Box has a check in it by clicking my PRINT Button in my Dialog. Thank you for your help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned macro in Dialog giving error
Try this:
Sub PRNT() With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = True Then Sheets("Sjeet1").PrintOut ElseIf c.Address = "$A$2" And c = True Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = True Then Sheets("Sheet3").Printout End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub "SJW_OST" wrote: Hello, I have a Dialog box which contains 3 Check Boxes & 2 Buttons. I am using this code to bring up the Dialog. Sub Dialog() ' DialogSheets("Dialog1").Show End Sub The 3 Check Boxes place a TRUE or FALSE in specific cells depending on if the box is checked and the 2 Buttons are PRINT & Cancel. I have assigned this code to the PRINT button. Sub PRNT() ' Sheets("Sheet2").Select If Range("A1") = True Then MyString = Sheets("Sheet1").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A2") = True Then MyString = Sheets("Sheet2").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A3") = True Then MyString = Sheets("Sheet3").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If MsgBox "All checked boxes have been printed." End Sub The problem comes when any of the Check Boxes is checked and the above macro is activated by clicking PRINT in the Dialog. I get; Method 'PrintOut' of object 'Sheets' failed I've looked at all of the attributes I know of, which is admittedly few, and tried finding a solution via MS Help to no avail. Can some one help me with this? I want to be able to print only the selected sheets based on which Check Box has a check in it by clicking my PRINT Button in my Dialog. Thank you for your help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned macro in Dialog giving error
Additional info on print command: The settings you included in your code
were all default settings and are unnecessary if you are printing to your personal printer. However, if you are printing to a network printer, it might be wise to include the copy and colate settings since you don't know what the user in front of you had for settings, and it takes several seconds for the printer to automatically reset. "SJW_OST" wrote: Hello, I have a Dialog box which contains 3 Check Boxes & 2 Buttons. I am using this code to bring up the Dialog. Sub Dialog() ' DialogSheets("Dialog1").Show End Sub The 3 Check Boxes place a TRUE or FALSE in specific cells depending on if the box is checked and the 2 Buttons are PRINT & Cancel. I have assigned this code to the PRINT button. Sub PRNT() ' Sheets("Sheet2").Select If Range("A1") = True Then MyString = Sheets("Sheet1").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A2") = True Then MyString = Sheets("Sheet2").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A3") = True Then MyString = Sheets("Sheet3").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If MsgBox "All checked boxes have been printed." End Sub The problem comes when any of the Check Boxes is checked and the above macro is activated by clicking PRINT in the Dialog. I get; Method 'PrintOut' of object 'Sheets' failed I've looked at all of the attributes I know of, which is admittedly few, and tried finding a solution via MS Help to no avail. Can some one help me with this? I want to be able to print only the selected sheets based on which Check Box has a check in it by clicking my PRINT Button in my Dialog. Thank you for your help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned macro in Dialog giving error
I used the code you gave but still got the Error
PrintOut method of Worksheet class failed I've noticed that when I "F8" thru my code, starting with Sub Dialog() ' DialogSheets("Dialog1").Show End Sub The above code does not end until the Dialog box closes. Could this be a part of what is causeing the problem? The PRNT code you gave works if I use it by itself but when assigned to the PRINT Button on the Dialog and activated by clicking he Button is when the error occurs. "JLGWhiz" wrote: Try this: Sub PRNT() With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = True Then Sheets("Sjeet1").PrintOut ElseIf c.Address = "$A$2" And c = True Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = True Then Sheets("Sheet3").Printout End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub "SJW_OST" wrote: Hello, I have a Dialog box which contains 3 Check Boxes & 2 Buttons. I am using this code to bring up the Dialog. Sub Dialog() ' DialogSheets("Dialog1").Show End Sub The 3 Check Boxes place a TRUE or FALSE in specific cells depending on if the box is checked and the 2 Buttons are PRINT & Cancel. I have assigned this code to the PRINT button. Sub PRNT() ' Sheets("Sheet2").Select If Range("A1") = True Then MyString = Sheets("Sheet1").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A2") = True Then MyString = Sheets("Sheet2").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A3") = True Then MyString = Sheets("Sheet3").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If MsgBox "All checked boxes have been printed." End Sub The problem comes when any of the Check Boxes is checked and the above macro is activated by clicking PRINT in the Dialog. I get; Method 'PrintOut' of object 'Sheets' failed I've looked at all of the attributes I know of, which is admittedly few, and tried finding a solution via MS Help to no avail. Can some one help me with this? I want to be able to print only the selected sheets based on which Check Box has a check in it by clicking my PRINT Button in my Dialog. Thank you for your help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned macro in Dialog giving error
I just noticed that I have a typo in the code and forgot some quote marks.
This fixes that: Sub PRNT() Dim c As Range With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = "True" Then Sheets("Sheet1").PrintOut ElseIf c.Address = "$A$2" And c = "True" Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = "True" Then Sheets("Sheet3").PrintOut End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub "SJW_OST" wrote: I used the code you gave but still got the Error PrintOut method of Worksheet class failed I've noticed that when I "F8" thru my code, starting with Sub Dialog() ' DialogSheets("Dialog1").Show End Sub The above code does not end until the Dialog box closes. Could this be a part of what is causeing the problem? The PRNT code you gave works if I use it by itself but when assigned to the PRINT Button on the Dialog and activated by clicking he Button is when the error occurs. "JLGWhiz" wrote: Try this: Sub PRNT() With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = True Then Sheets("Sjeet1").PrintOut ElseIf c.Address = "$A$2" And c = True Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = True Then Sheets("Sheet3").Printout End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub "SJW_OST" wrote: Hello, I have a Dialog box which contains 3 Check Boxes & 2 Buttons. I am using this code to bring up the Dialog. Sub Dialog() ' DialogSheets("Dialog1").Show End Sub The 3 Check Boxes place a TRUE or FALSE in specific cells depending on if the box is checked and the 2 Buttons are PRINT & Cancel. I have assigned this code to the PRINT button. Sub PRNT() ' Sheets("Sheet2").Select If Range("A1") = True Then MyString = Sheets("Sheet1").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A2") = True Then MyString = Sheets("Sheet2").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A3") = True Then MyString = Sheets("Sheet3").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If MsgBox "All checked boxes have been printed." End Sub The problem comes when any of the Check Boxes is checked and the above macro is activated by clicking PRINT in the Dialog. I get; Method 'PrintOut' of object 'Sheets' failed I've looked at all of the attributes I know of, which is admittedly few, and tried finding a solution via MS Help to no avail. Can some one help me with this? I want to be able to print only the selected sheets based on which Check Box has a check in it by clicking my PRINT Button in my Dialog. Thank you for your help. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned macro in Dialog giving error
It still gives the same error.
"JLGWhiz" wrote: I just noticed that I have a typo in the code and forgot some quote marks. This fixes that: Sub PRNT() Dim c As Range With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = "True" Then Sheets("Sheet1").PrintOut ElseIf c.Address = "$A$2" And c = "True" Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = "True" Then Sheets("Sheet3").PrintOut End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub "SJW_OST" wrote: I used the code you gave but still got the Error PrintOut method of Worksheet class failed I've noticed that when I "F8" thru my code, starting with Sub Dialog() ' DialogSheets("Dialog1").Show End Sub The above code does not end until the Dialog box closes. Could this be a part of what is causeing the problem? The PRNT code you gave works if I use it by itself but when assigned to the PRINT Button on the Dialog and activated by clicking he Button is when the error occurs. "JLGWhiz" wrote: Try this: Sub PRNT() With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = True Then Sheets("Sjeet1").PrintOut ElseIf c.Address = "$A$2" And c = True Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = True Then Sheets("Sheet3").Printout End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub "SJW_OST" wrote: Hello, I have a Dialog box which contains 3 Check Boxes & 2 Buttons. I am using this code to bring up the Dialog. Sub Dialog() ' DialogSheets("Dialog1").Show End Sub The 3 Check Boxes place a TRUE or FALSE in specific cells depending on if the box is checked and the 2 Buttons are PRINT & Cancel. I have assigned this code to the PRINT button. Sub PRNT() ' Sheets("Sheet2").Select If Range("A1") = True Then MyString = Sheets("Sheet1").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A2") = True Then MyString = Sheets("Sheet2").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A3") = True Then MyString = Sheets("Sheet3").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If MsgBox "All checked boxes have been printed." End Sub The problem comes when any of the Check Boxes is checked and the above macro is activated by clicking PRINT in the Dialog. I get; Method 'PrintOut' of object 'Sheets' failed I've looked at all of the attributes I know of, which is admittedly few, and tried finding a solution via MS Help to no avail. Can some one help me with this? I want to be able to print only the selected sheets based on which Check Box has a check in it by clicking my PRINT Button in my Dialog. Thank you for your help. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned macro in Dialog giving error
1. I assume the Dialog Box you are referring to is a UserForm.
2. I am also assuming that the buttons and checkboxes were created from the Control Toolbox in the VBE. 3. Based on these two assumptions I am wondering why your macro name is not part of a click event like: Private Sub CommandButton1_Click() and then the code. 4. Here is what I did. I set up a UserForm1. I put three checkboxes on the form using the Control Toolbox from VBE. I put a command button on the form from the Control Toolbox from VBE. I used this code in the public code module of the project: Sub ufshow() UserForm1.Show End Sub Then I put this code behind the command button: Private Sub CommandButton1_Click() Dim c As Range With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = "True" Then Sheets("Sheet1").PrintOut ElseIf c.Address = "$A$2" And c = "True" Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = "True" Then Sheets("Sheet3").PrintOut End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub Then, in consecutive order of the three checkboxes, I entered the control source as Sheet2!a1, Sheet2!a2 and Sheet2!a3 respectively. I then tested this configuration by initilizing the first macro. It displayed the userform with the checkboxes and the command button. I clicked one of the checkboxes and then clicked the command button. It immediately found the sheet for the checkbox that was clicked and initiated the print process. Maybe this will help you to find where your glitch is. I cannot find it from what you have shown in the postings. "SJW_OST" wrote: It still gives the same error. "JLGWhiz" wrote: I just noticed that I have a typo in the code and forgot some quote marks. This fixes that: Sub PRNT() Dim c As Range With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = "True" Then Sheets("Sheet1").PrintOut ElseIf c.Address = "$A$2" And c = "True" Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = "True" Then Sheets("Sheet3").PrintOut End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub "SJW_OST" wrote: I used the code you gave but still got the Error PrintOut method of Worksheet class failed I've noticed that when I "F8" thru my code, starting with Sub Dialog() ' DialogSheets("Dialog1").Show End Sub The above code does not end until the Dialog box closes. Could this be a part of what is causeing the problem? The PRNT code you gave works if I use it by itself but when assigned to the PRINT Button on the Dialog and activated by clicking he Button is when the error occurs. "JLGWhiz" wrote: Try this: Sub PRNT() With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = True Then Sheets("Sjeet1").PrintOut ElseIf c.Address = "$A$2" And c = True Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = True Then Sheets("Sheet3").Printout End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub "SJW_OST" wrote: Hello, I have a Dialog box which contains 3 Check Boxes & 2 Buttons. I am using this code to bring up the Dialog. Sub Dialog() ' DialogSheets("Dialog1").Show End Sub The 3 Check Boxes place a TRUE or FALSE in specific cells depending on if the box is checked and the 2 Buttons are PRINT & Cancel. I have assigned this code to the PRINT button. Sub PRNT() ' Sheets("Sheet2").Select If Range("A1") = True Then MyString = Sheets("Sheet1").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A2") = True Then MyString = Sheets("Sheet2").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A3") = True Then MyString = Sheets("Sheet3").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If MsgBox "All checked boxes have been printed." End Sub The problem comes when any of the Check Boxes is checked and the above macro is activated by clicking PRINT in the Dialog. I get; Method 'PrintOut' of object 'Sheets' failed I've looked at all of the attributes I know of, which is admittedly few, and tried finding a solution via MS Help to no avail. Can some one help me with this? I want to be able to print only the selected sheets based on which Check Box has a check in it by clicking my PRINT Button in my Dialog. Thank you for your help. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned macro in Dialog giving error
By Dialog Box, I am refering to right clicking on on any Sheet, choosing
Insert and selecting "MS Excel 5.0 Dialog" to create a Dialog Box. Insert Check boxes from Forms Menu, assigning the check boxes to specific cells on a Sheet of my choosing using Format Control and then using and updating the OK & CANCEL buttons that come up as default. Right click the OK button and "Assign Macro...". I assign the code that either I made or one provided to the button that should be renamed from "OK" to "PRINT". Now, comes in the code; Sub Dialog() DialogSheets("Dialog1").Show End Sub which opens the dialog box so the user can select/check the box that relates to the sheet to be printed. When the PRINT button(old OK button) is clicked to invoke the code; Sub PRNT() Dim c As Range With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = "True" Then Sheets("Sheet1").PrintOut ElseIf c.Address = "$A$2" And c = "True" Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = "True" Then Sheets("Sheet3").PrintOut End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub This code now invokes the RunTime Error; "PrintOut method of Worksheet class failed" at the first "Sheets("Sheet1").PrintOut " line. Keep in mind that the dialog box is still open, if that matters. The code to open the dialog box has not reached the "End Sub" yet. Sorry for the long explaination but I hope this clears up what I am doing and where my error is occuring. My version of Excel does not have or I do not know "UserForm". Thank you for you continued assistance. "JLGWhiz" wrote: 1. I assume the Dialog Box you are referring to is a UserForm. 2. I am also assuming that the buttons and checkboxes were created from the Control Toolbox in the VBE. 3. Based on these two assumptions I am wondering why your macro name is not part of a click event like: Private Sub CommandButton1_Click() and then the code. 4. Here is what I did. I set up a UserForm1. I put three checkboxes on the form using the Control Toolbox from VBE. I put a command button on the form from the Control Toolbox from VBE. I used this code in the public code module of the project: Sub ufshow() UserForm1.Show End Sub Then I put this code behind the command button: Private Sub CommandButton1_Click() Dim c As Range With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = "True" Then Sheets("Sheet1").PrintOut ElseIf c.Address = "$A$2" And c = "True" Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = "True" Then Sheets("Sheet3").PrintOut End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub Then, in consecutive order of the three checkboxes, I entered the control source as Sheet2!a1, Sheet2!a2 and Sheet2!a3 respectively. I then tested this configuration by initilizing the first macro. It displayed the userform with the checkboxes and the command button. I clicked one of the checkboxes and then clicked the command button. It immediately found the sheet for the checkbox that was clicked and initiated the print process. Maybe this will help you to find where your glitch is. I cannot find it from what you have shown in the postings. "SJW_OST" wrote: It still gives the same error. "JLGWhiz" wrote: I just noticed that I have a typo in the code and forgot some quote marks. This fixes that: Sub PRNT() Dim c As Range With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = "True" Then Sheets("Sheet1").PrintOut ElseIf c.Address = "$A$2" And c = "True" Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = "True" Then Sheets("Sheet3").PrintOut End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub "SJW_OST" wrote: I used the code you gave but still got the Error PrintOut method of Worksheet class failed I've noticed that when I "F8" thru my code, starting with Sub Dialog() ' DialogSheets("Dialog1").Show End Sub The above code does not end until the Dialog box closes. Could this be a part of what is causeing the problem? The PRNT code you gave works if I use it by itself but when assigned to the PRINT Button on the Dialog and activated by clicking he Button is when the error occurs. "JLGWhiz" wrote: Try this: Sub PRNT() With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = True Then Sheets("Sjeet1").PrintOut ElseIf c.Address = "$A$2" And c = True Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = True Then Sheets("Sheet3").Printout End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub "SJW_OST" wrote: Hello, I have a Dialog box which contains 3 Check Boxes & 2 Buttons. I am using this code to bring up the Dialog. Sub Dialog() ' DialogSheets("Dialog1").Show End Sub The 3 Check Boxes place a TRUE or FALSE in specific cells depending on if the box is checked and the 2 Buttons are PRINT & Cancel. I have assigned this code to the PRINT button. Sub PRNT() ' Sheets("Sheet2").Select If Range("A1") = True Then MyString = Sheets("Sheet1").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A2") = True Then MyString = Sheets("Sheet2").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A3") = True Then MyString = Sheets("Sheet3").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If MsgBox "All checked boxes have been printed." End Sub The problem comes when any of the Check Boxes is checked and the above macro is activated by clicking PRINT in the Dialog. I get; Method 'PrintOut' of object 'Sheets' failed I've looked at all of the attributes I know of, which is admittedly few, and tried finding a solution via MS Help to no avail. Can some one help me with this? I want to be able to print only the selected sheets based on which Check Box has a check in it by clicking my PRINT Button in my Dialog. Thank you for your help. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned macro in Dialog giving error
Yes, I vaguely remember the old dialog boxes now. I think I tried to use
them in Excel 4.0 but was not very successful with them back then. I was just beginning to learn how to use Excel transitioning from Lotus 123 and QuatroPro. I don't know anything about the idiosyncrasies of the dialog box, but it shouldn't vary that much from the UserForm. They are both basically containers for controls. Also the Checkboxes should work the same by showing the word True in the designated cell if checked. We know the button is working and the code is initializing because you are getting a message that generates from an aborted attempt to print. The code works on my system, so that leaves, bad printer connection, printer not turned on and darned if I know! I am out of guesses. "SJW_OST" wrote: By Dialog Box, I am refering to right clicking on on any Sheet, choosing Insert and selecting "MS Excel 5.0 Dialog" to create a Dialog Box. Insert Check boxes from Forms Menu, assigning the check boxes to specific cells on a Sheet of my choosing using Format Control and then using and updating the OK & CANCEL buttons that come up as default. Right click the OK button and "Assign Macro...". I assign the code that either I made or one provided to the button that should be renamed from "OK" to "PRINT". Now, comes in the code; Sub Dialog() DialogSheets("Dialog1").Show End Sub which opens the dialog box so the user can select/check the box that relates to the sheet to be printed. When the PRINT button(old OK button) is clicked to invoke the code; Sub PRNT() Dim c As Range With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = "True" Then Sheets("Sheet1").PrintOut ElseIf c.Address = "$A$2" And c = "True" Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = "True" Then Sheets("Sheet3").PrintOut End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub This code now invokes the RunTime Error; "PrintOut method of Worksheet class failed" at the first "Sheets("Sheet1").PrintOut " line. Keep in mind that the dialog box is still open, if that matters. The code to open the dialog box has not reached the "End Sub" yet. Sorry for the long explaination but I hope this clears up what I am doing and where my error is occuring. My version of Excel does not have or I do not know "UserForm". Thank you for you continued assistance. "JLGWhiz" wrote: 1. I assume the Dialog Box you are referring to is a UserForm. 2. I am also assuming that the buttons and checkboxes were created from the Control Toolbox in the VBE. 3. Based on these two assumptions I am wondering why your macro name is not part of a click event like: Private Sub CommandButton1_Click() and then the code. 4. Here is what I did. I set up a UserForm1. I put three checkboxes on the form using the Control Toolbox from VBE. I put a command button on the form from the Control Toolbox from VBE. I used this code in the public code module of the project: Sub ufshow() UserForm1.Show End Sub Then I put this code behind the command button: Private Sub CommandButton1_Click() Dim c As Range With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = "True" Then Sheets("Sheet1").PrintOut ElseIf c.Address = "$A$2" And c = "True" Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = "True" Then Sheets("Sheet3").PrintOut End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub Then, in consecutive order of the three checkboxes, I entered the control source as Sheet2!a1, Sheet2!a2 and Sheet2!a3 respectively. I then tested this configuration by initilizing the first macro. It displayed the userform with the checkboxes and the command button. I clicked one of the checkboxes and then clicked the command button. It immediately found the sheet for the checkbox that was clicked and initiated the print process. Maybe this will help you to find where your glitch is. I cannot find it from what you have shown in the postings. "SJW_OST" wrote: It still gives the same error. "JLGWhiz" wrote: I just noticed that I have a typo in the code and forgot some quote marks. This fixes that: Sub PRNT() Dim c As Range With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = "True" Then Sheets("Sheet1").PrintOut ElseIf c.Address = "$A$2" And c = "True" Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = "True" Then Sheets("Sheet3").PrintOut End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub "SJW_OST" wrote: I used the code you gave but still got the Error PrintOut method of Worksheet class failed I've noticed that when I "F8" thru my code, starting with Sub Dialog() ' DialogSheets("Dialog1").Show End Sub The above code does not end until the Dialog box closes. Could this be a part of what is causeing the problem? The PRNT code you gave works if I use it by itself but when assigned to the PRINT Button on the Dialog and activated by clicking he Button is when the error occurs. "JLGWhiz" wrote: Try this: Sub PRNT() With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = True Then Sheets("Sjeet1").PrintOut ElseIf c.Address = "$A$2" And c = True Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = True Then Sheets("Sheet3").Printout End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub "SJW_OST" wrote: Hello, I have a Dialog box which contains 3 Check Boxes & 2 Buttons. I am using this code to bring up the Dialog. Sub Dialog() ' DialogSheets("Dialog1").Show End Sub The 3 Check Boxes place a TRUE or FALSE in specific cells depending on if the box is checked and the 2 Buttons are PRINT & Cancel. I have assigned this code to the PRINT button. Sub PRNT() ' Sheets("Sheet2").Select If Range("A1") = True Then MyString = Sheets("Sheet1").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A2") = True Then MyString = Sheets("Sheet2").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A3") = True Then MyString = Sheets("Sheet3").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If MsgBox "All checked boxes have been printed." End Sub The problem comes when any of the Check Boxes is checked and the above macro is activated by clicking PRINT in the Dialog. I get; Method 'PrintOut' of object 'Sheets' failed I've looked at all of the attributes I know of, which is admittedly few, and tried finding a solution via MS Help to no avail. Can some one help me with this? I want to be able to print only the selected sheets based on which Check Box has a check in it by clicking my PRINT Button in my Dialog. Thank you for your help. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigned macro in Dialog giving error
Thank you very much for all of your help. I will keep plugging at it until I
get it or find a better way, maybe UserForms. I'll have to look at them now that you've mentioned them and I found them in VB Editor. Thanks again and keep up the great work. "JLGWhiz" wrote: Yes, I vaguely remember the old dialog boxes now. I think I tried to use them in Excel 4.0 but was not very successful with them back then. I was just beginning to learn how to use Excel transitioning from Lotus 123 and QuatroPro. I don't know anything about the idiosyncrasies of the dialog box, but it shouldn't vary that much from the UserForm. They are both basically containers for controls. Also the Checkboxes should work the same by showing the word True in the designated cell if checked. We know the button is working and the code is initializing because you are getting a message that generates from an aborted attempt to print. The code works on my system, so that leaves, bad printer connection, printer not turned on and darned if I know! I am out of guesses. "SJW_OST" wrote: By Dialog Box, I am refering to right clicking on on any Sheet, choosing Insert and selecting "MS Excel 5.0 Dialog" to create a Dialog Box. Insert Check boxes from Forms Menu, assigning the check boxes to specific cells on a Sheet of my choosing using Format Control and then using and updating the OK & CANCEL buttons that come up as default. Right click the OK button and "Assign Macro...". I assign the code that either I made or one provided to the button that should be renamed from "OK" to "PRINT". Now, comes in the code; Sub Dialog() DialogSheets("Dialog1").Show End Sub which opens the dialog box so the user can select/check the box that relates to the sheet to be printed. When the PRINT button(old OK button) is clicked to invoke the code; Sub PRNT() Dim c As Range With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = "True" Then Sheets("Sheet1").PrintOut ElseIf c.Address = "$A$2" And c = "True" Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = "True" Then Sheets("Sheet3").PrintOut End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub This code now invokes the RunTime Error; "PrintOut method of Worksheet class failed" at the first "Sheets("Sheet1").PrintOut " line. Keep in mind that the dialog box is still open, if that matters. The code to open the dialog box has not reached the "End Sub" yet. Sorry for the long explaination but I hope this clears up what I am doing and where my error is occuring. My version of Excel does not have or I do not know "UserForm". Thank you for you continued assistance. "JLGWhiz" wrote: 1. I assume the Dialog Box you are referring to is a UserForm. 2. I am also assuming that the buttons and checkboxes were created from the Control Toolbox in the VBE. 3. Based on these two assumptions I am wondering why your macro name is not part of a click event like: Private Sub CommandButton1_Click() and then the code. 4. Here is what I did. I set up a UserForm1. I put three checkboxes on the form using the Control Toolbox from VBE. I put a command button on the form from the Control Toolbox from VBE. I used this code in the public code module of the project: Sub ufshow() UserForm1.Show End Sub Then I put this code behind the command button: Private Sub CommandButton1_Click() Dim c As Range With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = "True" Then Sheets("Sheet1").PrintOut ElseIf c.Address = "$A$2" And c = "True" Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = "True" Then Sheets("Sheet3").PrintOut End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub Then, in consecutive order of the three checkboxes, I entered the control source as Sheet2!a1, Sheet2!a2 and Sheet2!a3 respectively. I then tested this configuration by initilizing the first macro. It displayed the userform with the checkboxes and the command button. I clicked one of the checkboxes and then clicked the command button. It immediately found the sheet for the checkbox that was clicked and initiated the print process. Maybe this will help you to find where your glitch is. I cannot find it from what you have shown in the postings. "SJW_OST" wrote: It still gives the same error. "JLGWhiz" wrote: I just noticed that I have a typo in the code and forgot some quote marks. This fixes that: Sub PRNT() Dim c As Range With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = "True" Then Sheets("Sheet1").PrintOut ElseIf c.Address = "$A$2" And c = "True" Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = "True" Then Sheets("Sheet3").PrintOut End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub "SJW_OST" wrote: I used the code you gave but still got the Error PrintOut method of Worksheet class failed I've noticed that when I "F8" thru my code, starting with Sub Dialog() ' DialogSheets("Dialog1").Show End Sub The above code does not end until the Dialog box closes. Could this be a part of what is causeing the problem? The PRNT code you gave works if I use it by itself but when assigned to the PRINT Button on the Dialog and activated by clicking he Button is when the error occurs. "JLGWhiz" wrote: Try this: Sub PRNT() With Sheets("Sheet2") For Each c In .Range("A1:A3") If c.Address = "$A$1" And c = True Then Sheets("Sjeet1").PrintOut ElseIf c.Address = "$A$2" And c = True Then Sheets("Sheet2").PrintOut ElseIf c.Address = "$A$3" And c = True Then Sheets("Sheet3").Printout End If Next End With MsgBox "Sheets for all checked boxes have printed" End Sub "SJW_OST" wrote: Hello, I have a Dialog box which contains 3 Check Boxes & 2 Buttons. I am using this code to bring up the Dialog. Sub Dialog() ' DialogSheets("Dialog1").Show End Sub The 3 Check Boxes place a TRUE or FALSE in specific cells depending on if the box is checked and the 2 Buttons are PRINT & Cancel. I have assigned this code to the PRINT button. Sub PRNT() ' Sheets("Sheet2").Select If Range("A1") = True Then MyString = Sheets("Sheet1").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A2") = True Then MyString = Sheets("Sheet2").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If If Range("A3") = True Then MyString = Sheets("Sheet3").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Else: Sheets("Sheet2").Select Range("A1").Select End If MsgBox "All checked boxes have been printed." End Sub The problem comes when any of the Check Boxes is checked and the above macro is activated by clicking PRINT in the Dialog. I get; Method 'PrintOut' of object 'Sheets' failed I've looked at all of the attributes I know of, which is admittedly few, and tried finding a solution via MS Help to no avail. Can some one help me with this? I want to be able to print only the selected sheets based on which Check Box has a check in it by clicking my PRINT Button in my Dialog. Thank you for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Giving me a error ( =REF! ) | Excel Worksheet Functions | |||
Error using a combo box with an assigned macro in Excel | Excel Worksheet Functions | |||
Error using a combo box with an assigned macro in Excel | Excel Discussion (Misc queries) | |||
Macro giving runtime error on one PC and not another | Excel Programming | |||
Macro giving runtime error on one PC and not another | Excel Programming |