Excel 2010 VBA Lesson 19: The Worksheet Object

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInEmail this to someone

 [Lesson 18]<<[Table of Contents]>>[Lesson 20]

19.1 The Worksheet Properties in Excel 2010 VBA 

Similar to the Range Object, the Worksheet has its own set of properties and methods. When we write Excel 2010 VBA  code involving the Worksheet object, we use Worksheets.

Some of the common properties of the worksheet are name, count, cells, columns, rows and columnWidth.

Example 19.1

Private Sub CommandButton1_Click()
 MsgBox Worksheets(1).Name
End Sub



Running the code will produce a pop-up dialog that displays the worksheet name as sheet 1, as shown in Figure 19.1.

Figure 19.1

The count property returns the number of worksheets in an opened workbook.

Example 19.2

Private Sub CommandButton1_Click()
 MsgBox Worksheets.Count
End Sub


Figure 19.2: The runtime screen




Example 19.3

The count property in this example will return the number of columns in the worksheet.

Private Sub CommandButton1_Click()
 MsgBox Worksheets(1).Columns.Count
End Sub

The output is shown below:

Figure 19.3

Example 19.4

The count property in this example will return the number of rows in the worksheet.

Private Sub CommandButton1_Click()
 MsgBox Worksheets(1).Rows.Count
End Sub

Figure 19.4

19.2 The Worksheet Methods

Some of the worksheet methods are add, delete, select, SaveAs, copy, paste and more.

 

Example 19.5

In this example, when the user clicks the first command button, it will add a new sheet to the workbook. When the user clicks the second command button, it will delete the new worksheet that has been added earlier.

Private Sub CommandButton1_Click()
 Worksheets. Add
End Sub

Private Sub CommandButton2_Click()
 Worksheets(1).Delete
End Sub




Example 19.6

The select method associated with worksheet lets the user select a particular worksheet. In this example, worksheet 2 will be selected.

Private Sub CommandButton1_Click()
‘Worksheet 2 will be selected
 Worksheets(2).Select
End Sub

The select method can also be used together with the Worksheet’s properties Cells, Columns, and Rows as shown in the following examples.

Example 19.7

Private Sub CommandButton1_Click()
‘Cell A1 will be selected
 Worksheets (1).Cells (1).Select
End Sub

Example 19.8

Private Sub CommandButton1_Click()
‘Column 1 will be selected
 Worksheets (1).Columns (1).Select
End Sub

Example 19.9

Private Sub CommandButton1_Click()
‘Row 1 will be selected
 Worksheets (1).Rows (1).Select
End Sub

 

Excel VBA 2010 also allows us to write code for copy and paste. Let’s look at the following Example:

 Example 19.10

Private Sub CommandButton1_Click()
‘To copy the content of a cell 1
 Worksheets(1).Cells(1).Select
 Selection.Copy
End Sub

Private Sub CommandButton2_Click()
‘To paste the content of cell 1 to cell 2
 Worksheets(1).Cells(2).Select
 ActiveSheet.Paste
End Sub






 [Lesson 18]<<[Table of Contents]>>[Lesson 20]

 

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInEmail this to someone