Assign a specific Column to Shapes that Link to Hyperlinks

RedBorg

New Member
Joined
Feb 22, 2016
Messages
23
I have this code that works however it works too well, as that it wants to assign a hyperlink to every shape on my page. I need to tell this code which column to reference the shapes before it anchors the hyperlinks to them. My shapes are located in Column T and my Hyperlinks are located 4 Columns to the left of Column T. Here's my original code and thanks for your assistance with this.

Code:
    Sub Macro3()
    '
        Dim shp As Shape
    '
        For Each shp In ActiveSheet.Shapes
            ActiveSheet.Hyperlinks.Add Anchor:=shp, _
            Address:=shp.TopLeftCell.Offset(, 4).Text
    '
        Next shp
    '
    End Sub
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

RedBorg

New Member
Joined
Feb 22, 2016
Messages
23
I meant to say that the hyperlinks are 4 columns to the right of Column T, not to the left.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Try...

Code:
[COLOR=darkblue]Sub[/COLOR] Macro3()
[COLOR=green]'[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] shp [COLOR=darkblue]As[/COLOR] Shape
[COLOR=green]'[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] shp [COLOR=darkblue]In[/COLOR] ActiveSheet.Shapes
        [COLOR=darkblue]If[/COLOR] shp.TopLeftCell.Column = 20 [COLOR=darkblue]Then[/COLOR] [COLOR=green]'Column T[/COLOR]
            ActiveSheet.Hyperlinks.Add Anchor:=shp, _
            Address:=shp.TopLeftCell.Offset(, 4).Text
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
[COLOR=green]'[/COLOR]
    [COLOR=darkblue]Next[/COLOR] shp
[COLOR=green]'[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!
 

RedBorg

New Member
Joined
Feb 22, 2016
Messages
23
:)
Absolutely works. You guys are fast and very knowledgeable.
Thanks,
Scott "RedBorg"
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,462
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top