This is a continuation of my last Kiosk Full Screen Excel article for 2007/2010.
Here I will describe how to create a true full screen look for Excel 2007/2010. Here is an index of the topics we will cover. I’ll include code snippets and my logic behind the type of code I use. I hope you find it useful.
1. Hide the system taskbar
2. Iterating worksheets in kiosk mode
3. Exit kiosk mode
1. Hide the system taskbar
Ok, so we have a lot of options when it comes to hiding the system taskbar, but we are slightly governed in one respect because we are going to be connecting to Excel’s full screen view, we can’t do this and use API calls to move/switch the size of the Excel application. MDI window to simply cover the taskbar. So we know that we need to completely hide the taskbar every time it starts up; now we have to determine how.
One method is to control the taskbar window and disable it. The other method is to toggle/autohide the taskbar like you would do manually by right-clicking on Properties and then setting the Autohide taskbar checkbox. The reason I opt for the latter is that:
a) The Windows 7 operating system has some trouble hiding the start button completely (you can almost do it, but you still get a little button curve which is unsightly. Well that’s what I’ve found, either that or the button is still completely visible – it’s a little sore.
b) If for some reason you can’t restore the taskbar, users won’t appreciate you having them go into Control Panel and reset the taskbar in Start menu and taskbar settings.
c) Finally, I found that in 2007/2010 and operating systems like XP, Vista or Windows 7, you can launch full screen with a switch and the taskbar stays hidden while Excel fills the whole screen when you call the application. DisplayFullscreen = True.
So it is for the above reasons that I chose and continue to use the following code in my Kiosk software in a VBA module:
hide taskbar()
show taskbar()
explicit option
private declaration function_
SHAppBarMessage Lib “shell32.dll”_
(ByVal dwMessage always, _
ByRef pData As APPBARDATA) While
Private Const ABS_AUTOHIDE Whenever = &H1
Private Cont. ABM_GETTASKBARPOS Whenever = &H5
Private const ABM_GETSTATE whenever = &H4
Private const ABM_SETSTATE While = &HA
Private const ABM_SETAUTOHIDEBAR Whenever = &H8
private type RECT
left so long
up so long
right as always
bottom so long
EndType
Private type APPBARDATA
cbSize as length
hwnd so long
uCallbackMessage always
uEdge so long
rc as RECT
lParam While
end type
Dim and like APPBARDATA
Dim abd_retval, _
abd_setval whenever
‘ AutoHideTaskBar:: sets the appdardata, lParam structure to automatically hide 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)
finish sub
‘ RestoreTaskBar: Restores the auto-hide system taskbar to a normal state
Public SubUnhideTaskbar()
abd_retval = _
SHAppBarMessage(ABM_GETSTATE, abd)
abd.lParam = _
abd_retval and not ABS_AUTOHIDE
abd_setval = _
SHAppBarMessage(ABM_SETSTATE, abd)
finish sub
2. Iterating worksheets in kiosk mode
Next, iteration of worksheets in kiosk mode. Well, obviously, we hide the taskbar and then launch Full Screen. In my kiosk menu, I add the ability to hide gridlines, headers, worksheet tabs, etc. I won’t cover that here as it’s very elementary VBA coding to do these tasks. However, what I will cover and share with you is some code on how to iterate through Excel worksheets skipping hidden or highly hidden sheets both backwards and forwards. There are many functions to navigate one way, but I wanted to create a method to do it.
First, we need to set up an Enum in a VBA code module called b_functions as follows (I’ve given both of my Navigator types a unique integer of 3 for Previous, navigate back, and 2 for Next, navigate forward):
‘browser enum::for worksheet navigation
Public Enum Browser
xlNavigatePrevious = 3
xlNavigateNext = 2
final enumeration
We’ll use the above types to pass a Function called Browser (Address) by using the following code (we won’t return a value):
Iterate the worksheet forward
Call b_functions.Browser(xlNavigateNext)
Iterate the worksheet backwards
Call b_functions.Browser(xlNavigatePrevious)
Here is the function that does the job.
‘ Browser::ally dynamically selects a sheet or chart (hidden, deep hidden ignored) via browser buttons
Public Function Browser (ByVal Address as Browser)
‘vars
Just me, _
j, _
K as integer
Dim DefaultSheet As String ‘selected sheet
DefaultSheet = ActiveWorkbook.ActiveSheet.Name
J = ActiveWorkbook.Sheets.Count
With Active Workbook
For i = 1 For J
If DefaultSheet =.Sheets(i).Name Then
Select case address
Case xlNavigatePrevious ‘ left
If i > J – (J – 1) Then
While.Sheets(i – 1).Visible = xlSheetVeryHidden O _
.Sheets(i – 1).Visible = xlHiddenSheet
If i – 1 = J – (J – 1) Then i = 2: GoTo select_prev Else i = i – 1
heading to
select_previous:
.Sheets(i – 1).Select
go out by
The rest
For K = J For i Step -1
While.Sheets(K).Visible = xlSheetVeryHidden O _
.Sheets(K).Visible = xlHiddenSheet
k = k – 1
heading to
.Leaves(K).Select
go out by
NextK
go out by
It will end if
Case xlNavigateNext ‘ right
If i < J Then
While.Sheets(i + 1).Visible = xlSheetVeryHidden O _
.Sheets(i + 1).Visible = xlHiddenSheet
If i + 1 = J Then i = 0: Go to select_next Otherwise i = i + 1
heading to
select_next:
.Sheets(i + 1).Select
go out by
ElseIf i = J Then
For K = 1 to J
While.Sheets(K).Visible = xlSheetVeryHidden O _
.Sheets(K).Visible = xlHiddenSheet
K = K + 1
heading to
.Leaves(K).Select
go out by
NextK
go out by
It will end if
finish select
It will end if
next me
Finish with
final function
3. Exit kiosk mode
Since Excel 2007 (Version 12, released in 2007), exiting Full Screen mode in Excel was somewhat easier due to the fact that Excel now allowed you to press Esc to exit. However, this raises a real question: how to trace/trap the Application.DisplayFullscreen = False event?
Once again, I considered subclassing the MDI Excel window and listening for window messages. I did pretty well in part (notoriously hard to do in a stable way without a DLL), however it still failed to catch the exit event when a workbook was full screen size and the main Excel window was maximized, all very frustrating. . This is because Exit Full Screen here doesn’t trigger Excel to refresh, so in theory there’s 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 thin timer thread to act as the application’s listener. DisplayFullscreen = false event. After some experimentation, I decided to check for this event every 1/10th of a second, enough to catch even the most enthusiastic user coming out of Kiosk mode.
Here’s the calling code, the subroutines used, the private function prototype declarations (.DLL library), and the code to use the timer thread (again, all stored in a VBA code module called b_functions):
Call b_functions.StartTimer 100
StartTimer(ByVal Duration As Long)
Secondary stop timer()
TimerIsActive() as boolean
Event Timer()
‘set timer
private declaration function_
SetTimer Lib “user32”
(ByVal hWnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc Always) Always
‘killTimer
private declaration function_
KillTimerLib “user32”
(ByVal hWnd As Long, _
ByVal nIDEvent As Long) As Long
‘Go. timer thread
Private m_TimerID for the duration
Private Sub StartTimer (ByVal Duration As Long)
If m_TimerID = 0 Then
m_TimerID = SetTimer(0, 0, Duration, AddressOf TimerEvent)
It will end if
finish sub
private secondary stop timer()
If m_TimerID <> 0 Then
KillTimer 0, m_TimerID
m_TimerID = 0
** TYPE EXIT CODE HERE **
It will end if
finish sub
Private property Get TimerIsActive() as boolean
TimerIsActive = (m_TimerID <> 0)
final property
private child timer event()
If Application.DisplayFullScreen = False Then
StopTimer
It will end if
finish sub
Well, that ends for now. Mark Kubiszyn.