Events after printing??

[goblin]

I'm trying to stop Excel from printing textboxes that contain only guidelines on how to use a workbook. Stopping Excel from printing is easy enough by turning of textbox visibility. My problem is turning on visibility again after printing. So far I have the code below (placed in the ThisWorkbook module). Now I need to know if some event fires AFTER printing so that I can turn visibility on again.

Sub TextBoxPrint(onOff As MsoTriState)
    Dim sh As Shape
    For Each sh In ActiveSheet.Shapes
        If sh.Type = msoTextBox Then
            sh.Visible = onOff
        End If
    Next
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    TextBoxPrint msoFalse
End Sub

Can anyone help? Or point out another way to do this?

 

[jlo1976d]

In design mode, if you right clicked on the TextBox and select properties, look for PrintObject, you can set that to True or False. Of course, you can set this properties through your code as well. This way you don't have to mess with the visibility.

 

[Juan Pablo González]

If you still want to, look here for some cool trick on having an "AfterPrint" event:

http://www.rb-ad.dircon.co.uk/rob/excelvba/tips/newevents.htm

 

[Richie(UK)]

Hi goblin,

You can get an AfterPrint effect by using something along these lines:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Cancel = True
'cancel main print

TextBoxPrint msoFalse

With ActiveSheet
    Application.EnableEvents = False
    'to stop this event being called again
    .PrintOut
    'print
    Application.EnableEvents = True
    'switch events back on
End With

TextBoxPrint msoTrue

End Sub

HTH

EDIT: Missed the second call to the routine

 

[goblin]

jlo1976d, I already looked for some properties on the TextBox, but had no success in locating it. That is the reason I reverted to VBA. That is the method I usually use, e.g. when I don't want to print CommandButtons and such.

As to the OnTime method refered to by Juan Pablo, I had already thought of and discounted it, as I didn't like the notion of the textboxes reappearing some X seconds after printing (as I obviously have no idea how long the printing is going to take). Perhaps it is worth investigating how the callback stacks with printing in Excel, i.e. if I can just set the X seconds delay to something very short and hope the OnTime callback schedules after the printing run.

I like Richies implementation very much though. Does everything I want and is very intuitive and clear. Thanks for that one Richie!!

Goblin