Forum home » Delegate support and help forum » Microsoft VBA Training and help » vba courses london - Create a contents page
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
vba courses london - Create a contents page
Resolved · Low Priority · Version Standard
Reply Thu 18 Jan 2007, 16:07Delegate David said...
David has attended:
Excel VBA Intro Intermediate course
Create a contents page
Hi there,
I'd like to have a macro which sits in my personal macro workbook that allows me to create a contents page for any excel workbook I access.
The contents page should include:
- a list of all the individual worksheets
- each worksheet name should also be a hyperlink to cell a1 of the corresponding sheet
It would also be handy to have the ability to
-name the contents page if a worksheet titled 'contents' already exists; and
- subsequently update the contents of the 'contents' worksheet I create.
Thanks!
For upcoming training course dates see: Pricing & availability
Create a contents page Using A Loop
A simple way of doing this is on startup run a procedure that does the following:
1. Creates a new Spreadsheet Call it "Contents Page". You need to check if "Contents Page" already exists and delete it
2. Does a loop that checks each sheet in the book
3. Inside this loop create another loop that using a row counter pastes the name of the sheet in a cell on the "Contents Page" and Hyperlinks it to the named sheet as seen in the routine below:
Sub ContentSheet
Dim vSheet As Worksheet
Dim RowNum As Integer
RowNum = 2
Sheets.Add Before:=Sheets(1)
ActiveSheet.Name = "Contents Page"
For Each vSheet In ActiveWorkbook.Worksheets
'You could test for "Contents Page" and ignore it here
Sheets("Contents Page").Select
Cells(RowNum, 1).Select
ActiveCell.Value = vSheet.Name
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
vSheet.Name & "!A1", TextToDisplay:=vSheet.Name
RowNum = RowNum + 1
Next vSheet
End Sub
|