1. Using a Form to interact with a Worksheet

Data Processing	A form as a Switchboard	Adding Data
Modifying Data	Deleting Records	Scope Of Variables
Browsing the data	Practice	Assignment

Learning Outcomes

On completion of this chapter you will know:

· How to use forms in order to activate other forms

· How to use code on a form to activate different worksheets

· How to insert columns or delete columns using code

· The objects Workbook, Activesheet

· The collection Worksheets

· What are the major processing that occur to data

Download pdf version

Introduction

Spreadsheet users frequently use their spreadsheets as a database, in other words they use them to store large volumes of data. The main problem of using this is that a spreadsheet has very limited capabilities of checking or validating the data that is being entered. It is also limited in how it can protect the data.

Some of those problems can be overcome by entering the data under programme control. To do this we enter the data into a form and then use code attached to the form to check and validate the data before storing it in the worksheet.

At first we shall look at a simple application for entering, modifying and browsing the data and then we shall look at a small example of how programme code can be used to validate the data being entered.

Data Processing – Add, Modify, Delete and Browse

Go to top

When dealing with data stored in a computer the only processing available to us are add, modify, delete and browse. If we have an application for processing student data in an educational institution then we normally add new data when we register new students. In other words we add new student records.

When we alter details of existing students then we are modifying existing data. This may involve changing a student’s surname due to the student getting married or correcting any other data item that had been entered wrongly.

Deleting records is not the most frequently performed operation as we normally like to keep records of as many past events as we can.

Browsing is done either by moving from record to record or by searching the data for a student id. Normally browsing does not involve changing the data.

Using a Form as a Switchboard

Go to top

The application we are about to demonstrate will have four components:

1. Entering data records

2. Modifying existing data

3. Deleting records

4. Browsing the existing data

All of those four activities will be carried out by separate forms. In order to unify the application we shall have a main form, from which we can activate the other four forms. The form is shown below in Figure 15‑1

Figure 15‑1

The form’s name is frmMainMenuForm. It has four command buttons named cmdAdd, cmdBrowse, cmdDelete and cmdModify. Below is shown the code attached to each of those buttons.

We need to examine only one of those pieces of code. We shall examine the first one, i.e. cmdAdd_Click – which spans lines 1 – 3.

Line 2 of this procedure calls the Show method of frmAddNewEmployees. What this does is to display the named form on the computer screen so that we can use it to enter data into our workbook. While this form is on display we cannot use either the spreadsheet itself or any other form that was active before.

Listing 15‑1

1	Private Sub cmdAdd_Click()
2	frmAddNewEmployees.Show
3	End Sub
4
5	Private Sub cmdBrowse_Click()
6	frmBrowseEmployees.Show
7	End Sub
8
9	Private Sub cmdDelete_Click()
10	frmDeleteEmployees.Show
11	End Sub
12
13	Private Sub cmdModify_Click()
14	frmModifyEmployee.Show
15	End Sub

Adding Data using a Form

Go to top

The form for adding data is shown below in Figure 15‑3 . It has four text boxes named txtName, txtSurname, txtJobDescription and txtRate. It also has one command button named cmdAdd.

The code for the form is shown in Listing 15‑2. It will add the data in the textboxes to a worksheet named Workers List – shown below in Figure 15‑2. This already has labels inserted across Row 1. The first record will be added into row 2. before the next record is added a new row is inserted at Row 2 thus pushing down the previous record. The new data is entered into the new Row 2. This process continues for all records added. Thus the most recent record is at the top and the oldest record is at the bottom.

Figure 15‑2

Figure 15‑3

Listing 15‑2

1	Dim wb As Workbook
2
3	Private Sub UserForm_Activate()
4	Set wb = ThisWorkbook
5	wb.Worksheets("Workers List").Activate
6	End Sub
7
8	Private Sub cmdAdd_Click()
9	Rows("2:2").Select
10	Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
11	ActiveSheet.Cells(2, 1).Value = txtName.Text
12	ActiveSheet.Cells(2, 2).Value = txtSurname.Text
13	ActiveSheet.Cells(2, 3).Value = txtJobDescription.Text
14	ActiveSheet.Cells(2, 4).Value = txtRate.Text
15	End Sub

