I seek to reduce the height of the Excel Application window for certain procedures in my Excel project.
I have a partial solution (via Application.Height = 151), but if I reduce the height to less than 151, the xl ribbon vanishes. I would like Application.height to be quite tight to the underside of the xl ribbon (I guess about 120)
Secondly, is there a way of locking/unlocking the height of the Application window so I have more control?
Else (?) detecting a change in window height as an event and over-riding that dimension change when I want it locked?
Dim appHeight As Long
'Change height of application window with appHeight
appHeight = 151 'minimum height - else ribbon disappears
.WindowState = xlNormal
.Top = 1
.Left = 30
.Width = 600
.Height = appHeight
With .ActiveWindow 'hide worksheets when application height is minimised
If appHeight < 300 Then
.Top = -700 'cannot hide all sheets, so place them out of window view when application
.Top = 1