4

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?

FelixSFD
  • 6,052
  • 10
  • 43
  • 117
NoobProgrammer
  • 191
  • 1
  • 2
  • 11

1 Answers1

8

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

Community
  • 1
  • 1
Mr.Burns
  • 690
  • 1
  • 10
  • 24
  • for the line of "ActiveWorkbook.Windows(1).Visible = False", what does the "windows(1)" mean? what does the 1 represent? – NoobProgrammer Nov 18 '16 at 15:24
  • When you open multiple workbooks they all stay in a singlw window (generally), the 1 represents which order they were opened in essentially – Mr.Burns Nov 18 '16 at 15:27
  • so what if I only want to minimize a specific workbook? how do I find out the number for it or can I somehow do "Workbook("nameofworkbook")"? – NoobProgrammer Nov 18 '16 at 15:30
  • I updated my answer, this should cover your question. As to why I have set the `Activeworkbook.Windows()` I was playing around with it a bit more and found you wouldnt make the workbook visible again using `.Visible= True` – Mr.Burns Nov 18 '16 at 15:44