Printing
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Printing

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Does anyone know if there is a way to deactivate printing / print previewing through the use of VBA code?

    Based on a value stored in a worksheet or variable [i haven't decided], I would like to turn this feature on or off.
    Thanks in advance,
    Patrick

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What do you mean by deactivate? You can always delete the icons from the toolbars.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Try this. Open the VB editor, click Ctrl R to open the Project explorer and then double click the ThisWorkbook icon of your workbook. This code should do what you need:-

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If Sheets("sheet1").Range("a1") <> "CAN PRINT" Then Cancel = True
    End Sub

    HTH,
    Dan

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think he/she wants to disable the menuitem.
    You can do this in VBA.
    I do not know the syntax. Will do a search
    Tom

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Application.CommandBars("Worksheet Menu Bar").Controls _
    ("File").Controls("Print Preview").Enabled = False

    Application.CommandBars("Standard").Controls _
    ("Print Preview").Enabled = False


    The following two lines of code will disable the print preview on the Worksheet Menu Bar - File menuitem and the Print Preview on the standard toobar...

    However, you will need to disable custimization. The user can still create their own button.

    Tom

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks. Works like a charm!
    Thanks in advance,
    Patrick

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Okay - this works absolutely great for PrintPreview. However, it does not work for regular Print. I have modified it so it does work for the Menu listing "Print...". However, it does not work for the toolbar. I believe this is because the toolbar is called Print(Default Printer). How can I find the default printer and make this icon unavailable.
    Thanks in advance,
    Patrick

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    This will disable any button in any commandbar which begins with "Print"
    To re-enable, copy into another sub and change the false to true...

    Sub DisableCommandBarControl()
    Dim cb As CommandBar
    Dim bb As CommandBarControl

    For Each cb In CommandBars
    For Each bb In cb.Controls
    If Left(bb.Caption, 5) = "Print" Then _
    bb.Enabled = False
    Next
    Next

    End Sub

    Tom

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom,

    Thanks for all of your help. I tried to figure this out for about 2 hours last night with no success. I did not know about the bb.

    Thanks!!!

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Tom,

    Thanks again for the help. I have one more question however. I have setup two subs - one call Sub EnablePrint() and the other Sub DisablePrint(). I use these by calling them when I need them.

    However, I want to call the EnablePrint whenever the workbook or application is closed. I am trying to do this by using the following in the ThisWorkbood tab

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Call EnablePrint

    End Sub

    This gives me an error every time. Sometimes Excel just locks up. Any suggestions?
    Thanks in advance,
    Patrick

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com