Excel Tool

Order Online
Our company contact information There you can find frequently asked questions and answers

Home | Products |Purchase | FAQ | Contact Us | Useful Resources

How to Create a List (Directory) of Worksheet with VBA

Create a List (Directory) of Worksheet with VBA

Create a worksheet list, there are several ways, using the VBA methods are described below.


1. Click the cell and activate the worksheet with the same name.

Right click on "Directory" worksheet tab, select the "View Code" and copy and paste the following code at the cursor:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Sheets(Target.Value).Activate 'Activate the worksheet the same name

End Sub

Return to the worksheet, click the cell, you can activate the worksheet with the same name, and same as clicking the worksheet tab.
In actual use, the need to add some code, and determine whether the cell is empty, cell is within a specified range, and so on, see Example files.

Code is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'If you choose an Excel range, do not run the following code, exit the program
If Target.Count > 1 Then Exit Sub

'If the cell is not selected within the C4:I8, exit the program
If Intersect(Target, Range("c4:i8")) Is Nothing Then Exit Sub

If Target = "" Then Exit Sub 'If the cell is empty, exit the program

Sheets(Target.Value).Activate 'Activate the worksheet the same name

End Sub

Example File: activate the worksheet the same name.xlsm


2. How to Hide Extra Worksheets

To hide the extra sheet, two work needs to be done:
First: Open the project manager by Ctrl +F11, right click the module name "ThisWorkbook", select the "Ciew Code", as shown below:

Create a List (Directory) of Worksheet with VBA


Paste the following code at the cursor:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name <> "Directory" Then Sh.Visible = 2 'If the sheet name is not a "directory", then hide it
End Sub


If your worksheet name is not "Directory", should be based on actual changes in the code.


The second step:

Double click the module name "Sheet1 (Directory)" in the Microsoft Excel Object.

Create a List (Directory) of Worksheet with VBA

Paste the following code at the cursor:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'If you choose an Excel range, do not run the following code, exit the program
If Target.Count > 1 Then Exit Sub

'If the cell is not selected within the C4:I8, exit the program
If Intersect(Target, Range("c4:i8")) Is Nothing Then Exit Sub

If Target = "" Then Exit Sub 'If the cell is empty, exit the program
Sheets(Target.Value).Visible = 1 'Unhide, display the worksheet
Sheets(Target.Value).Activate 'Activate the worksheet the same name

End Sub

Example File: hide extra worksheets.xlsm


3. Hidden Sheet1 (Directory)

In the worksheet properly place, set up a "Returns" cell, click the cell, activates directory worksheet, copy and paste the following code into the module "ThisWorkbook" in the Microsoft Excel Object:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Sh.Visible = 2 'Hide inactive worksheets
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'If you click on the contents of the cell are "Returns"
If Target.Count = 1 And Target.Range("a1") = "Returns" Then
Target.Offset(1).Activate
Sheets("Directory").Visible = 1 'Unhide worksheet "Directory"
Sheets("Directory").Activate 'Activate worksheet "Directory"
End If
End Sub

Example File: hidden directory.xlsm

Copyright © 2001-2014 Excel-Tool All Rights Reserved.

Copyright Excel-Tool All rights reserved