Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
hi,
I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
Your code has no toggle. You are setting the options buttons to true none
are being set to false. I think you want to add a group box around the two controls so they toggle. From the excel spreadsheet menu - View - Toolbars - Forms. Put a group box around the two buttons so they will automatically toggle. "peyman" wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
No Joel, it toggles.it's weird! in the worksheet it toggles but in print
no!!!!!!!!!!! "Joel" wrote: Your code has no toggle. You are setting the options buttons to true none are being set to false. I think you want to add a group box around the two controls so they toggle. From the excel spreadsheet menu - View - Toolbars - Forms. Put a group box around the two buttons so they will automatically toggle. "peyman" wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
I was able to repeat the problem. Found a fix, you may not like it. Add a
Preview to the print ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True , Preview:=True "peyman" wrote: No Joel, it toggles.it's weird! in the worksheet it toggles but in print no!!!!!!!!!!! "Joel" wrote: Your code has no toggle. You are setting the options buttons to true none are being set to false. I think you want to add a group box around the two controls so they toggle. From the excel spreadsheet menu - View - Toolbars - Forms. Put a group box around the two buttons so they will automatically toggle. "peyman" wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
no, still ,I have the problem!! the change in optionbuttons doesn't show up
in the prints.how the "preview" can help me???!!!!it makes the procedure manual!! thanx anyway. "Joel" wrote: I was able to repeat the problem. Found a fix, you may not like it. Add a Preview to the print ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True , Preview:=True "peyman" wrote: No Joel, it toggles.it's weird! in the worksheet it toggles but in print no!!!!!!!!!!! "Joel" wrote: Your code has no toggle. You are setting the options buttons to true none are being set to false. I think you want to add a group box around the two controls so they toggle. From the excel spreadsheet menu - View - Toolbars - Forms. Put a group box around the two buttons so they will automatically toggle. "peyman" wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
I tried adding some DoEvents. And it didn't help.
I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
Did/has anyone tried to .Repaint the form/control before the print?
I know, I'm lazy AND hate burning paper, so I didn't try it myself, but I've used that in the past to get 'instant' updates to the appearance of a control. "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
Oops, just a little pink in the face: .Repaint is only available for
UserForm, not individual controls, so that may have been a useless thought. "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
I don't think its the control, I think the problem is with the window. but
you gave me a good idea that seems to work. If you activate the window between the prints it solves the problem. Activating the window does a repaint. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If ActiveWindow.Activate Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub "JLatham" wrote: Oops, just a little pink in the face: .Repaint is only available for UserForm, not individual controls, so that may have been a useless thought. "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
I also assumed that these were controls from the control toolbox toolbar placed
on the worksheet. JLatham wrote: Did/has anyone tried to .Repaint the form/control before the print? I know, I'm lazy AND hate burning paper, so I didn't try it myself, but I've used that in the past to get 'instant' updates to the appearance of a control. "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
I still used preview:=true to test, but this didn't fix the problem for me.
Joel wrote: I don't think its the control, I think the problem is with the window. but you gave me a good idea that seems to work. If you activate the window between the prints it solves the problem. Activating the window does a repaint. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If ActiveWindow.Activate Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub "JLatham" wrote: Oops, just a little pink in the face: .Repaint is only available for UserForm, not individual controls, so that may have been a useless thought. "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
To save paper, I'm been sending my prints to an adobe writer. I went back
and checked a few more times and everything is working correctly with my added line. Dave can you check one more time? The first button should be selected at the beginning. The first print should show the 1st button selected and the 2nd print should show the 2nd button selected. Both buttons should be in the same group Box. The original problem without the additional activate had the first button selected in both prints. "Dave Peterson" wrote: I still used preview:=true to test, but this didn't fix the problem for me. Joel wrote: I don't think its the control, I think the problem is with the window. but you gave me a good idea that seems to work. If you activate the window between the prints it solves the problem. Activating the window does a repaint. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If ActiveWindow.Activate Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub "JLatham" wrote: Oops, just a little pink in the face: .Repaint is only available for UserForm, not individual controls, so that may have been a useless thought. "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
I tested one more time (still with printpreview) and it suffered from the same
problem. The buttons don't change status in the print preview. Joel wrote: To save paper, I'm been sending my prints to an adobe writer. I went back and checked a few more times and everything is working correctly with my added line. Dave can you check one more time? The first button should be selected at the beginning. The first print should show the 1st button selected and the 2nd print should show the 2nd button selected. Both buttons should be in the same group Box. The original problem without the additional activate had the first button selected in both prints. "Dave Peterson" wrote: I still used preview:=true to test, but this didn't fix the problem for me. Joel wrote: I don't think its the control, I think the problem is with the window. but you gave me a good idea that seems to work. If you activate the window between the prints it solves the problem. Activating the window does a repaint. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If ActiveWindow.Activate Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub "JLatham" wrote: Oops, just a little pink in the face: .Repaint is only available for UserForm, not individual controls, so that may have been a useless thought. "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
Dave.I didn't get what you did.can you please explain the codes you have
written.sorry I'm novice.what is "Me. ..."?what should I replace with that? "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
Me is a reserved word in VBA. It refers to the thing that owns the code. In
this case since the code is in a worksheet module, it refers to the worksheet that owns the code. You used activesheet. In some cases, you may be selecting or activating a different sheet. I wanted to make sure that the correct sheet was printed, so I used Me. The other portion of the code is the application.ontime. That just tells excel to wait a second (Now + TimeSerial(0, 0, 1)) and then start a new routine. You may want to take a look at Chip Pearson's notes: http://www.cpearson.com/excel/OnTime.aspx peyman wrote: Dave.I didn't get what you did.can you please explain the codes you have written.sorry I'm novice.what is "Me. ..."?what should I replace with that? "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson -- Dave Peterson |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
thanx Dave.so as I undrestand there is no difference between activesheet. ...
and Me. ...? right? "Dave Peterson" wrote: Me is a reserved word in VBA. It refers to the thing that owns the code. In this case since the code is in a worksheet module, it refers to the worksheet that owns the code. You used activesheet. In some cases, you may be selecting or activating a different sheet. I wanted to make sure that the correct sheet was printed, so I used Me. The other portion of the code is the application.ontime. That just tells excel to wait a second (Now + TimeSerial(0, 0, 1)) and then start a new routine. You may want to take a look at Chip Pearson's notes: http://www.cpearson.com/excel/OnTime.aspx peyman wrote: Dave.I didn't get what you did.can you please explain the codes you have written.sorry I'm novice.what is "Me. ..."?what should I replace with that? "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson -- Dave Peterson |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
hi Dave,
I used your code : Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub it works perfect but still a bit manual.I have push the "print" button.can't it be done automatically?? "Dave Peterson" wrote: Me is a reserved word in VBA. It refers to the thing that owns the code. In this case since the code is in a worksheet module, it refers to the worksheet that owns the code. You used activesheet. In some cases, you may be selecting or activating a different sheet. I wanted to make sure that the correct sheet was printed, so I used Me. The other portion of the code is the application.ontime. That just tells excel to wait a second (Now + TimeSerial(0, 0, 1)) and then start a new routine. You may want to take a look at Chip Pearson's notes: http://www.cpearson.com/excel/OnTime.aspx peyman wrote: Dave.I didn't get what you did.can you please explain the codes you have written.sorry I'm novice.what is "Me. ..."?what should I replace with that? "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson -- Dave Peterson |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
There can be a difference between Me and the Activesheet--if you've
selected/activated a different sheet. peyman wrote: thanx Dave.so as I undrestand there is no difference between activesheet. ... and Me. ...? right? "Dave Peterson" wrote: Me is a reserved word in VBA. It refers to the thing that owns the code. In this case since the code is in a worksheet module, it refers to the worksheet that owns the code. You used activesheet. In some cases, you may be selecting or activating a different sheet. I wanted to make sure that the correct sheet was printed, so I used Me. The other portion of the code is the application.ontime. That just tells excel to wait a second (Now + TimeSerial(0, 0, 1)) and then start a new routine. You may want to take a look at Chip Pearson's notes: http://www.cpearson.com/excel/OnTime.aspx peyman wrote: Dave.I didn't get what you did.can you please explain the codes you have written.sorry I'm novice.what is "Me. ..."?what should I replace with that? "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
You should be clicking on CommandButton11. Is that what you meant by the
"print" button? peyman wrote: hi Dave, I used your code : Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub it works perfect but still a bit manual.I have push the "print" button.can't it be done automatically?? "Dave Peterson" wrote: Me is a reserved word in VBA. It refers to the thing that owns the code. In this case since the code is in a worksheet module, it refers to the worksheet that owns the code. You used activesheet. In some cases, you may be selecting or activating a different sheet. I wanted to make sure that the correct sheet was printed, so I used Me. The other portion of the code is the application.ontime. That just tells excel to wait a second (Now + TimeSerial(0, 0, 1)) and then start a new routine. You may want to take a look at Chip Pearson's notes: http://www.cpearson.com/excel/OnTime.aspx peyman wrote: Dave.I didn't get what you did.can you please explain the codes you have written.sorry I'm novice.what is "Me. ..."?what should I replace with that? "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
hi Dave,
I removed preview:=true and it works fine.how about if wanna repeat the second print with toggled optionbutton?in other words, I'd like to have one print with ColorIndex = xlAutomatic (no matter what optionbutton is selected) and two more prints with different optionbutton selected.now I have only two of them.thanx again "Dave Peterson" wrote: You should be clicking on CommandButton11. Is that what you meant by the "print" button? peyman wrote: hi Dave, I used your code : Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub it works perfect but still a bit manual.I have push the "print" button.can't it be done automatically?? "Dave Peterson" wrote: Me is a reserved word in VBA. It refers to the thing that owns the code. In this case since the code is in a worksheet module, it refers to the worksheet that owns the code. You used activesheet. In some cases, you may be selecting or activating a different sheet. I wanted to make sure that the correct sheet was printed, so I used Me. The other portion of the code is the application.ontime. That just tells excel to wait a second (Now + TimeSerial(0, 0, 1)) and then start a new routine. You may want to take a look at Chip Pearson's notes: http://www.cpearson.com/excel/OnTime.aspx peyman wrote: Dave.I didn't get what you did.can you please explain the codes you have written.sorry I'm novice.what is "Me. ..."?what should I replace with that? "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
I'd add another delayed procedure in the WaitABit procedure.
.... End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True 'toggle your commandbutton value here! 'and call another procedure Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit2" End Sub Private Sub WaitABit2() 'do your setup and print here End Sub peyman wrote: hi Dave, I removed preview:=true and it works fine.how about if wanna repeat the second print with toggled optionbutton?in other words, I'd like to have one print with ColorIndex = xlAutomatic (no matter what optionbutton is selected) and two more prints with different optionbutton selected.now I have only two of them.thanx again "Dave Peterson" wrote: You should be clicking on CommandButton11. Is that what you meant by the "print" button? peyman wrote: hi Dave, I used your code : Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub it works perfect but still a bit manual.I have push the "print" button.can't it be done automatically?? "Dave Peterson" wrote: Me is a reserved word in VBA. It refers to the thing that owns the code. In this case since the code is in a worksheet module, it refers to the worksheet that owns the code. You used activesheet. In some cases, you may be selecting or activating a different sheet. I wanted to make sure that the correct sheet was printed, so I used Me. The other portion of the code is the application.ontime. That just tells excel to wait a second (Now + TimeSerial(0, 0, 1)) and then start a new routine. You may want to take a look at Chip Pearson's notes: http://www.cpearson.com/excel/OnTime.aspx peyman wrote: Dave.I didn't get what you did.can you please explain the codes you have written.sorry I'm novice.what is "Me. ..."?what should I replace with that? "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#22
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
I'm confused.which line should I add to the "waitabit" procedure?and what's
the "waitabit2"? "Dave Peterson" wrote: I'd add another delayed procedure in the WaitABit procedure. .... End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True 'toggle your commandbutton value here! 'and call another procedure Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit2" End Sub Private Sub WaitABit2() 'do your setup and print here End Sub peyman wrote: hi Dave, I removed preview:=true and it works fine.how about if wanna repeat the second print with toggled optionbutton?in other words, I'd like to have one print with ColorIndex = xlAutomatic (no matter what optionbutton is selected) and two more prints with different optionbutton selected.now I have only two of them.thanx again "Dave Peterson" wrote: You should be clicking on CommandButton11. Is that what you meant by the "print" button? peyman wrote: hi Dave, I used your code : Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub it works perfect but still a bit manual.I have push the "print" button.can't it be done automatically?? "Dave Peterson" wrote: Me is a reserved word in VBA. It refers to the thing that owns the code. In this case since the code is in a worksheet module, it refers to the worksheet that owns the code. You used activesheet. In some cases, you may be selecting or activating a different sheet. I wanted to make sure that the correct sheet was printed, so I used Me. The other portion of the code is the application.ontime. That just tells excel to wait a second (Now + TimeSerial(0, 0, 1)) and then start a new routine. You may want to take a look at Chip Pearson's notes: http://www.cpearson.com/excel/OnTime.aspx peyman wrote: Dave.I didn't get what you did.can you please explain the codes you have written.sorry I'm novice.what is "Me. ..."?what should I replace with that? "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#23
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
Set your default printer to a PDF writer and try then. You won't
waste paper this way. On Oct 20, 9:04 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: Did/has anyone tried to .Repaint the form/control before the print? I know, I'm lazy AND hate burning paper, so I didn't try it myself, but I've used that in the past to get 'instant' updates to the appearance of a control. "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson- Hide quoted text - - Show quoted text - |
#24
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
There's an added .ontime line in the WaitABit changed procedure that calls
waitabit2. And WaitABit2 is the procedure where you'd print the 3rd time. peyman wrote: I'm confused.which line should I add to the "waitabit" procedure?and what's the "waitabit2"? "Dave Peterson" wrote: I'd add another delayed procedure in the WaitABit procedure. .... End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True 'toggle your commandbutton value here! 'and call another procedure Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit2" End Sub Private Sub WaitABit2() 'do your setup and print here End Sub peyman wrote: hi Dave, I removed preview:=true and it works fine.how about if wanna repeat the second print with toggled optionbutton?in other words, I'd like to have one print with ColorIndex = xlAutomatic (no matter what optionbutton is selected) and two more prints with different optionbutton selected.now I have only two of them.thanx again "Dave Peterson" wrote: You should be clicking on CommandButton11. Is that what you meant by the "print" button? peyman wrote: hi Dave, I used your code : Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub it works perfect but still a bit manual.I have push the "print" button.can't it be done automatically?? "Dave Peterson" wrote: Me is a reserved word in VBA. It refers to the thing that owns the code. In this case since the code is in a worksheet module, it refers to the worksheet that owns the code. You used activesheet. In some cases, you may be selecting or activating a different sheet. I wanted to make sure that the correct sheet was printed, so I used Me. The other portion of the code is the application.ontime. That just tells excel to wait a second (Now + TimeSerial(0, 0, 1)) and then start a new routine. You may want to take a look at Chip Pearson's notes: http://www.cpearson.com/excel/OnTime.aspx peyman wrote: Dave.I didn't get what you did.can you please explain the codes you have written.sorry I'm novice.what is "Me. ..."?what should I replace with that? "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#25
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem in VB codes
thank you Dave.got it.
"Dave Peterson" wrote: There's an added .ontime line in the WaitABit changed procedure that calls waitabit2. And WaitABit2 is the procedure where you'd print the 3rd time. peyman wrote: I'm confused.which line should I add to the "waitabit" procedure?and what's the "waitabit2"? "Dave Peterson" wrote: I'd add another delayed procedure in the WaitABit procedure. .... End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True 'toggle your commandbutton value here! 'and call another procedure Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit2" End Sub Private Sub WaitABit2() 'do your setup and print here End Sub peyman wrote: hi Dave, I removed preview:=true and it works fine.how about if wanna repeat the second print with toggled optionbutton?in other words, I'd like to have one print with ColorIndex = xlAutomatic (no matter what optionbutton is selected) and two more prints with different optionbutton selected.now I have only two of them.thanx again "Dave Peterson" wrote: You should be clicking on CommandButton11. Is that what you meant by the "print" button? peyman wrote: hi Dave, I used your code : Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub it works perfect but still a bit manual.I have push the "print" button.can't it be done automatically?? "Dave Peterson" wrote: Me is a reserved word in VBA. It refers to the thing that owns the code. In this case since the code is in a worksheet module, it refers to the worksheet that owns the code. You used activesheet. In some cases, you may be selecting or activating a different sheet. I wanted to make sure that the correct sheet was printed, so I used Me. The other portion of the code is the application.ontime. That just tells excel to wait a second (Now + TimeSerial(0, 0, 1)) and then start a new routine. You may want to take a look at Chip Pearson's notes: http://www.cpearson.com/excel/OnTime.aspx peyman wrote: Dave.I didn't get what you did.can you please explain the codes you have written.sorry I'm novice.what is "Me. ..."?what should I replace with that? "Dave Peterson" wrote: I tried adding some DoEvents. And it didn't help. I tried toggling application.screenupdating off, then on. And it didn't help. I tried adding application.wait (for a second). And it didn't help. I tried adding minimizing the activewindow, then restoring it. And it didn't help. But this seemed to work ok for me. Actually, I didn't test on paper. I only tested using print preview. But the others failed with that. This one worked ok. Option Explicit Private Sub CommandButton11_Click() Me.Range("G13:I32").Font.ColorIndex = xlAutomatic Me.PrintOut preview:=True, Copies:=1, Collate:=True If Me.OptionButton1.Value = True Then Me.OptionButton2.Value = True Else Me.OptionButton1.Value = True End If Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="'" & ThisWorkbook.Name & "'!" & Me.CodeName & ".WaitABit" End Sub Private Sub WaitABit() Me.Range("G13:I32").Font.ColorIndex = 2 Me.PrintOut preview:=True, Copies:=1, Collate:=True End Sub (The WaitABit code is in the same worksheet module.) peyman wrote: hi, I have a very simple code ,but I don't know why it doesn't work. Private Sub CommandButton11_Click() Range("G13:I32").Select Selection.Font.ColorIndex = xlAutomatic Range("B7").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True If ActiveSheet.OptionButton1.Value = True Then ActiveSheet.OptionButton2.Value = True Else ActiveSheet.OptionButton1.Value = True End If Range("G13:I32").Select Selection.Font.ColorIndex = 2 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub the problem is when I run the program, the optionbutton toggle is not showing in the second print?!! Any help?thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem in VB codes-2 | Excel Discussion (Misc queries) | |||
problem in VB codes | Excel Discussion (Misc queries) | |||
Help with codes | Excel Discussion (Misc queries) | |||
VBA Codes | Excel Worksheet Functions | |||
Zip Codes | Excel Discussion (Misc queries) |