problem in VB codes

P

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.
 
J

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.
 
P

peyman

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

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
 
P

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.
 
D

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.)
 
J

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.
 
J

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.
 
J

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
 
J

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.
 
D

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.
 
P

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?
 
D

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
 
P

peyman

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

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??
 
D

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?
 
D

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??
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top