Excel 2016 Buttons in Print Preview Disabled

[edwardjohn]

Hello,
I have troubles with Excel 2016. I have a button on a form which hides the form then print previews a sheet then shows the form again.



Sub CommandButton1_Click()
me.hide
sheet1.printpreview
me.show
End Sub

However, all the buttons in print preview are disabled! So I can't print the sheet or anything the only thing I can do is close the print preview down by clicking the x in the top right corner. This only happens when I load the form when the workbook is opended eg

Sub Workbook_Open()
userform1.show
End Sub

If I remove this code and manually run the userform after I have opened the workbook it works perfect. All the buttons are enabled and clickable. It also works if I put a button on a sheet and have it show the userform when clicked. But I need to load the form automatically when the workbook is opened not by manually running it or clicking a button to open it etc.


Anyone have suggestion to solve this issue?

[Aflatoon]

Try not loading it directly from the Open event. Create a routine that just shows the form, then use OnTime in the Workbook_Open event to schedule that - e.g.

private sub workbook_open()
application.ontime now(), "myShowForm"
end sub

[jdelano]

This worked okay for me:

Private Sub btnShowPreview_Click()


    Me.Hide
    DoEvents
    ThisWorkbook.Sheets("Sheet1").PrintPreview True   ' allow user to edit the parameters
    DoEvents
    Me.Show False  ' don't make the form modal which allows other items to be interacted with
    
End Sub

Private Sub Workbook_Open()
    UserForm1.Show False ' don't make the form modal which allows other items to be interacted with
End Sub

edit: tested in Office 2010 and Office 2021 (I don't have 2016)

[edwardjohn]

Try not loading it directly from the Open event. Create a routine that just shows the form, then use OnTime in the Workbook_Open event to schedule that - e.g.

private sub workbook_open()
application.ontime now(), "myShowForm"
end sub

Thanks for your answer. I got it

[Paul_Hossler]

I usually avoid OnTime if I can

Just .Show the Userform using vbModeLess

Option Explicit


Sub Workbook_Open()
    UserForm1.Show vbModeless
End Sub



Private Sub CommandButton1_Click()
    Me.Hide
    Sheet1.PrintPreview
    Me.Show
End Sub

Capture.JPG