Resize & Positioning Excel Window

[nupema]

Hi,

I'm trying to resize and position one particular Excel workbook, and I made it work, but there is a problem, every excel file I open after this one, is opening in the same position and configuration (no bars, buttons, etc)


I used this code:

ThisWorkbook:

Sub Workbook_Open()
Application.EnableEvents = False
Call SetWindowSize
Application.EnableEvents = True
End Sub
Sub Workbook_Close()
Application.EnableEvents = False
Call SetWindowSizeClose
Application.EnableEvents = True
End Sub

Module 3:

Sub SetWindowSize()
    Application.WindowState = xlNormal
    Application.Top = 444
    Application.Left = 920
    Application.Width = 280
    Application.Height = 230
    ActiveWindow.DisplayHeadings = False
    ActiveWindow.DisplayGridlines = False
    Application.DisplayFormulaBar = False
    Application.ScreenUpdating = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    Application.DisplayStatusBar = False
  
    'CommandBars.ExecuteMso "MinimizeRibbon"
    
End Sub

Module 4:

Sub SetWindowSizeClose()
Application.WindowState = xlNormal
 Application.Top = 150
    Application.Left = 300
    Application.Width = 800
    Application.Height = 600
    ActiveWindow.DisplayHeadings = True
    ActiveWindow.DisplayGridlines = True
    Application.DisplayFormulaBar = True
    Application.ScreenUpdating = True
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    Application.DisplayStatusBar = True
End Sub

Module 5:

Sub Workbook_Open()
'On workbook open turn off scroll bars.
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
End With

End Sub

Module 5:

Sub Workbook_Close(Cancel As Boolean)
'Turn the scroll bars on before closing the workbook.
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
End With
End Sub

I also found this other code, that for what I can understand, could also do the job, but I can't make it work at all...

Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim ws As Worksheet
If ActiveSheet.Name = "BF" Then
Application.CommandBars("Worksheet Menu Bar").Enabled = False
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Formatting").Visible = False
Application.CommandBars("NM").Visible = False
With ActiveWindow
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
    End With
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
Else
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Formatting").Visible = True
Application.CommandBars("NM").Visible = True
With ActiveWindow
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
    End With
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
End If
 
Application.DisplayAlerts = True
End Sub

Can anyone please help me?

 

[nupema]

Andrew, it's solved!! I used your sample code and with some small modifications its working perfectly!!

Thank you very much for your help!

 

[muhammedali25]

Hi, I know this thread is old. Sorry for that firstly. I want to resizing the workbook window when I Change Sheet1 to sheet2. Is there any way to do this?