Hiding command button before printing

[AndrewGKenneth]

Hi there,

I am having some trouble getting my code to work. I would like commandbutton1 in sheet1 to hide so it is not visible when the form is printed by the user. Could someone please help me to identify where I am going wrong with my code? I have posted it below.

Private Sub Workbook_BeforePrint (Cancel As Boolean)
Sheets("Sheet1").Commandbutton1.Visible = False
End Sub

Thanks in advance

 

[mikerickson]

I'm not sure for an ActiveX box, but if it's from the Forms menu, you can set the .PrintObject property.

Private Sub Workbook_BeforePrint (Cancel As Boolean)
    Sheets("Sheet1").Shapes("Button 1").PrintObject = False
End Sub

It looks like an ActiveX might have the same property, that could be set at design time.

 

[Yongle]

Your code looks ok to me (I tested lower case button - it works)
- assumes that the command button is on Sheet1, and not on a userform

for active-x button either

Sheets("Sheet1").CommandButton1.Visible = False
Sheets("Sheet1").Shapes("CommandButton1").Visible = False

form control button

Sheets("Sheet1").Shapes("Button 1").Visible = False

Is the command button name correct?
- check with this

Sub TestShapes()
    Dim objX As Object, msg As String
    With Sheets("Sheet1")
        For Each objX In .OLEObjects
            If TypeName(objX.Object) = "CommandButton" Then
                msg = msg & vbCr & objX.Name
            End If
        Next
    End With
    MsgBox msg
End Sub

Where is Private Sub Workbook_BeforePrint located?
- it should be in ThisWorkbook module
- add a message box to prove that it triggers when printing

 

[mikerickson]

The problem that I have with the OP code is how to make the button re-appear once the printing is complete.
Yes, a simple macro would do that, but how to avoid running that second macro.

 

[Yongle]

@AndrewGKenneth
It did not occur to me , but @mikerickson has a valid point. When should the command button become visible again?

 

[dreid1011]

If I'm not mistaken, both Form Controls and Active X Controls have a "Print Object" option that can be toggled in the "Format Control" menu. This requires no code to set the value each time... the button would be visible all the time on the worksheet, but as soon as you print, the button does not show up on the hard copy.

I guess this is only relevant if you are not using vba to add a button to a sheet dynamically.