The_Steward
Board Regular
- Joined
- Nov 26, 2020
- Messages
- 63
- Office Version
- 365
- Platform
- Windows
The below macro ran successfully until I tried adding hyperlinks to it. I can't seem to define the Anchor correctly. any suggestions are appreciated.
I am trying to create a directory to 20 different sheets (I have the loop set to 3 while testing) so that users can easily access their client data.
I am trying to create a directory to 20 different sheets (I have the loop set to 3 while testing) so that users can easily access their client data.
VBA Code:
Sub Create_SilButton()
'Still need to change formatting + add hyperlink and Screentip
Dim macrobook As Workbook
Set macrobook = ThisWorkbook
Dim namesheet As Worksheet
Set namesheet = macrobook.Sheets("Code and Data Centre")
Dim nameslastrow As Long
nameslastrow = namesheet.Cells(Rows.Count, 1).End(xlUp).Row
Dim names As Long
Dim y As Long
Dim x As Long
Dim shapesloop As Long
Dim SilSelection As Worksheet
Set SilSelection = macrobook.Sheets("SIL House Selection")
SilSelection.Activate
x = 300
y = 180
names = 4
For shapesloop = 1 To 3
Dim shapes As Shape
Set shapes = SilSelection.shapes.AddShape(msoShapeRoundedRectangle, x, y, 370, 30)
shapes.Fill.ForeColor.RGB = RGB(191, 194, 211)
With shapes.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(70, 74, 100)
.Transparency = 0
End With
Application.CommandBars("Format Object").Visible = False
shapes.TextFrame2.TextRange.Font.Bold = msoTrue
shapes.TextFrame2.TextRange.ParagraphFormat.Alignment = _
msoAlignCenter
shapes.TextFrame2.VerticalAnchor = msoAnchorMiddle
With shapes.TextFrame2.TextRange.Font
.NameComplexScript = "Helvetica"
.NameFarEast = "Helvetica"
.Name = "Helvetica"
End With
shapes.Name = "S1"
Set HLShape = SilSelection.shapes("S1")
SilSelection.Hyperlinks.Add Anchor:=HLShape, Address:="", SubAddress:=namesheet.Cells(names, 8), ScreenTip:="Please Click"
shapes.TextFrame2.TextRange.Font.Size = 18
shapes.TextFrame.Characters.Text = namesheet.Cells(names, 10)
y = y + 60
names = names + 1
x = 300
Next shapesloop
End Sub