Opening and Closing the Battlecell Workbook

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

Several of the requirements for the Battlecell program can best be satisfied using the Open(), BeforeClose(), and WindowResize() events of the Workbook object. The Open() event is triggered when a workbook file (.xls extension) is first opened, thus making it an ideal location for sizing both the application and workbook windows. The WindowResize() event procedure is triggered whenever the user resizes the workbook window, so it must also include code that ensures the Battlecell game board is in the user's viewable range. Since two event procedures must resize the game board, I will write a custom sub procedure that handles this task and call it from the event procedures. To resize the workbook window such that the game board is completely visible, I can increase or decrease the zoom (found on the Standard toolbar) programmatically.

You may be wondering how I knew the Open(), BeforeClose(), and WindowResize() events of the Workbook object existed. Part of it is experience, but requirement number 2 clearly tells me to look for an event procedure associated with the Workbook object; thus, I opened a code module for the Workbook object and searched the names of the event procedures in the drop-down list box. After searching the online help describing these event procedures, I settled on these three events for satisfying the program requirements.

As you gain experience with VBA, you will not only remember more tools available to use in your programs, but you will also learn how to find what is available and find it quickly.

The code I have written for the Workbook object's code module follows:

Option Explicit

Private Sub Workbook_Open()

'Maximize the application and workbook windows, then use the 'worksheet zoom to change the viewable area of the worksheet

Application.ScreenUpdating = False Range("A1").Select

Application.WindowState = xlMaximized ActiveWindow.WindowState = xlMaximized ZoomGameBoard End Sub

The Open() event of the Workbook object is triggered when the Excel file is opened by the user. It's an excellent location for code that initializes the appearance of the workbook and/or specific worksheets. I have used it here to maximize the Excel application window and the workbook window by setting the WindowState property of the Application and Window objects to the VBA-defined constant xlMaximized. The ZoomGameBoard() sub procedure (listed later) is called in order to zoom in or out on the workbook window such that the game board fits within the user's visible range.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Reset the board and save the workbook.

Dim cmdObj As OLEObject Battlesheet.ClearBoard

Set cmdObj = ActiveSheet.OLEObjects("cmdStart") cmdObj.Enabled = True

If Not Me.Saved Then Me.Save End Sub

The BeforeClose() event of the Workbook object is triggered when the user closes the workbook. This procedure actually executes before the workbook is closed. I have used this event to clear the Battlecell game board and re-save the workbook file that contains the game. The board is cleared by calling the ClearBoard() sub procedure listed in the object module for the Worksheet object named Battlesheet. The ClearBoard() sub procedure must have public scope because it is accessed from more than one code module. The object module containing the ClearBoard() procedure must be qualified in the path (Battlesheet.ClearBoard) because the procedure is contained in an object module.

Take a close look at the BeforeClose() event procedure as it contains an element that is probably unfamiliar. You will notice that I have declared an object variable of type OLEObject. ActiveX controls placed on worksheets are part of the OLEObjects collection object. Thus, in order to enable the Command Button control named cmdStart, I must access the control by setting an object reference to a variable (cmdObj in this case) via the OLEobjects collection object. Once the variable reference is set, I can change its Enabled property to true.

The last task before closing the workbook is to save it using the Save() method of the Workbook object. If the Saved property of the Workbook object returns false, then the Save() method is used to resave the Battlecell.xls workbook.

You can use the Me keyword to refer to the current instance of an object currently in scope. All procedures associated with the current object have access to the object referred to by Me. For example, when the Me keyword is used in the BeforeClose() event procedure of the Workbook object, it references the Workbook object. You could also reference the Workbook object in the Battlecell game using Workbooks("BattleCell.xls").Saved or ThisWorkbook.Saved.

Private Sub Workbook_WindowResize(ByVal Wn As Window)

'Use the worksheet zoom to change the viewable area of the sheet.

Application.ScreenUpdating = False Range("A1").Select ZoomGameBoard End Sub

The WindowResize() event is triggered whenever the user resizes the workbook window via its window icons in the upper-right corner, or by dragging an edge or corner of the window. I have used the WindowResize() event procedure to call the ZoomGameBoard() sub procedure. Note that the SreenUpdating property of the Application object is set to false so that the changes made to the appearance of the workbook will not be seen by the user until the procedure as ended (End Sub).

Private Sub ZoomGameBoard()

'Set worksheet zoom such that about 600 cells are visible.

Const NUMCELLS = 550

Select Case ActiveWindow.VisibleRange.Cells.Count Case Is <= NUMCELLS

Do Until (ActiveWindow.VisibleRange.Cells.Count >= NUMCELLS) ActiveWindow.Zoom = ActiveWindow.Zoom - 2

Loop Case Else

Do Until (ActiveWindow.VisibleRange.Cells.Count <= NUMCELLS) ActiveWindow.Zoom = ActiveWindow.Zoom + 2

Loop End Select End Sub

The ZoomGameBoard() sub procedure increases or decreases the workbook zoom (found on the Standard toolbar) in order to keep the Battlecell game board within the user's viewable range. The game board uses rows 1 through 18 and columns A through Y, which represents 450 total cells. So I use the Count property of the Range object to return the number of cells in the range returned by the VisibleRange property of the Window object. If the visible range is too small (not enough cells are visible), then the zoom is decreased and vice versa. I increase the number to 550 to ensure a little cushion around the range of cells used by the game.

Was this article helpful?

0 0
Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook

Post a comment