hello
i have a macro that give a list of all sheett name in a workbook
how can i add a clickable link to the code so every time i click on
sheet name it jump to it
moshe
my code
Sub sheetret()
Application.ScreenUpdating = False
Dim shtsNumber, sh As Integer
shtsNumber = Sheets.Count
ActiveCell.Select
ActiveCell.FormulaR1C1 = "sheet list"
ActiveCell.Font.ColorIndex = 3
ActiveCell.Offset(1, 0).Activate
For sh = 1 To shtsNumber
ActiveCell.Offset(sh - 1, 0).Value = Sheets(sh).Name
Next sh
With selection.Font
.Italic = True
.Bold = True
End With
boldera
SortWorksheets
fifts
Application.ScreenUpdating = True
End Su

--
Message posted from http://www.ExcelForum.com

Check out this previous thread in Google for an alternative approach to
exactly the same problem as you describe http://tinyurl.com/ypmy3

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"lior03 " wrote in message
...
hello
i have a macro that give a list of all sheett name in a workbook
how can i add a clickable link to the code so every time i click on a
sheet name it jump to it
moshe
my code
Sub sheetret()
Application.ScreenUpdating = False
Dim shtsNumber, sh As Integer
shtsNumber = Sheets.Count
ActiveCell.Select
ActiveCell.FormulaR1C1 = "sheet list"
ActiveCell.Font.ColorIndex = 3
ActiveCell.Offset(1, 0).Activate
For sh = 1 To shtsNumber
ActiveCell.Offset(sh - 1, 0).Value = Sheets(sh).Name
Next sh
With selection.Font
Italic = True
Bold = True
End With
boldera
SortWorksheets
fifts
Application.ScreenUpdating = True
End Sub


---
Message posted from http://www.ExcelForum.com/

Hi,

Just another method if you want to show inbuilt sheet activate dialog
box from your macro.


Sub MoreSheets()
Dim myctl As CommandBarButton

'find the control id 957 that shows inbuilt sheet activate dialogbox
Set myctl = CommandBars.FindControl(Type:=msoControlButton, ID:=957)

If myctl.Parent.Controls.Count = 16 Then
'if it is not added to toolbar then add it so we may use it
afterwards.
'if it is added then parent.control count < 16 but may be = 23 or ?

'checked it in xl-2000
'for i= 1 to myctl.Parent.Controls.Count
'debug.print myctl.Parent.controls(i).caption
'next


Set myctl = CommandBars("Standard").Controls.Add _
(Type:=msoControlButton, befo=1, ID:=957, Temporary:=True)
End If

myctl.Style = msoButtonCaption
myctl.Execute

End Sub


To popup sheet names.

Sub SheetNamePopup()
Application.CommandBars("workbook tabs").ShowPopup
End Sub



Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Look in Excel (not VBA) help at the hyperlink worksheetfunction. You could
have your macro add this in the next cell, keying off the sheetname.

--
Regards,
Tom Ogilvy

"lior03 " wrote in message
...
hello
i have a macro that give a list of all sheett name in a workbook
how can i add a clickable link to the code so every time i click on a
sheet name it jump to it
moshe
my code
Sub sheetret()
Application.ScreenUpdating = False
Dim shtsNumber, sh As Integer
shtsNumber = Sheets.Count
ActiveCell.Select
ActiveCell.FormulaR1C1 = "sheet list"
ActiveCell.Font.ColorIndex = 3
ActiveCell.Offset(1, 0).Activate
For sh = 1 To shtsNumber
ActiveCell.Offset(sh - 1, 0).Value = Sheets(sh).Name
Next sh
With selection.Font
Italic = True
Bold = True
End With
boldera
SortWorksheets
fifts
Application.ScreenUpdating = True
End Sub


---
Message posted from http://www.ExcelForum.com/