none
limit physical size on open RRS feed

  • Question

  • Can anyone tell me of code I can use to control the physical space a workbook opens with? My goal is for a particular workbook that has very limited function to open to a very small space on my desktop, regardless of how I might have recently closed some other more normal workbook.

    thank you

    Thursday, January 28, 2016 2:02 AM

Answers

  • Very good Edward. Here is the whole thing, with comments. It probably has a pretty limited application, but it's fun and actually pretty simple.

    Sub Auto_Open()
    
        ' the following commands cause the menus and ribbon, the 
        ' status bar, the formula bar, the scroll bars, and the the column 
        ' and row headings to all be hidden on the open event. Just a 
        ' bare-bones spreadsheet remains.
        
    Application.ExecuteExcel4Macro "show.toolbar(""ribbon"",false)"
    Application.DisplayStatusBar = False
    Application.DisplayFormulaBar = False
    Application.DisplayScrollBars = False
    ActiveWindow.DisplayHeadings = False
    
        ' the following commands (a) set the upper left corner of
        ' the application at the upper left corner of the screen, and
        ' (b) cause only the cells actually needed to display.
    
    Application.Top = 0
    Application.Left = 0
    Application.Width = 230
    Application.Height = 158
    
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
        ' the following commands execute on close event, returning the
        ' window to a more normal size and location, so that if another
        ' spreadsheet is opened next, it won't be cramped into the tiny
        ' window used by this one.
    
    Application.Top = 25
    Application.Left = 25
    Application.Width = 850
    Application.Height = 500
    
    End Sub
    
    Sub Auto_Close()
    
        ' these commands both force an automatic save on the close 
        ' event and suppress the save prompt Excel normally displays 
        ' on close
    
        If ThisWorkbook.Saved = False Then
            ThisWorkbook.Save
        End If
    End Sub
    
    
    Sub Show_ribbon()
    
        ' The following commands set up a macro that will return the
        ' spreadsheet to a normal window, should I wish to edit the 
        ' sheet or the code. For convenience, this macro is attached to a 
        ' button. If I were distributing a spreadsheet that I didn't want 
        ' anyone else to be able to edit, I would omit the button.
    
    Application.ExecuteExcel4Macro "show.toolbar(""ribbon"",true)"
    Application.DisplayStatusBar = True
    Application.DisplayFormulaBar = True
    Application.DisplayScrollBars = True
    
    Application.Top = 25
    Application.Left = 25
    Application.Width = 850
    Application.Height = 500
    
    
    End Sub
    
    

    • Marked as answer by Nick_Vittum Saturday, January 30, 2016 1:27 AM
    Saturday, January 30, 2016 1:26 AM

All replies

  • I have figured this out. Please disregard.
    Thursday, January 28, 2016 11:14 PM
  • Hi Nick,

    I am glad your issue has been resolved. It would be appreciated if you could share us details information about your solution, and you could mark your reply as answer. Then others who run into the same issue would find the solution easily.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, January 29, 2016 3:16 AM
  • Very good Edward. Here is the whole thing, with comments. It probably has a pretty limited application, but it's fun and actually pretty simple.

    Sub Auto_Open()
    
        ' the following commands cause the menus and ribbon, the 
        ' status bar, the formula bar, the scroll bars, and the the column 
        ' and row headings to all be hidden on the open event. Just a 
        ' bare-bones spreadsheet remains.
        
    Application.ExecuteExcel4Macro "show.toolbar(""ribbon"",false)"
    Application.DisplayStatusBar = False
    Application.DisplayFormulaBar = False
    Application.DisplayScrollBars = False
    ActiveWindow.DisplayHeadings = False
    
        ' the following commands (a) set the upper left corner of
        ' the application at the upper left corner of the screen, and
        ' (b) cause only the cells actually needed to display.
    
    Application.Top = 0
    Application.Left = 0
    Application.Width = 230
    Application.Height = 158
    
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
        ' the following commands execute on close event, returning the
        ' window to a more normal size and location, so that if another
        ' spreadsheet is opened next, it won't be cramped into the tiny
        ' window used by this one.
    
    Application.Top = 25
    Application.Left = 25
    Application.Width = 850
    Application.Height = 500
    
    End Sub
    
    Sub Auto_Close()
    
        ' these commands both force an automatic save on the close 
        ' event and suppress the save prompt Excel normally displays 
        ' on close
    
        If ThisWorkbook.Saved = False Then
            ThisWorkbook.Save
        End If
    End Sub
    
    
    Sub Show_ribbon()
    
        ' The following commands set up a macro that will return the
        ' spreadsheet to a normal window, should I wish to edit the 
        ' sheet or the code. For convenience, this macro is attached to a 
        ' button. If I were distributing a spreadsheet that I didn't want 
        ' anyone else to be able to edit, I would omit the button.
    
    Application.ExecuteExcel4Macro "show.toolbar(""ribbon"",true)"
    Application.DisplayStatusBar = True
    Application.DisplayFormulaBar = True
    Application.DisplayScrollBars = True
    
    Application.Top = 25
    Application.Left = 25
    Application.Width = 850
    Application.Height = 500
    
    
    End Sub
    
    

    • Marked as answer by Nick_Vittum Saturday, January 30, 2016 1:27 AM
    Saturday, January 30, 2016 1:26 AM
  • This is what i was looking for. Is there any way to make it only apply to one workbook?

    So when I open another one it would open like it regularly would without this code?

    Thanks!

    Wednesday, February 8, 2017 6:51 PM