Excel Worksheets

by Linda Quinn

Close Window        Print Page


More Worksheet Properties and Methods

Activate

Calculate

Protect / Unprotect

ResetAllPageBreaks

Index

Name

UsedRange

Tab


Activate

Activate makes a sheet the active sheet. It is the equivalent of clicking on the sheet and displaying it.

If you record a macro in Excel, you will see Activate used extensively. This is because recorded macros don't have variables to identify worksheets.


If you are writing VBA code you should identify each worksheet with a variable so you can refere to them by name. Activate can only references one worksheet at a time. Activate is rarely used by VBA programmers because of its limitations.


One time you might use Activate in VBA is at the end of a program when you want a certain sheet to be the one displayed. You can use: wst.Activate to have that sheet appear on the top.


Calculate

Calculate all formulas in a worksheet, or in all worksheets. Usually Excel is set to automatically calculate formulas, but if not, calculation can be forced by VBA.


To calculate all open workbooks:


To calculate a specific worksheet:


Calculate can also be used on a specific Range.


Protect / Unprotect

Protect a worksheet with a password.


To unprotect a worksheet:


If you want to protect or unprotect a large number of worksheets in one step. Put this is a loop:


ResetAllPageBreaks

This removes all manual page breaks. This is helpful if a worksheet had page breaks set manually and you want to remove them and start with a clean print mode.


Index

This returns the index number assigned to a worksheet. As mentioned before, we can't always be sure what index number was assigned to a worksheet. As sheets are removed and added, Excel continues numbering them, so you can have worksheet index 5 when there are only three worksheets.

x will contain the number of the worksheet index.


Name

This will assign a name to worksheet, or return the existing name.

When a worksheet is first created, it is given a name like "Sheet3". To change this to a meaningful name you use the Name property:

You can also use Name to get the name of a sheet:

wname will contain the worksheet name, like "Revenue Summary", or "Sheet4".


UsedRange

Returns a Range of the used cells on the specified worksheet.

Selecting the UsedRange (rng) will highlight the range. This property is useful in VBA if you want to add data to an existing worksheet and you need to know where the first open cells are.


Tab

Worksheet tabs are at the bottom of each sheet and display the name of the worksheet.

With the Tab property you can set the color of the tab:

The worksheet tab will be red.





Copyright © 2006-2016, LQSystems,Inc. All rights reserved.





























Copyright © 2006-2016, LQSystems,Inc. All rights reserved.