Working With Worksheets

fix ra

Keep in mind that you cannot hide every worksheet in a workbook. If you attempt to do so, Excel displays a Run-time error indicating that it is unable to set the Visible property of the Worksheet class. This message essentially means that you cannot hide all worksheets. To avoid receiving that message, make sure you leave one worksheet visible. To ensure that another user does not hide the worksheet that you want to keep visible, consider using the Visible property. This property checks that a sheet is visible before changing the visiblity of the other worksheets.

Sheets that you hide are still accessible to the user from within Excel. You can see which sheets are hidden in a workbook by selecting Format O Sheet O Unhide. The Unhide dialog box lists all of the sheets that are currently hidden. To unhide a sheet, you need to click the appropriate sheet and then click OK. This process is equivalent to setting the Visible property for a sheet to True.

L-H Type Sheets(N).Visible False.

□ Switch to Excel and run the macro.

L-H Type Sheets(N).Visible False.

□ Switch to Excel and run the macro.

How Use Two Windows Once
-■ Excel hides all but the first sheet in the workbook.

CHANGE the name of a sheet

You can change the name of a sheet within a workbook using the Name property of the Sheets object. By default Excel names all worksheets Sheet# replacing # with the order in which you added the sheet to the workbook. For example, a typical workbook contains three worksheets, Sheet1, Sheet2, and Sheet3. If you add an additional worksheet, by default Excel names the sheet Sheet4. Excel uses the name Chart# for chart sheets. Again, Excel assigns chart sheets numbers based upon the order in which you add them with the first chart sheet being Chart1. The other two types of sheets, macro sheets and dialog sheets, also have the same naming conventions. Excel names the first macro sheet you add to a workbook as Macro1 and the first dialog sheet as Dialog1.

You can change the name of a sheet in a workbook by assigning a new string value to the Name property of the corresponding Sheet object. For example, the following code illustrates how to change the name of the sheet to Budget2000: Sheets(1).Name = "Budget2000". Remember when assigning a string value, you must enclose the string in parentheses. You can also assign a string as the value of a variable.

The other function of the Name property is to provide the name of a specific sheet. For example, you can check that a sheet has the appropriate name. To determine the name of a sheet, you assign the string that the Name property returns to a variable, for example: StringName = Sheets(3) .Name.

After assigning the value to a variable, you perform any of the typical string functions. For example, you can compare it to another string, or just display it using the MsgBox function. See Chapter 7 for more information on working with the MsgBox function.

CHANGE THE NAME OF A SHEET

CHANGE THE NAME OF A SHEET

Excell Hide All But The Working Area

—n Create a new subroutine.

H Type Dim SheetName As String, replacing SheetName with the variable to contain the new sheet name.

-0 Type SheetName = InputBox("Input Box Text"), replacing Input Box Text with the text to display on the Input Box.

□ Type ActiveSheet.Name : SheetName.

—n Create a new subroutine.

H Type Dim SheetName As String, replacing SheetName with the variable to contain the new sheet name.

-0 Type SheetName = InputBox("Input Box Text"), replacing Input Box Text with the text to display on the Input Box.

□ Type ActiveSheet.Name : SheetName.

+1 0

Post a comment