Horizontal Scrollbar partially Hidden by Taskbar

[Jon5001]

When I run the code below, the horizontal scrollbar and status bar are
partially hidden by the taskbar. If I resize the taskbar, the status bar and
scrollbar appear unobstructed above the taskbar, but without resizing the
taskbar they remain partially hidden.

Is there anyway I can make it so that the status bar and horizontal bar are
unobstructed from the outset automatically?

Thanks

Sub Auto_open()

With Application
.DisplayFullScreen = True
.ScreenUpdating = False
.DisplayFormulaBar = False
.DisplayScrollBars = True
.DisplayStatusBar = True
'.CommandBars("Standard").Visible = False
'.CommandBars("Formatting").Visible = False
'.CommandBars("Drawing").Visible = False
'.CommandBars("Control Toolbox").Visible = False


End With



Dim current_screen As String
Dim ws
On Error Resume Next
current_screen = ActiveSheet.Name
For Each ws In ActiveWorkbook.Worksheets
ws.Select
With ActiveWindow
.DisplayHeadings = False
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = False
End With
Next
Worksheets(current_screen).Select
With Application '

End With
Range("A1").Select



End Sub

Sub auto_close()

With Application
.DisplayFullScreen = False
.ScreenUpdating = False
.DisplayFormulaBar = True
.DisplayScrollBars = True
.DisplayStatusBar = True
'.CommandBars("Standard").Visible = True
'.CommandBars("Formatting").Visible = True
'.CommandBars("Drawing").Visible = True
'.CommandBars("Control Toolbox").Visible = True
End With
Dim current_screen As String
Dim ws
On Error Resume Next
current_screen = ActiveSheet.Name
For Each ws In ActiveWorkbook.Worksheets
ws.Select
With ActiveWindow
.DisplayHeadings = False
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
End With
Next
Worksheets(current_screen).Select
With Application '
End With

End Sub

 

[NickHK]

I would guess you have your Taskbar set to AlwaysOnTop. If so, it would seem
that Excel fills the whole Desktop, ignoring the height of the Taskbar when
set to .DisplayFullScreen = True.
I suppose this is the correct behaviour, because Excel IS filling the screen
; it's just that you force your taskbar on top of it. But then it should not
resize to accomodate the taskbar later.

One way would be to make you own FullScreen routine, hiding the required
elements and setting the Application height. A SysInfo control or one of the
API methods below will give you the required height :
With SysInfo1
Debug.Print .WorkAreaHeight, Application.Height * 20
End With
Or use the API.
http://www.vb-helper.com/howto_center_form.html

Otherwise, I was thinking you could change the taskbar size and broadcast
the change.
Private Declare Function SHAppBarMessage Lib "shell32.dll" (ByVal dwMessage
As Long, pData As APPBARDATA) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal
lpClassName As String, ByVal lpWindowName As String) As Long
Const ABM_NEW = &H0
Const ABM_REMOVE = &H1
Const ABM_QUERYPOS = &H2
Const ABM_SETPOS = &H3 'Sets the size and screen position
of an appbar
Const ABM_GETSTATE = &H4
Const ABM_GETTASKBARPOS = &H5
Const ABM_ACTIVATE = &H6
Const ABM_GETAUTOHIDEBAR = &H7
Const ABM_SETAUTOHIDEBAR = &H8
Const ABM_WINDOWPOSCHANGED = &H9 'Notifies the system when an appbar'
s position has changed.

Const ABN_STATECHANGE = &H0
Const ABN_POSCHANGED = &H1
Const ABN_FULLSCREENAPP = &H2
Const ABN_WINDOWARRANGE = &H3

Const ABS_AUTOHIDE = &H1
Const ABS_ALWAYSONTOP = &H2

Const ABE_LEFT = 0
Const ABE_TOP = 1
Const ABE_RIGHT = 2
Const ABE_BOTTOM = 3

Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

Private Type APPBARDATA
cbSize As Long
hwnd As Long
uCallbackMessage As Long
uEdge As Long
rc As RECT
lParam As Long ' message specific
End Type

Private Sub ToggleTaskbar()
'Based on example from :KPD-Team 1999 URL, http://www.allapi.net/
Dim ABD As APPBARDATA, Ret As Long
Dim OldHeight As Long

With ABD
.cbSize = Len(ABD)
.hwnd = FindWindow("Shell_TrayWnd", vbNullString)

'Get the taskbar's position
SHAppBarMessage ABM_GETTASKBARPOS, ABD
'Save the current .Top
OldHeight = .rc.Bottom - .rc.Top
'Decrease the .Top
ABD.rc.Top = ABD.rc.Top - OldHeight
'Set the new .Top
SHAppBarMessage ABM_SETPOS, ABD
'Broadcast the change
SHAppBarMessage ABM_WINDOWPOSCHANGED, ABD
'Set back to the original .Top
.rc.Top = .rc.Bottom - OldHeight
SHAppBarMessage ABM_SETPOS, ABD
'Broadcast the change
SHAppBarMessage ABM_WINDOWPOSCHANGED, ABD
End With
End Sub

But this does NOT change the Windows taskbar, as apparently it does not
respond to the ABM_SETPOS message.
Spy++ can reveals the messages sent to the taskbar, but I'm not sure yet
which one is used when you manually resize the taskbar.
Let you know.

NickHK

 

[NickHK]

Seems that Excel is not sizing itself correctly, as a call to SetWindowPos
with the same dimensions retrived from GetWindowRect jogs it the correct
workarea.

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal
lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long,
lpRect As RECT) As Long

Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

Const SWP_NOACTIVATE = &H10
Const SWP_NOZORDER = &H4

Private Declare Function SetWindowPos Lib "user32" (ByVal hwnd As Long,
ByVal hWndInsertAfter As Long, ByVal x As Long, ByVal y As Long, ByVal cx As
Long, ByVal cy As Long, ByVal wFlags As Long) As Long

Sub Auto_open()
Dim XLhwnd As Long
Dim RetVal As Long
Dim WndRect As RECT

XLhwnd = FindWindow("XLMAIN", Application.Caption)
RetVal = GetWindowRect(XLhwnd, WndRect)

With Application
.DisplayFullScreen = True

RetVal = SetWindowPos(XLhwnd, 0, WndRect.Left, WndRect.Top,
WndRect.Right, WndRect.Bottom, SWP_NOZORDER Or SWP_NOACTIVATE)
...rest of your code

NickHK

 

[Jon5001]

Thanks! This solution works like a charm.

Seems that Excel is not sizing itself correctly, as a call to SetWindowPos
with the same dimensions retrived from GetWindowRect jogs it the correct
workarea.

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal
lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long,
lpRect As RECT) As Long

Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

Const SWP_NOACTIVATE = &H10
Const SWP_NOZORDER = &H4

Private Declare Function SetWindowPos Lib "user32" (ByVal hwnd As Long,
ByVal hWndInsertAfter As Long, ByVal x As Long, ByVal y As Long, ByVal cx As
Long, ByVal cy As Long, ByVal wFlags As Long) As Long

Sub Auto_open()
Dim XLhwnd As Long
Dim RetVal As Long
Dim WndRect As RECT

XLhwnd = FindWindow("XLMAIN", Application.Caption)
RetVal = GetWindowRect(XLhwnd, WndRect)

With Application
.DisplayFullScreen = True

RetVal = SetWindowPos(XLhwnd, 0, WndRect.Left, WndRect.Top,
WndRect.Right, WndRect.Bottom, SWP_NOZORDER Or SWP_NOACTIVATE)
...rest of your code

NickHK