This is a follow-up to my last Full Screen Excel Kiosk article for 2007/2010.
Here I will describe how to create a real full screen appearance 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. Hide the system taskbar
2. Iterating worksheets in kiosk mode
3. Exit kiosk mode
1. Hide the system taskbar
Okay, we have a lot of options when it comes to Hiding the System Taskbar, but we stick slightly in one respect, because because we’re going to connect to Excel’s full-screen view, we can’t do this and use API calls to move / resize Excel. MDI Window App to simply cover the taskbar. So we know that we need to completely hide the taskbar on every launch; now we have to find out how.
One method is to get a handle to the taskbar window and shut it down. The other method is to automatically toggle / hide the taskbar as you would manually by right-clicking Properties and then setting the auto-hide taskbar checkbox. The reason I opt for the latter is that:
a) Windows 7 OS has some trouble completely hiding the start button (you can almost do it, but you still get a little curve from the button which is unsightly. Well that’s what I found, that or the button is still completely visible – it’s a bit painful.
b) If for some reason you cannot restore the taskbar, users will not appreciate you making them go to the Control Panel and reset the taskbar to the taskbar and Start menu settings.
c) Lastly, I found out that in 2007/2010 and on operating systems like XP, Vista or Windows 7, you can launch full screen with a toggle and the taskbar remains hidden while Excel fills the entire screen when you call Application.DisplayFullscreen = True.
So it is for the reasons above that I chose and continue to use the following code in my kiosk software in a VBA module for:
Hide taskbar ()
Show taskbar ()
Explicit option
Private declaration function _
SHAppBarMessage Lib “shell32.dll” _
(ByVal dwMessage as long, _
ByRef pData As APPBARDATA) Whenever
Private const ABS_AUTOHIDE provided = & H1
Private const ABM_GETTASKBARPOS As Long = & H5
Private const ABM_GETSTATE provided = & H4
Private const ABM_SETSTATE As Long = & HA
Private const ABM_SETAUTOHIDEBAR As Long = & H8
Private type RECT
left so long
Up whenever
so long
So long bottom
Final type
APPBARDATA of private type
cbSize so long
hwnd so long
uCallbackMessage so long
uEdge so long
rc as RECT
lParam so long
Final type
Dim abd as APPBARDATA
Dim abd_retval, _
abd_setval whenever
‘AutoHideTaskBar :: set the appdardata, lParam structure to automatically hide the system taskbar
Hide public secondary taskbar ()
abd_retval = _
SHAppBarMessage (ABM_GETSTATE, abd)
abd.lParam = _
abd_retval or ABS_AUTOHIDE
abd_setval = _
SHAppBarMessage (ABM_SETSTATE, abd)
End Sub
‘RestoreTaskBar :: restores the auto-hide system taskbar to a normal state
Sub 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
So on to Iterate worksheets in kiosk mode. Well, obviously we have hidden the taskbar and then launched the full screen. In my kiosk menu I add the ability to hide grid lines, headings, worksheet tabs, etc. I won’t cover that here as it is very elementary VBA encoding to accomplish these tasks. However, what I will cover and share with you is some code on how to iterate Excel worksheets by skipping hidden or heavily hidden sheets both backwards and forwards. There are many functions to navigate one way, but I wanted to create a method to do both.
First, we need to set up an enum in a VBA code module called b_functions as follows (I gave my two browser types a unique integer of 3 for Previous, navigate back, and 2 for Next, navigate forward):
‘browser enumeration :: for worksheet navigation
Public Enumeration Browser
xlNavigatePrevious = 3
xlNavigateNext = 2
End Enum
We will use the above types to pass to a Function called Navigator (Address) by using the following code (we will not 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: dynamically select a sheet or chart (hidden, highly hidden ignored) via browser buttons
Public Function Navigator (ByVal Direction As Navigator)
‘vars
Dim i, _
J, _
K as an integer
Dim DefaultSheet As String ‘selected sheet
DefaultSheet = ActiveWorkbook.ActiveSheet.Name
J = ActiveWorkbook.Sheets.Count
With ActiveWorkbook
For i = 1 TB 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 = xlSheetHidden
If i – 1 = J – (J – 1) Then i = 2: Go to select_prev Else i = i – 1
Head to
select_prev:
.Sheets (i – 1) .Select
Go out for
The rest
For K = J To i Step -1
While.Sheets (K) .Visible = xlSheetVeryHidden O _
.Sheets (K) .Visible = xlSheetHidden
K = K – 1
Head to
.Sheets (K) .Select
Go out for
Next K
Go out for
It will end if
Case xlNavigateNext ‘right
If i
While.Sheets (i + 1) .Visible = xlSheetVeryHidden O _
.Sheets (i + 1) .Visible = xlSheetHidden
If i + 1 = J Then i = 0: Go to select_next Else i = i + 1
Head to
select_next:
.Sheets (i + 1) .Select
Go out for
Otherwise If i = J Then
For K = 1 to J
While.Sheets (K) .Visible = xlSheetVeryHidden O _
.Sheets (K) .Visible = xlSheetHidden
K = K + 1
Head to
.Sheets (K) .Select
Go out for
Next K
Go out for
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 2007), exiting full screen mode in Excel was somewhat easier due to the fact that Excel now allows you to press Esc to exit. However, this raises a real question: how to track / catch the Application.DisplayFullscreen = False event?
Again, I considered subclassing the MDI Excel window and listening to the window messages. I did quite well in part (notoriously difficult to do stably 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 a full screen output here does not trigger any Excel updates, so in theory there are no events 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 the Application listener. DisplayFullscreen = false event. After a bit of experimentation, I decided to check this event every 1/10 of a second, enough to capture even the most enthusiastic user coming out of Kiosk mode.
Here is 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
TimerEvent ()
‘SetTimer
Private declaration function _
SetTimer Lib “user32” _
(ByVal hWnd As Long, _
ByVal nIDEvent So long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) Whenever
‘KillTimer
Private declaration function _
KillTimer Lib “user32” _
(ByVal hWnd As Long, _
ByVal nIDEvent As Long) As Long
‘id of the timer thread
Private m_TimerID as long
Private secondary start timer (ByVal duration whenever needed)
If m_TimerID = 0 Then
m_TimerID = SetTimer (0, 0, Duration, AddressOf TimerEvent)
It will end if
End Sub
Private Secondary Stop Timer ()
If m_TimerID <> 0 Then
KillTimer 0, m_TimerID
m_TimerID = 0
** LEAVE THE CODE HERE **
It will end if
End Sub
Private property Get TimerIsActive () as boolean
TimerIsActive = (m_TimerID <> 0)
Final property
Private Secondary Timer Event ()
If Application.DisplayFullScreen = False, then
StopTimer
It will end if
End Sub
Well, that wraps it up for now. Mark Kubiszyn.