Part4



The Worksheet objectand Worksheets CollectionChapter SixIn this chapter we consider the properties, methods and events of the Worksheet object, and introduce the concept of the collection as it applies to the Worksheet object. We learn that events can be made to trigger by doing something as simple as clicking on a worksheet.Worksheet properties and methodsProperties MethodsYouTube VBACh6a: VBACh6b: mistake: Cancel – true not Cancel =falseselectionchange change beforedoubleclick beforerightclick calculate crossfire-caldwell codename protectWorksheet properties and methodsTo get Help on ActiveSheet go to the VBA Help.and rather confusingly seek Worksheet Object Members. There you will find the properties and methods of the Worksheet object. (Unfortunately Itellisense doesn’t seem to work after typing the dot after ActiveSheet.)Each worksheet in an Excel workbook is represented by a corresponding VBA Worksheet object. As an object, the Worksheet object has properties and methods. This example demonstrates the Name property and Delete method respectively.Private Sub CommandButton1_Click() MsgBox ActiveSheet.Name ActiveSheet.DeleteEnd SubWhen the above code is run, a message box will appear with the name of the current sheet...2501582183335... followed by a dialog box asking for confirmation.When the dialog box appears asking for confirmation, take care to declinelest you irretrievably delete the very worksheet and it’s module that holds your code!The PrintOut method can also be used to preview a page (only) by settingthe named parameter PreView to True, i.e. ActiveSheet.PrintOut PreView :=True. (We will discuss named parameters in Chapter 10.)Click Cancel!The PrintPreview method can be used to emulate the pressing of the Print Preview button from Excel, e.g.Private Sub CommandButton1_Click() ActiveSheet.PrintPreviewEnd SubSimilarly, if we wish to actually print the sheet we could instead use:ActiveSheet.PrintOutCollectionsA collection is a group of objects which have the same properties and methods. A collection allows us to deal with the objects as a unit. The name of a collection of a group of objects is almostNot all collections are formed by adding “s” to the corresponding object, e.g. Rowsis a collection of Range objects whereas Row itself is a property which indicates a row number.always, very conveniently specified by adding “s” to the object. For example Worksheet objects are contained in the Worksheets collection. Each object in a collection can be referenced by an index. A collection is particularly useful for iterating through a group of objects. For example, if we wished to find the respective names of three workbooks, we could use i as an index to iterate through the collection as follows.Private Sub CommandButton1_Click() Dim i As IntegerFor i = 1 To 3MsgBox Worksheets(i).Name Next iEnd Sub3 is the numberof worksheets in this workbook.Three message boxes will appear with the respective worksheet names.Note that the index of the Worksheet object corresponds to the order in which the worksheets appear at the bottom left of the Excel window. This is most important to remember, in case the end user of the spreadsheet decides to swap them around! (In anticipation of this, CodeName could be used to refer to the sheet in preference to Name as we shall see at the end of this chapter.)We can also refer to a worksheet using a tag (its name) egWorksheets("Sheet1 "). Try running the above code again, but this time first swap the sheets around so that Sheet2 is the first sheet. Now the first message box to appear should display Sheet2 – the first object in the collection.Collection properties and methodsCollections have their own properties and methods.The Count property reveals the number of items in the collection, e.g.Msgbox Worksheets.Countwill indicate the number of worksheets in the Worksheetscollection.Count is particularly useful when we want to loop through a collection as shown below.Private Sub CommandButton1_Click() Dim i As IntegerFor i = 1 To Worksheets.Count Msgbox Worksheets(i).Name Next iEnd SubWorksheets.Count specifies the total number of worksheets.Just as we can add a worksheet from Excel (Insert, Worksheet etc.), we can add a new worksheet by using the Add method of the Worksheets collection to add a new Worksheet object to the Worksheets collection, i.e.Worksheets.Addwill add a new worksheet before the active worksheet.The Remove method, in general is used to remove an item from a collection, but it seems that we are only able to remove a Worksheet from the Worksheets collection via the Delete method of the Worksheet object. For example,Worksheets(1).Deletewill effectively remove the first worksheet object in ourWorksheets collection.The first worksheet at the bottom left of the Excel window will be removed.Take care when using a loop to remove items from a collection since as they aredeleted, the remainder will continually be renumbered! (Hint: If you wish to delete them all, delete them backwards.)If you are not sure what type an object variable should be, use the generic Objecttype, e.g. Dim wks As Object, but is not recommended if speed of execution is important.Another quite amazing feature of the collection object is its ability to renumber itself when an object is added or removed.Accordingly, you will need to keep in mind that Worksheets(3) for example, will not remain Worksheets(3) if a sheet has been added or removed before it.We can declare a variable as a Worksheet type, just as we can declare variables to be of Integer or Range type, e.g:Dim wks As WorksheetIf a collection is clever enough to know how many items it contains (Count), then perhaps we shouldn’t have to specify this number at all - and indeed we don’t! The For Each...Next loop is a special loop which does not require us to specify the number of objects – it will iterate through the collection until it runs out of objects.Private Sub CommandButton1_Click() Dim wks As WorksheetFor Each wks In WorkSheets MsgBox wks.IndexNext wks End SubThe Index numbers of the worksheets will be displayed sequentially.Item is another of the properties of a collection, but it is a default property and accordingly, Worksheets(1).Item(1) for example, can be written equivalently as Worksheets(1), which serves exactly the same purpose.For example,MsgBox WorkSheets.Item(1).Namewould perform exactly the same as:MsgBox WorkSheets(1).NameExercise. Write code to loop through the first 3 worksheets and add 3 respective numbers in cell A1 eg 10,20 & 30. Either msgbox the result or place it in another cell.Cells, columns and rowsCellsEach individual Worksheet can be considered to be a composed of cells. Each individual cell is a Range object.To select all of the cells of the first worksheet we could use:Worksheets(1).Cells.SelectTo select the first cell we could use:Worksheets(1).Cells(1).SelectColumnsCells returns all of the cells of a worksheet.Cells(1) is the first cell.sheet first! e.g.Note that if you wish to Select a range on another worksheet, you must activate theNotice that above we said that the Worksheet object “can be” considered to be a composed of cells, because it can also be considered to be a composed of columns or rows, (each of which is a Range object) depending on what is convenient at the time!The Columns property returns all of the columns of a particularWorksheets(2).Activate Worksheets(2).Cells(1).Selectworksheet. Columns(2) for example, would return the second column on a worksheet. We could therefore select column 2 using:Worksheets(1).Columns(2).Select2501582184784RowsSimilarly, the Rows property returns all of the rows of a particular worksheet, so we could select the second row of the first worksheet with:Worksheets(1).Rows(2).SelectColumns is a property which returns a Range object.Exercise. Write code to transfer a number in cell A1 on Sheet1 to A1 on Sheet2 when we click the button on Sheet1.CountIt is interesting to use the Count property of Columns to determine the number of columns on a sheet. ( Note that we don’t bother to specify the worksheet here - it is assumed to be the active worksheet (ActiveSheet).21316954826000MsgBox Columns.CountSimilarly, use the Count property of Rows to determine the number of rows.21291553683000MsgBox Rows.CountA million rows!ColumnWidthRows and Columns are themselves properties which return collections of rows and columns respectively. To change the width of all of the columns of a spreadsheet for example, we can take advantage of a collection’s ability to deal with all of theobjects in the collection as a unit. For example, to change the width of each column of the active sheet we could useColumns.ColumnWidth = 5where 5 is the width of each column in characters. The above is an example of writing to a property. Since ColumnWidth is a read/write (variant) we could also read the property using:Msgbox Columns.ColumnWidthWorksheet eventsYouTube VBACh6b: As well as having properties and methods, an object can have events. An event is an action that is performed by the user, such as clicking a mouse or pressing a key. An event procedure is code that will “trigger” (via the operating system) whenever thecorresponding event occurs.To write a worksheet event (Selection_Change)1 Make sure that Project Explorer is visible and then double click on thesheet name (Sheet1 in this case) to access its code module.2 Click on the Object drop-down and thenselect Worksheet.The SelectionChange event procedure “template” appears by default . Click in it and write the code shown on the next page.(If you inadvertently create another event, delete it and choose the SelectionChange event procedure.)The SelectionChange event is triggered whenever the user changes the selection on the worksheet, for example if the user clicks on another cell on the same worksheet. Like all other Worksheet events, it will only work for that particular worksheet.Target is the “target” of our mouse click. It is a Range object which is passed (via the operating system) to this event. (ByVal concerns the way that objects are passed to procedures and will be explained later.)Private Sub Worksheet_SelectionChange(ByVal _ Target As Range)MsgBox Target.Address End Sub(Note the line continuation character shown here which you may not require in your code.)When the user clicks on another cell, the message box appears displaying the address of the cell that the user clicked upon.(Make sure that the Design Modebutton is not depressed – your worksheet event will not fire if it is.)Confirm that this event won’t trigger for another worksheet, by selecting a cell on another sheet.The Worksheet_Change eventThe Change event occurs when the user physically changes the contents of a cell on the sheet.(Before starting, make sure to comment out or remove the code in the SelectionChange event from the previous example so that it won’t trigger when we are testing the new event.)1If the code module is not already visible, double-click on the required sheet (inProject Explorer as before.2Click on the Procedure drop- down and choosethe Change event.If, when you click on the Object box, a default event procedure is placed in yourcode sheet that you don’t want, simply highlight it and delete it.Place some code in the Change event which itself changes the contents of a cell,e.g. cells(1,1).Value = 2 .You will get a chain reaction. This newly- changed cell will trigger the3 Place this code in the Worksheet_Change event procedure. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value > 5 Then Target.Font.ColorIndex = 3 End Sub4Change event – which will change the cell – which will trigger the Change event etc. Try also placing a MsgBox in the Change procedure which could confirm that the procedure is being called continuously. To halt it press Ctrl+Break.Application.EnableEvents will enable us to prevent this chain reaction as we shall see later.Type in some numbers.After a number bigger than 5 is entered, it turns red.BeforeRightClick doesn’t mean before you right- click!BeforeRight Click and BeforeDoubleClick are useful procedures fortesting your code as an alternative to the CommandButton1_Click() event.The Worksheet_BeforeRightClick eventBeforeRightClick means after the user has right-clicked, but before the right-click event reaches the operating system and is acted upon. The default behavior for the right-click of course, is for the context-sensitive pop-up menu to appear on the spreadsheet. If we do not wish this to occur after a right-click, set the Cancel property of the event to True as shown below.Private Sub Worksheet_BeforeRightClick _ (ByVal Target As Range, Cancel As Boolean) Cancel = TrueMsgBox "Right Click" End Sub2681922170711The Worksheet_BeforeDoubleClick eventBeforeDoubleClick is very similar to the BeforeRightClick event, except of course to raise the event, the user must double- click a cell, and in this case setting Cancel = True will cause the default cell editing behavior to be cancelled.Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, Cancel As Boolean) Cancel = TrueMsgBox "Double Click" End Sub1Excel will recalculate automatically onlyThe Worksheet_Calculate eventMicrosoft Excel Help (not VBE Help) states “By default, Microsoft Excel automatically recalculates formulas when the cells that the formula depends on have changed.”The Worksheet_Calculate event will “trigger” whenever a recalculation occurs.if Calculation is set to Automatic(From Excel, choose Tools, Options..., Calculation, Automatic), otherwise it isnecessary to press the F9 key toPlace a value in a cell, and a formula that depends upon it in another. (See below.) Place the Worksheet_Calculate code shown below in the code module of the worksheet.recalculate (all of) the worksheets.Private Sub Worksheet_Calculate() MsgBox "Recalculated"End Sub2Change the value upon which the formula depends. The message box should appear, reminding us that a recalculationhas just taken place. (If your Calculation mode is not set to Automatic, pressing the F9 key will have the same effect.)2501582138475This Worksheet_Calculate event only triggers when a recalculation occurs on this particular worksheet. What if we want the Calculate event to trigger on any worksheet? We need the Workbook_SheetCalculate event of the Workbook object which we will meet in the next chapter.Automatic sheet entryWe have below a workbook which contains three sheets. The first sheet is the Invoices sheet which contains records of recent dividend payments for each customer Caldwell and Crossfire who have their respective account details on the separate,correspondingly-named sheets. We wish to be able to double-click on a customer’s name on the Invoice sheet, and have the respective sheets automatically activated, in order that the payments may then be manually entered on that particular sheet.We wish to double-click on a client name to activate the corresponding sheet.Place the code below into the BeforeDoubleClick event of the Invoices sheet.Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, Cancel As Boolean) Cancel = True Worksheets(Target.Value).ActivateEnd SubThe value in the target cell will be the name of the client, which corresponds to the name of the sheet to be activated.When we double-click on Caldwell (cell A2) for example, in the Invoices sheet, the Caldwell sheet should become activated as shown.2681922144395It would be more useful perhaps, to also automatically update the entries on the chosen sheet. This could best be achieved using the Offset property of the Range object which will be considered in a later chapter. For the moment, suffice it to say that Target.Offset(0,1).Value would return the value contained in the cell which is one cell to the right of a cell that we double-click. With this knowledge, we can actually update the respective sheets as well as choosing them. After double-clicking on the respective names, each of the two sheets ought to be updated with the date and dividend value as is shown here for the Crossfire sheet:2501582169168Since we are not using the Select method to select a cell on the chosen worksheet,it is not essential that we Activate the sheet, i.e. we could omit the Worksheets(Target.Value).Activate line. (Unless we want to immediately view/check the new entry.)The following code should achieve that.Private Sub Worksheet_BeforeDoubleClick(ByVal _ Target As Range, Cancel As Boolean)Cancel = True Worksheets(Target.Value).Activate Worksheets(Target.Value).Cells(2, 1).Value = _ Target.Offset(0, 1).Value Worksheets(Target.Value).Cells(2, 2).Value = _ Target.Offset(0, 2).ValueEnd SubAt present, if we double-click on a cell with no contents, an error will occur. Toprovide for this, we could either trap the run-time error, or insist by means of a message box that the user double-clicks only the permissible cells. Both of remedies will be considered later.Target.Offset(0,2) is the cell which is 2 cells to the right of the cell double-clicked upon on the Invoices sheet, which contains the Dividend value. (Offset(0,1) contains the Date.)Target.Value is the name of the worksheet on which the values will be placed.Cells(2,1) and Cells(2,2) is where the Date and Dividend value respectively will be placed on this sheet.Worksheet namesA worksheet has two names, one which can be changed by the user, and one which can only be changed by the programmer. To change the former, of course we proceed as follows.From Excel, change the name of the worksheet by double-clicking on the name and typing a new name.We wish to inspect this new name in the VBE, and to also change the CodeName. From the VBE, view the Properties window of the worksheet. (Choose View, Properties Window.)Ensure the Worksheet is selected.(Name) (with the parentheses) specifies the CodeName of the Worksheet object. Change it to myCodeName.(The name of the worksheet which we changed to Income is represented by the Name property of the Worksheet object.)(The CodeName has now been changed to myCodeName.)Project Explorer can also be used to view the Name and the Code Name. Choose View, Project Explorer.The CodeNameThe NameWe are familiar with using the Name property to refer to a worksheet from code – for example to protect a sheet:Private Sub CommandButton1_Click() Worksheets("Income").ProtectEnd Sub(This of course is equivalent to choosing, Tools, Protection, Protect Sheet... from the Excel window. To unprotect the sheet, choose Tools, Protection, Unprotect Sheet... , or use the Unprotect method from code.)The equivalent using CodeName would be:Private Sub CommandButton1_Click() myCodeName.ProtectEnd SubWhen writing code, it is good practise to try to use the CodeName to refer to a worksheet rather than the Name. The problem with using the Name is that a user is able to change the name of the sheet, in which case your code will surely fail. Since it is veryunlikely that the user will change the CodeName, this is a better bet. Note that the default name for both the Name and the CodeName are the same initially, e.g. Sheet1, so that for example, Worksheets("Sheet1").Protect and Sheet1.Protect would be equally acceptable!Exercises: (Solutions page 14 of vbaClassSolutions.docx)1. Write some code which changes the names of all of the worksheets in a workbook to upper case upon double-clicking on a particular sheet (only works for this sheet). Use UCase().2. We have some worksheets named A,B,C,..Z, Summary. 10795019050Each of them has some data in Cells A1. Write code to iterate thru the sheets A->Z only (ie not Summary) one at a time and Msgbox the data in the cell A1 of each sheet each time.3. (Deleted)4. Write some code which will move the contents one cell to the right when we double-click on it. 5. We would like to be able to double-click on a fullname (see below) whereupon it would be separated into Title, First Name & Surname as shown. We have previously written code (see in box below) to perform this when we selected the cell and then clicked a button. Modify that code to use the double-click event.pos1 = InStr(rng, " ")pos2 = InStr(pos1 + 1, rng, " ")title = Left(rng, pos1 - 1) 'Titlefn = Mid(rng, pos1 + 1, pos2 - pos1 - 1) 'First Namesn = Mid(rng, pos2 + 1) 'Surname.You might like to try InstrRev for a change?Exercise:1. Make the interior of a cell mauve coloured when we double-click on it. Interior.ColorIndex = xlAutomatic ' Clears all Target.Interior.ColorIndex = 24 ' This cell given Mauve interior1. Iterate thru the range to find the mauve cell. Msgbox its contents.Optional (Best done when we talk about Intersect and Workbooks.)2. Open the corresponding w/book. (Do this bit after the next chapter.)6. We have 3 worksheets eg Sheet1, Sheet2 and Sheet3.Place a button on Sheet3 which takes a value from Sheet1 A1 and adds it to the value fromSheet2 A1. Place the result in Sheet3 A1. ie:329628512509500152463510985500-381011049000 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download