problem in VB codes

[peyman]

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.

 

[Joel]

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]

No Joel, it toggles.it's weird! in the worksheet it toggles but in print
no!!!!!!!!!!!

 

[Joel]

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]

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.

 

[Dave Peterson]

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), _
procedure:="'" & 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.)

 

[JLatham]

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.

 

[JLatham]

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.

 

[Joel]

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

 

[Dave Peterson]

I also assumed that these were controls from the control toolbox toolbar placed

 

[Dave Peterson]

I still used preview:=true to test, but this didn't fix the problem for me.

 

[Joel]

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]

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.

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.

 

[peyman]

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]

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]

thanx Dave.so as I undrestand there is no difference between activesheet. ...
and Me. ...? right?

 

[peyman]

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), _
procedure:="'" & 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]

There can be a difference between Me and the Activesheet--if you've
selected/activated a different sheet.

thanx Dave.so as I undrestand there is no difference between activesheet. ...
and Me. ...? right?

 

[Dave Peterson]

You should be clicking on CommandButton11. Is that what you meant by the
"print" button?

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), _
procedure:="'" & 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??

 

[peyman]

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