Tom has attended:
Excel VBA Intro Intermediate course
Excel Advanced course
Excel VBA Advanced course
Access Introduction course
Hyperlinks
I want to make navigation around a spreadsheet easier, and have tried to to achieve that using hyperlinks. I have an index sheet to each sheet in the book, which works fine. What I then want is smaller lists of sheet names, (not all of them), with a hyperlink for each one to that sheet. I can do this one by one, but want to write code so as to navigate in each case to the sheet with the name in the anchor cell. How can I do this please?
RE: hyperlinks
Hi Tom
Further to your post you are on the right track with regards to coding a solution. We are confident a solution can be found however it will be beyond the capabilities of this forum.
The approach to take would be to review your working files along with a mock up you have prepared for our guidance. We can then confirm how we will go about creating the solution, time required and related costs. This shouldn't take more than a half day but we will have a better idea once we have reviewed your files.
If you would like to progress this option then please do let us know.
Kind regards
Jacob
RE: hyperlinks
Thanks, Jacob.
Could you advise please:
(i) what qualifies as within the scope of the forum in terms of getting free support, and what does not; and
(ii) the rates at which work outside that scope would be chargeable.
Thanks,
tom
RE: hyperlinks
Hi Tom
This is the sub-routine I use to create an index of worksheets:
Sub ContentsSheet()
'This Creates a List of Worksheets, together with a Hyperlink to each one
Sheets("Index").Select
Range("A1").Select
Dim DoIt
Dim MySheetName
'ScreenUpdating = False
Range("A1").Select
'DoIt = MsgBox("Have you clicked in the cell where you want your contents list to start?", vbYesNo, "Create a contents list")
'If DoIt = vbYes Then
For I = 1 To Worksheets.Count
ActiveCell.Value = Worksheets(I).Name
ActiveCell.Offset(0, 1).Select
MySheetName = "'" & Worksheets(I).Name & "'!A1"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=MySheetName
ActiveCell.FormulaR1C1 = Worksheets(I).Name
ActiveCell.Offset(1, -1).Select
Next I
'End If
Range("A1").Select
ScreenUpdating = True
End Sub
I use the following formula to enter the worksheet name into cell A1 on each sheet (but of course you could use this in any cel):
=RIGHT((CELL("filename",$A$1)),LEN(CELL("filename",$A$1))-FIND("]",(CELL("filename",$A$1))))
Please let me know if this is of any help to you.
I also use a couple of other sub-routines to sort the worksheets into alphabetical order and to place the 'Index' woksheet as 'Sheet 1' - the first sheet in the workbook.
Good Luck,
John
