Excel - Disable Print Preview

[maximusimo]

Hi,

is there anyway I can disable Print Preview within Excel? I have a form with a cell that increments on every print done with the following:



Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim MyVal As Integer
Range("A1").Select
MyVal = ActiveCell.Value + 1
ActiveCell.Value = MyVal
Range("A2").Select
End Sub



although if someone goes into Print Preview the number also goes up by one - i dont want this to happen as they may cancel out of Print Preview and so the number has already gone up and wont come back down.

All help is much appreciated.

 

[xlbo]

I don't know if you can do this in excel as the printpreview method has no property to test - it is just an action....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Loomah]

To answer the original question, "is there anyway I can disable Print Preview within Excel?", without any thought or consideration to further implications you can disable print preview using the following (hopefully - I can't test it as the FindControls doesn't seem to exist in xl97!)

Sub Find_Disable_Commands()
Dim myControls As CommandBarControls
Dim ctl As CommandBarControl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, Id:=109)    '109 = Print Preview
    For Each ctl In myControls
        ctl.Enabled = False
    Next ctl
End Sub

To re-enable change the property ctl.Enabled to true

See this FAQ for more info faq707-4841

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

Loomah - if you follow File>Print, does it stop you previewing from there ???

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Loomah]

xlbo
Given that that control's ID is 109 it should disable it! Should!

However I can't test it here on xl8 as it doesn't recognise FindControls.

If,pehaps, you were feeling generous you might test it for me!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

Loomah - code works fine to get the Print Preview button on the toolbar

doesn't stop you using the Preview button on the Print dialog box though (as I alluded to before)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[maximusimo]

Sorry Loomah,

doesnt work in Excel V.x for the Mac nor Excel 2002 on the PC

 

[Loomah]

OK, yeah! I should have read xlbo's post properly instead of just seeing some words and thinking I knew what it said.

As xlbo has already pointed out I'm also pretty sure there is no way to detect (through VBA) whether the print preview window is activated. Never say never where an API call might do it but i don't know how!!

Without going down that route the next possible step is to disable File>Print & ctrl+P and so only leave the toolbar print button or create a bespoke print button. The down side is that users who use the FILE>PRINT or CTRL+P route might get confused or annoyed!!

Anyway just in case that is an option here is some code

Sub DisablePrintDialog()
    CommandBars("File").Controls("Print...").Enabled = False
    Application.OnKey "^p", ""
End Sub

Sub EnablePrintDialog()
    CommandBars("File").Controls("Print...").Enabled = True
    Application.OnKey "^p"
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[firefytr]

I thought Print was wanting enabled but Print Preview was wanting disabled..

Sub DisableAllPrint()
    Dim CB As CommandBarControls
    Dim b As Object
    Set CB = Application.CommandBars.FindControls
    For Each b In CB
        If b.Caption = "Print Pre&view" Then
            b.Enabled = False
        End If
    Next
End Sub

Sub EnableAllPrint()
    Dim CB As CommandBarControls
    Dim b As Object
    Set CB = Application.CommandBars.FindControls
    For Each b In CB
        If b.Caption = "Print Pre&view" Then
            b.Enabled = True
        End If
    Next
End Sub

And any Before_Print code will fire when the Print dialog box opens; if the Preview button is pressed from that dialog box the code does not run again.

Maybe an option would be to temporarily disable all Print items and put one of your own which does not access the standard Print dialog box, but instead loads your own userform where you can better control the app environment from.

-----------
Regards,
Zack Barresse