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?

 

[Andrew Poulsom]

The event is :

Sub Workbook_BeforeClose()

 

[nupema]

Hi Andrew,

First of all, thanks for your quick answer.

But the issue remains. If the file with this vba code keeps opened, every excel I open during this time assume the window resize configuration.

 

[Andrew Poulsom]

That's because you have resized the Application (Excel).

 

[nupema]

Right... Solutions?

 

[Andrew Poulsom]

I guess you could use Application level events:

Excel Application Events And VBA

 

[nupema]

Let me put it in other way...Me and Google, we are good friends since is birth.. and we help each other every day, believe me...
And I did search solutions before I posted... But since I couldn't figure out a working solution, I'm searching a kind of help that give me more than just telling me what its wrong with my code or just pointing to webpages that give me information about the topic.
If this is possible I would be very grateful.

Kind regards

 

[Andrew Poulsom]

How is anyone expected to know what you are or aren't able to do from your original post? Dictator applications are not straightforward.

 

[no_Fate]

Hi all!

I'm actually facing the same issue. I have a document with code to resize the window when opened but every excel file that I open after that is also resized. Is there a way to apply the code to ONE excel document only?

Cheers!

 

[Andrew Poulsom]

Here is some sample code that you can expand to suit:

'ThisWorkbook Module

Private WithEvents App As Application

Private Sub Workbook_Open()
    Call SetWindowSize
    Set App = Application
End Sub

Private Sub Workbook_Activate()
    Call SetWindowSize
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call ResetWindowSize
    Set App = Nothing
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    Call ResetWindowSize
End Sub

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
    If Wb.Name <> Me.Name Then
        Call ResetWindowSize
    End If
End Sub

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    Call ResetWindowSize
End Sub

'General Module

Sub SetWindowSize()
    Application.WindowState = xlNormal
    Application.Top = 150
    Application.Left = 300
    Application.Width = 800
    Application.Height = 600
End Sub

Sub ResetWindowSize()
    Application.WindowState = xlMaximized
End Sub