Minimize workbook/sheet but keep form opened

Question

Is there a way to minimize a workbook/sheet but able to keep the form opened up? I have tried the code:

application.visible=false

and

userform1.show vbmodeless

But this hides the all active workbooks and the tool bar ribbon thing disappears as well. Is there a way to minimize the workbook but keep the ribbon showing and form opened as well?

Answer 1

Tested on Excel 2010

Sub Test()

    ActiveWindow.WindowState = xlMinimized
    UserForm1.Show

End Sub

This will minimize the all the workbooks in Excel but will keep the ribbon and any userforms visible, if you dont have Application.ScreenUpdating = False then people will be able to see the workbooks in the bottom left of Excel.

---

If you want to just minimize a single workbook you can use the code below

Credit to this answer on SO for the minimizing specific workbooks

Sub test()

    Dim wbName As Window

    Set wbName = ActiveWorkbook.Windows(1)'You can use Windows("[Workbook Name]") as well

    wbName.Visible = False
    wbName.Visible = True

End Sub

Let me know if you need anything clarified