The code above is listed in the order in which the different procedures are run. The first procedure, i.e. that which spans lines 3 – 6, is run on the Activate event of the form. The Activate event of a form occurs just before the form appears on the screen in Form mode. In the procedure for this event it is common practice to put code that sets up the environment for the application to run successfully. In our case, since we are to put our data into the worksheet Workers List we first ensure that that worksheet is the active one.

The first step here is to ensure that the variable wb points to the workbook that we are using. This occurs in line 4

In line 5 we activate the worksheet Workers List. The logic of the line is equivalent to “Of all the worksheets in the current workbook select the one named ‘Workers List’ and make it the active sheet” Manually we would activate the same sheet by clicking on it.

The code for actually adding the data to the current worksheet is included in the click event of the command button cmdAdd, which spans lines 8 – 15. As stated earlier data is added to the worksheet by inserting a blank row just above the current row 2 and inserting the data into the newly created blank row.

Line 9 selects the current row 2. (Manually this would be done by clicking on the 2 in the row header bar.) Line 10 inserts an extra row at row 2 and pushes all other rows down one place. This leaves a blank row into which data can be entered. This is done in lines 11 – 14.

The method of entering data into the worksheet’s cells is different from the method used in Listing 13‑1 In this case the cells are identified by their row/column coordinates. Thus in line 11 the code piece ActiveSheet.Cells(2, 1) means the cell the second row and the first column meet. This clearly refers to cell A2. Similarly in line 12 ActiveSheet.Cells(2, 2) means where the second row and the second column meet – which means cell B2. Thus lines 11 – 14 insert the contents of the four textboxes on the form into the cells A2, B2, C2 and D2.

Modifying existing Data using a Form

Go to top

Figure 15‑4

The form for modifying existing data is shown above. It contains the same four textboxes as the form for adding new data but the buttons are different. The names of the current buttons are cmdNext and cmdModify.

The processing of the data is more complex than that for adding new data. When adding new data we only needed to insert a blank row at row 2 and then copy the contents of our four textboxes into the first four columns of the new row 2. For modifying the data we must first display the data in the textboxes. If the user wishes to change the data he makes the alterations in the textboxes and then clicks the Modify button, which writes the data back to the worksheet. If the user does not wish to modify the record on display then he clicks the Next button in order to move to the next record. Thus our processing must be capable of doing the following:

· Displaying data on the form’s textboxes

· Moving from one record to the next one

· Saving the contents of the textboxes back to the worksheet.

Listing 15‑3 below shows the code

Listing 15‑3

1	Dim wb As Workbook
2	Dim intCurrentRecord As Integer
3
4	Private Sub UserForm_Activate()
5	Set wb = ThisWorkbook
6	wb.Worksheets("Workers List").Activate
7	intCurrentRecord = 2
8	showData
9	End Sub
10
11	Private Sub cmdModify_Click()
12	ActiveSheet.Cells(intCurrentRecord, 1).Value = txtName.Text
13	ActiveSheet.Cells(intCurrentRecord, 2).Value = txtSurname.Text
14	ActiveSheet.Cells(intCurrentRecord, 3).Value = txtJobDescription.Text
15	ActiveSheet.Cells(intCurrentRecord, 4).Value = txtRate.Text
16	End Sub
17
18	Private Sub cmdNext_Click()
19	intCurrentRecord = intCurrentRecord + 1
20	showData
21	End Sub
22
23	Private Sub showData()
24	If ActiveSheet.Cells(intCurrentRecord, 1).Value = "" Then
25	MsgBox "No records to display"
26	intCurrentRecord = intCurrentRecord - 1
27	Else
28	txtName.Text = ActiveSheet.Cells(intCurrentRecord, 1).Value
29	txtSurname.Text = ActiveSheet.Cells(intCurrentRecord, 2).Value
30	txtJobDescription.Text = ActiveSheet.Cells(intCurrentRecord, 3).Value
31	txtRate.Text = ActiveSheet.Cells(intCurrentRecord, 4).Value
32	End If
33	End Sub

