Hi there,
The following code will allow you to specify the cell in which the button should appear:
Option Explicit
Sub Contents_Hyperlinks()
' PURPOSE: Add hyperlinked buttons back to Table of Contents worksheet tab
Const sBUTTON_CELL As String = "B2"
Const iMARGIN As Integer = 2
Const iHEIGHT As Integer = 20
Const iWIDTH As Integer = 60
Dim ContentName As String
Dim rButtonCell As Range
Dim myShape As Shape
Dim iLeft As Integer
Dim iTop As Integer
Dim sht As Worksheet
' Inputs - Table of Contents Worksheet Name
ContentName = "Contents"
' Loop Through Each Worksheet in Workbook
For Each sht In ActiveWorkbook.Worksheets
If sht.Name <> ContentName Then
Set rButtonCell = sht.Range(sBUTTON_CELL)
iLeft = rButtonCell.Left + iMARGIN
iTop = rButtonCell.Top + iMARGIN
' Create & Position Shape
Set myShape = sht.Shapes.AddShape(msoShapeRoundedRectangle, iLeft, iTop, _
iWIDTH, iHEIGHT)
' Format Shape
With myShape
.Fill.ForeColor.RGB = RGB(91, 155, 213) 'Blue
.Line.Visible = msoFalse
With .TextFrame2.TextRange
.Font.Size = 10
.Text = ContentName
.Font.Bold = True
.Font.Fill.ForeColor.RGB = RGB(255, 255, 255) 'White
End With
End With
' Assign Hyperlink to Shape
sht.Hyperlinks.Add myShape, "", SubAddress:="'" & ContentName & "'!A1"
End If
Next sht
End Sub
Hope this helps - please let me know how you get on.
Regards,
Greg M
Bookmarks