[ad_1]
Excel Full Screen View – The Fundamentals
This is a follow up to my last article Kiosk Full Screen Excel for 2007/2010.
Here I will outline how to create a true Full Screen look for Excel 2007/2010. Here is an Index of the topics we will cover. I will include Code snippets and my logic behind the type of Code I use. I hope you find it useful.
1. Hiding the System Taskbar
2. Iterating WorkSheets in Kiosk Mode
3. Exiting Kiosk Mode
1. Hiding the System Taskbar
OK we have many options when it comes to Hiding the System Taskbar but we are governed slightly in one respect in that because we are going to hook into Excel s own Full Screen view we cannot do this and use API calls to Move/Resize the Excel App MDI Window to simply cover the Taskbar. So we know that we need to completely hide the Taskbar each launch; we now need to ascertain how.
One method is to get a handle to the Taskbar Window and turn it off. The other method is to toggle/auto-hide the Taskbar like you would do manually with a right-click Properties and then set the auto-hide the taskbar checkbox. The reason that I opt for the latter is that:
a) Windows 7 OS has some issues completely hiding the Start Button (you can almost do it, but you still get a tiny curve of the Button which is unsightly. Well that’s what I have found, that or the Button is still completely visible – it is a bit of a pain.
b) If for any reason you fail to restore the Taskbar users will not thank you for making them go into Control Panel and resetting the Taskbar in the Taskbar & Start Menu Settings.
c) Lastly I found out that in 2007/2010 and OS like XP, Vista or Windows 7 you can launch Full Screen with a toggle and the Taskbar remains hidden as Excel fills the entire Screen when you Call Application.DisplayFullscreen = True.
Therefore it is for the above reasons that I chose and still use the following code in my Kiosk Software in a VBA Module by:
HideTaskbar()
UnhideTaskbar()
Option Explicit
Private Declare Function _
SHAppBarMessage Lib “shell32.dll” _
(ByVal dwMessage As Long, _
ByRef pData As APPBARDATA) As Long
Private Const ABS_AUTOHIDE As Long = &H1
Private Const ABM_GETTASKBARPOS As Long = &H5
Private Const ABM_GETSTATE As Long = &H4
Private Const ABM_SETSTATE As Long = &HA
Private Const ABM_SETAUTOHIDEBAR As Long = &H8
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
End Type
Dim abd As APPBARDATA
Dim abd_retval, _
abd_setval As Long
‘ AutoHideTaskBar:: sets the appdardata, lParam struct to autohide the system taskbar
Public Sub HideTaskbar()
abd_retval = _
SHAppBarMessage(ABM_GETSTATE, abd)
abd.lParam = _
abd_retval Or ABS_AUTOHIDE
abd_setval = _
SHAppBarMessage(ABM_SETSTATE, abd)
End Sub
‘ RestoreTaskBar:: restores the autohide system taskbar to a normal state
Public Sub UnhideTaskbar()
abd_retval = _
SHAppBarMessage(ABM_GETSTATE, abd)
abd.lParam = _
abd_retval And Not ABS_AUTOHIDE
abd_setval = _
SHAppBarMessage(ABM_SETSTATE, abd)
End Sub
2. Iterating WorkSheets in Kiosk Mode
Right so on to Iterating WorkSheets in Kiosk Mode. Well obviously we have hidden the Taskbar and then launched Full Screen. In my Kiosk Menu I add the ability to hide Gridlines, Headings, WorkSheet Tabs etc. I will not cover that here as it is very elementary VBA coding to perform these tasks. What I will cover and share with you though is some code on how to iterate Excel WorkSheets skipping over Hidden or Very Hidden Sheets both backward and forward. There are lots of functions out there to browse one way but I wanted to create a method to do either.
First we need to set up an Enum in a VBA Code Module named b_functions as follows (I have given my two Navigator types a unique integer of 3 for Previous, browse backward and 2 for Next, browse forward):
‘ browser enum:: for worksheet navigation
Public Enum Navigator
xlNavigatePrevious = 3
xlNavigateNext = 2
End Enum
We will use the types above to pass into a Function called Browser (Direction) by the use of the following code (we will not return a value):
Iterate the WorkSheet Forwards
Call b_functions.Browser(xlNavigateNext)
Iterate the WorkSheet Backwards
Call b_functions.Browser(xlNavigatePrevious)
Here is the Function that does the work.
‘ Browser:: dynamically select a sheet or chart (hidden, very hidden ignored) via browser buttons
Public Function Browser(ByVal Direction As Navigator)
‘ vars
Dim i, _
J, _
K As Integer
Dim DefaultSheet As String ‘ selected sheet
DefaultSheet = ActiveWorkbook.ActiveSheet.Name
J = ActiveWorkbook.Sheets.Count
With ActiveWorkbook
For i = 1 To J
If DefaultSheet =.Sheets(i).Name Then
Select Case Direction
Case xlNavigatePrevious ‘ left
If i > J – (J – 1) Then
While.Sheets(i – 1).Visible = xlSheetVeryHidden Or _
.Sheets(i – 1).Visible = xlSheetHidden
If i – 1 = J – (J – 1) Then i = 2: GoTo select_prev Else i = i – 1
Wend
select_prev:
.Sheets(i – 1).Select
Exit For
Else
For K = J To i Step -1
While.Sheets(K).Visible = xlSheetVeryHidden Or _
.Sheets(K).Visible = xlSheetHidden
K = K – 1
Wend
.Sheets(K).Select
Exit For
Next K
Exit For
End If
Case xlNavigateNext ‘ right
If i < J Then
While.Sheets(i + 1).Visible = xlSheetVeryHidden Or _
.Sheets(i + 1).Visible = xlSheetHidden
If i + 1 = J Then i = 0: GoTo select_next Else i = i + 1
Wend
select_next:
.Sheets(i + 1).Select
Exit For
ElseIf i = J Then
For K = 1 To J
While.Sheets(K).Visible = xlSheetVeryHidden Or _
.Sheets(K).Visible = xlSheetHidden
K = K + 1
Wend
.Sheets(K).Select
Exit For
Next K
Exit For
End If
End Select
End If
Next i
End With
End Function
3. Exiting Kiosk Mode
Since Excel 2007 (Version 12, released 2007) exiting from Full Screen mode in Excel was somewhat easier due to the fact that Excel now allowed you to press Esc to exit. This however poses one real question – how to track/trap the Application.DisplayFullscreen = False event?
Once again I considered Sub-classing the MDI Excel Window and listening for Window messages. I did it pretty well in part (notoriously difficult to do in a stable manner without a.DLL) however it still failed to trap the Exit event when a WorkBook was Full Screen sized as well as the Excel main Window being Maximised – all very frustrating. This is because an Exit from Full Screen here triggers no refresh from Excel so theoretically there is no event to capture.
So what else could we do? Well the method I chose was to use a Windows API to create a very fine Timer Thread to act as a listener for the Application. DisplayFullscreen = false event. After some experimentation I decided to check for this event every 1/10th of a second – quite sufficient to capture even the most enthusiastic of user exiting the Kiosk mode.
Here is the Call code, the Subroutines used, Private Function Prototype Declarations (.DLL Library’s)and code to use the Timer Thread (again all stored in a VBA Code Module named b_functions):
Call b_functions.StartTimer 100
StartTimer(ByVal Duration As Long)
Sub StopTimer()
TimerIsActive() As Boolean
TimerEvent()
‘ SetTimer
Private Declare Function _
SetTimer Lib “user32” _
(ByVal hWnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long
‘ KillTimer
Private Declare Function _
KillTimer Lib “user32” _
(ByVal hWnd As Long, _
ByVal nIDEvent As Long) As Long
‘ timer thread id
Private m_TimerID As Long
Private Sub StartTimer(ByVal Duration As Long)
If m_TimerID = 0 Then
m_TimerID = SetTimer(0, 0, Duration, AddressOf TimerEvent)
End If
End Sub
Private Sub StopTimer()
If m_TimerID <> 0 Then
KillTimer 0, m_TimerID
m_TimerID = 0
** DO EXIT CODE HERE **
End If
End Sub
Private Property Get TimerIsActive() As Boolean
TimerIsActive = (m_TimerID <> 0)
End Property
Private Sub TimerEvent()
If Application.DisplayFullScreen = False Then
StopTimer
End If
End Sub
Well that about wraps it up for now. Mark Kubiszyn.
[ad_2]
Source by Mark Kubiszyn