In the global area of the form we have declared two variables this time: wb and intCurrentRecord. The second variable is to be used for indicating which record in our list we are currently dealing with. The value of intCurrentRecord will be the number of the row whose contents we are currently looking at or modifying.

The first procedure to run is the code attached to the Activate event of the form. This spans lines 4 – 9. The first two lines of code are identical to those of the code in Listing 15‑2 and perform the same function.

In Line 7 we set the value of intCurrentRecord to 2 and in the next line call the procedure showData in order to display the contents of the first four columns of row 2.

The next procedure is the Click event of cmdModify. This is almost identical to the procedure cmdSave_Click from Listing 15‑2 in that it writes the contents of the four textboxes of the form back to the worksheet. The only difference is that this time the value of the variable intCurrentRecord is used to determine into which row the data is written.

The next procedure is the Click event of cmdNext. The code here simply moves the focus to the next employee record and displays the data. Thus the first action at line 19 is to increase the value of intCurrentRecord and then call the procedure showData.

The procedure showData itself spans lines 23 – 33. The body consists of an If..Then..Else construct. Line 24 tests if the first column of the row we are about to display is a blank. (this would occur if either there were no records in the worksheet to begin with, or, more likely, if continuous pressing of the Next button had moved us past the end of the data. If cell is blank then lines 25 and 26 are executed and the rest of the procedure is skipped.

On the other hand if the cell is not blank then there is data to display and the body of the Else is executed i.e. lines 28 – 31. This is simply the reverse of the cmdModify_Click above in that the contents of the first four columns of the row pointed to by intCurrentRow are copied to the four textboxes on the form.

Deleting Records

Go to top

The form that controls the deleting of records is very different from any of the other forms we have used in this example. It is shown in Figure 15‑5 below. It has only two controls: a list box and a command button. The list box is named lstNames and the command button is named cmdDelete.

A list box is similar to a combo box in that it has a list of items and the user can select any one of those items simply by clicking on that item. In order for the programme to work out which item is selected the list box has a property called ListIndex. This is an integer and it gives the sequence number of the item selected. One thing to remember regarding the ListIndex property is that it starts counting at zero. Thus if the name Harry Potter is selected from the list below then ListIndex will have a value of zero. Similarly if Dick Smith is selected then ListIndex has a value of 1, while, if Tom Thumb is selected it has a value of 2. Finally if none of the items are selected then ListIndex has a value of -1.

The sequence of our processing will be as follows:

1. The form loads

2. The Activate event will read the first two columns of each row of data, i.e. the name and surname, and add those to the list of lstNames.

Once those two steps are accomplished the user is presented with something that looks like Figure 15‑5 below. Now let us examine the code that got us this far as well as the code that runs when we click on the button cmdDelete.

Figure 15‑5

Listing 15‑4

1	Dim wb As Workbook
2	Dim intCurrentRecord As Integer
3
4	Private Sub UserForm_Activate()
5	Set wb = ThisWorkbook
6	wb.Worksheets("Workers List").Activate
7	loadNames
8	End Sub
9
10	Sub loadNames()
11	Dim strFullname As String
12	lstNames.Clear
13	intCurrentRecord = 2
14	While ActiveSheet.Cells(intCurrentRecord, 1).Value <> ""
15	strFullname = ActiveSheet.Cells(intCurrentRecord, 1).Value & " " & ActiveSheet.Cells(intCurrentRecord, 2).Value
16	lstNames.AddItem strFullname
17	intCurrentRecord = intCurrentRecord + 1
18	Wend
19	intCurrentRecord = intCurrentRecord - 1
20	End Sub
21
22	Private Sub cmdDelete_Click()
23	Dim intListIndex As Integer
24	intListIndex = lstNames.ListIndex
25	If intListIndex = -1 Then
26	MsgBox "No name selected from list"
27	Else
28	intCurrentRecord = intListIndex + 2
29	ActiveSheet.Rows(intCurrentRecord).Select
30	Selection.Delete Shift:=xlUp
31	loadNames
32	End If
33	End Sub

We will examine the procedures in the code section above in the order in which they are run once the form loads. Obviously the first to run will be UserForm_Activate, which spans lines 4 – 8. Lines 5 and 6 work in the same way as they did for all of the other forms we have examined and so we will leave them alone. Line 7 calls the procedure loadNames.

The procedure loadNames spans lines 10 – 20. Line 11 declares a String variable strFullname which will hold the values of the first two columns of whichever row we are examining. Thus, if we were dealing with a worksheet like that in Figure 15‑2 above, and if we were at row 3, then strFullname would be “Dick Smith”.

Line 12 uses the Clear method of the list box in order to remove any items that may be there already.

Line 13 sets the value of intCurrentRecord to 2 so that we can begin searching our list beginning at row 2.

Lines 14 – 18 consist of a While loop. Line 14 itself tests if the first cell of the current row has got a value in it. If it does then line 15 will read the values in the first and second cells of the current row, put a space between them and store the result in the variable strFullname.

Line 16 uses the AddItem method of the listbox in order to add the value of strFullname to the box’s list.

Line 17 increases the value of intCurrentRecord by 1 so we can examine the next row of the worksheet.

Line 18 throws control back to line 14 where the first cell in the new row is tested for being blank. If it is then control passes to line 19 where intCurrentRecord is reduced by 1 so that it will point at the last record in the worksheet.

Finally we look at the Click event of cmdDelete. This spans lines 22 – 33 It uses the ListIndex property of the list box and thus we need to explain it here before proceeding with examining the code.

If we look at Figure 15‑5 we see a list box with three names, Harry Potter being the first, Dick Smith the second and Tom Thumb the third. If the user clicked on Harry Potter then the ListIndex property would have a value of 0, while if he clicked on Dick Smith then ListIndex would have a value of 1. Clearly it would have a value of 2 if the user clicked on Tom Thumb. Looking again at Figure 15‑5 we see that none of the three names are selected, which means that ListIndex has a value of -1.

Returning back to our code we can examine how the ListIndex property is used by the procedure in order to remove a record from the worksheet.

At line 23 a variable intListIndex is declared as an integer. Its name clearly indicates that it will be used to store the value of the ListIndex property of the list box and in line 24 that’s what exactly happens, the value of ListIndex is copied directly into intListIndex.

The rest of the code, i.e. lines 25 – 32, consist of an If..Else..EndIF construct. In line 25 the value of intListIndex is tested for having the value of -1, in other words testing whether an item has been selected from the list box. If the test is false, i.e. if an item has been selected from the list then the Else part of the construct is executed.

At line 28 intCurrentRecord is valued as intListIndex + 2. The reason for this is that the listbox starts to count at zero, while in the worksheet the first record begins at row 2. Thus in order to find the record in the worksheet that corresponds to the item selected from the list we simply add 2 to the value of ListIndex.

Line 29 selects the row where the record to be deleted is in while line 30 runs the code for removing that row from the worksheet.

Once the record has been removed from the worksheet there is no longer a direct correspondence between the data in the worksheet and the list of lstNames. This is because lstNames still holds a reference to the record that has been deleted. Thus, in order to have a one-to-one correspondence between the two again we must call loadNames at line 31.

Scope of Variables

Go to top

Before continuing with examining the data processing we must pause and consider a concept that has been introduced in Listing 15‑4 without being mentioned. This is the concept of global and local variables. A global variable, as the name implies, is visible to every procedure in the application while a local variable is visible only inside the procedure in which it is declared.

If we look at the first two lines of Listing 15‑4 we see that two variables are declared – wb and intCurrentRecord. Those two variables are declared outside of all of the procedures and for this reason all procedures can see them. The variable intCurrentRecord is used in the procedures loadNames and cmdDelete_Click. On the other hand cmdDelete_Click declares its own variable intListIndex at line 23. Because this variable has been declared inside the procedure it is visible only between the lines 22 and 33 and is invisible to every other procedure in the application. Thus cmdDelete_Click can see the variable intCurrentRecord because it is declared outside of all the procedures and it can see intListIndex because it is declared inside the procedure itself.

Similarly the procedure loadNames declares the variable strFullname at line 11. Thus this procedure can see both intCurrentRecord, again because it is declared global, and it can see its own variable strFullname. Again strFullname can only be seen between the lines 10 and 20.

Browsing the Data

Go to top

Browsing data in a file means being able to move from one record to another, both from the beginning towards the end and backwards. One must also be able to jump directly to the first record and the last record. When designing the code for such an application one must also ensure that the user is not allowed past either the first record or the last one. Thus, referring to the small example in Figure 15‑2 the user should not be allowed to browse above row 2 or below row 4. Below is the form we are using for browsing. The four textboxes are named as their predecessors were while the four command buttons are named cmdFirst, cmdLast, cmNext and cmdPrevious.

Also it is common in an application that allows browsing of records, that when the application starts up the first record is actually displayed.

Listing 15‑5 shows the code behind the form.

Figure 15‑6

Listing 15‑5

1	Dim wb As Workbook
2	Dim intCurrentRecord As Integer
3	Dim intLastRecord As Integer
4
5	Private Sub UserForm_Activate()
6	Set wb = ThisWorkbook
7	wb.Worksheets("Workers List").Activate
8	intCurrentRecord = 2
9	intLastRecord = 1
10	While ActiveSheet.Cells(intCurrentRecord, 1).Value <> ""
11	intCurrentRecord = intCurrentRecord + 1
12	Wend
13	intLastRecord = intCurrentRecord - 1
14	intCurrentRecord = 2
15	If intLastRecord <> 1 Then showData
16	End Sub
17	Private Sub cmdFirst_Click()
18	If intLastRecord <> 1 Then
19	intCurrentRecord = 2
20	showData
21	End If
22	End Sub
23
24	Private Sub cmdLast_Click()
25	If intLastRecord <> 1 Then
26	intCurrentRecord = intLastRecord
27	showData
28	End If
29	End Sub
30
31	Private Sub cmdNext_Click()
32	If intLastRecord <> 1 And intCurrentRecord <> intLastRecord Then
33	intCurrentRecord = intCurrentRecord + 1
34	showData
35	End If
36	End Sub
37
38	Private Sub cmdPrevious_Click()
39	If intLastRecord <> 1 And intCurrentRecord > 2 Then
40	intCurrentRecord = intCurrentRecord - 1
41	showData
42	End If
43	End Sub
44	Sub showData()
45	With ActiveSheet
46	txtName.Text = .Cells(intCurrentRecord, 1)
47	txtSurname.Text = .Cells(intCurrentRecord, 2)
48	txtJobDescription.Text = .Cells(intCurrentRecord, 3)
49	txtRate.Text = .Cells(intCurrentRecord, 4)
50	End With
51	End Sub

In lines 1 – 3 we have our global variable declaration. This time, however, we have an extra variable, intLastRecord. The reason for this variable is that the code will need to know where the last record in the worksheet is. Therefore our first job, once we have activated the worksheet, will be to search down through the worksheet, counting the rows as we go down, until we reach an empty row. Once we reach this we will have counted one row more than we have records and therefore we subtract 1 from our counter to give us the position of the last record in our worksheet.

What we have just described is part of the processing that occurs in in UserForm_Activate. So now let us look at this procedure in detail. The procedure itself spans lines 5 to 16 – it’s the longest version of it that we have had so far. Lines 6 and 7 should be familiar by now and so we shall start with line 8. Here we set intCurrentRecord to 2. The variable is therefore pointing to the first row of the worksheet where we store our data..

Line 9 sets our new variable intLastRecord to 1. There is no data in row 1, only the labels and thus intLastRecord having a value of 1 means that there are no records in the worksheet.

Lines 10 – 12 is a While loop. The first time through the loop intCurrentRecord will have a value of 2. Thus line 10 will test if the value of cell(2,1), i.e. cell A2 is blank. If it is not then intCurrentRecord is incremented by 1 and line 12 will throw control back to line 10 where now cell(3,1) i.e. A3 is tested for being blank. This process continues until the first blank cell is found in column A. Once a blank cell is found then the condition at line 10 will be false and control will pass to line 13. Here intLastRecord is given the value of the number of the last row in the worksheet that contains data.

Line 14 sets the value of intCurrentRecord to 2, or in other words it is pointing to the first row in the worksheet that contains data.

Line 15 tests if the value of intLastRecord is not 1 and if this is the case then showData is called.

Next we shall look at the code for displaying the first record – cmdFirst_Click. This procedure spans lines 17 – 22. The body is an If..End If structure. Line 18 tests if intLastRecord is not equal to 1, i.e. that there are records in the worksheet to display. If there are records to display, i.e. if intLastRecord is some value other than 1, then intCurrentRecord is set to 2 i.e. the first record in the worksheet and then showData is called to display the contents of that record.

The code for finding the last record spans lines 24 – 29. The logic is exactly the same as that for finding the first record. The only difference is in line 26 where intCurrentRecord is set to the value of intLastRecord, so that showData will display the contents of the last record.

The code for finding the next record spans lines 31 – 36. The logic here is slightly more complex than the two procedures we already have examined. The body of the procedure is still an If..End IF structure but the condition of the If at line 32 is more complex. As well as testing if there are records to display, i.e. if intLastRecord is not equal to 1, it also tests that intCurrentRecord is not equal to intLastRecord. The reason for this second test is that if intCurrentRecord has the same value as intLastRecord, it means that we are already at the end of the data and that there is no more data to display. The body of the If construct is therefore only executed if intLastRecord has a value greater than 1 and intCurrentRecord is less than intLastRecord. If both of those conditions are true then the body of the structure is executed i.e. intCurrentRecord is incremented by 1 and the procedure showData is called.

The code for moving to the previous record spans lines 38 – 43. Logic is similar to the procedure we have just described except that in line 39 the second condition is that intCurrentRecord is greater than 2. The reason for this second condition is that if intCurrentRecord already has a value of 2 then we are already at the first record and we cannot go above it. Apart from this the rest of the logic is the same.

Finally the procedure showData, which spans lines 44 – 51, displays the data in the form’s textboxes. It does this by copying the contents of the first four cells of the current row into the textboxes txtName, txtSurname, txtJobDescription and txtRate.

Practice

Go to top

Create the five forms discussed in this chapter and add the appropriate controls to each form. Ensure that the controls are named appropriately. Now copy the code belonging to each form. Finally run the application and check that it performs each task correctly.

Assignment Part 11

Go to top

Once again you have to modify the previous part of the assignment, this time Part 10. Once you have finished you will have a simple version of the software that supermarkets use at the checkouts. There are two major functions that this software performs: it calculates the customer’s total purchases and it also updates the stock file.

In order to function the software is attached to a database file, which contains details of the stock that the supermarket holds. Among those details are: description of the stock, its unit price and the amount of that item in stock. Whenever a customer’s purchase is passed over the scanner the following processing occurs:

1. The record for that stock item is accessed in the database file

2. Its unit price is read and is added to the customer’s total

3. The amount in stock for that item is reduced by 1

This assignment is, of course, a very simplified version of the one the supermarkets use. However, by completing this assignment, you will have gained knowledge of the programming techniques required for creating a major piece of software to be used in commercial data processing.

The specifications for this assignment are:

1. Add two worksheets to your workbook and name them “Sales” and “Stock” respectively. The sheet “Stock” will contain details of the stock the supermarket holds and should look as Figure 15‑7 below. The sheet “Sales” will record the sale of each individual stock item in exactly the same way as the text file recorded it up to now.
Of course you may still update the text file, along with the worksheet, as one will act as a crosscheck on the other.

2. On the form the textbox txtDescription will be replaced with a combo box named cmbDescription. On the Activate event of the form the “Stock” sheet is activated and the contents of Column A are added to the list of the combo box cmbDescription. Check Listing 15‑4 to see how this is done.

3. When processing a sale the user first enters the customer name. Next he selects an item from the drop down list of the combo box. On the Change event of this control the appropriate record is accessed in the file “Stock” and the unit price is read and is placed in the textbox txtUnitPrice.

4. The user then enters the amount of that item that has been purchased into the textbox txtAmtSold.

5. To record this sale the user clicks the Save button. This causes the contents of the form’s text boxes to be written to the worksheet “Sales”. Refer to Listing 15‑2 to find out how to do this. Next the appropriate record in the “Stock” file is accessed and the amount of the item sold is subtracted from the current stock figure.
You may wish, as stated earlier, to also write the contents of the form’s text boxes to the text file that you have used up to now.

Figure 15‑7