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


31750