Macro to Populate Cells with Hyperlink to Itself?

MattDW

Board Regular
Joined
Mar 7, 2007
Messages
66
Hi,

I am a network administrator for a Jr. High in Colorado and am new to Excel Macro writing. I have become stumped with a little project I am working on, a spreadsheet with links to various computers and resources and scripts I use in my day to day work. I discovered the mrexel.com message board at the end of last week and have been reading and learning more about Excel from posts on your board (and the blogs off the root domain) since then. I haven't been able to find what I needed through searches of past posts on this board, or through countless Google searches, so I thought maybe I could elect a kind soul's assistance with my question.

In this spreadsheet I have several macros I would like to execute via a hyperlink. I found a post in this message board that indicated this was possible by creating a hyperlink that points back to the same cell in the worksheet and then by adding code to the Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) in the VB Editor. I have done this, and the macros I am using I have listed below. When I manually create the links using Excel's Insert > Hyperlink... menu, everything works exactly as I want it to. (I'd tried using a SelectionChange, at one point in my experimentation, but this broke the several of the macros).

What I need to do next I am not sure how to do. I have seven columns (F, G, H, I, J, K and L) that I need to fill (down) with hyperlinks that point back to its own cell. Through Excel I would do this through the Insert > Hyperlink... > Place in This Document. However, the number of cells I need to add hyperlinks for are to numerous to do this by hand. My first thought was fill in one row and then use Excel's Fill Down functionality. However, what I found was that the address's Cell Reference is duplicated instead of incremented when using any of the fill tricks I know (Fill menu, by using drag bar, by right clicking and dragging the drag bar, or holding down the Ctrl button). So instead of having links pointing to themselves, they instead all point to the first cell that was replicated (in my case that would be Row 11 of each of the columns mentioned).

Using the Record macro feature in Excel I was able to capture the creating of each one of these links. However, I'm not sure how to make this macro loop, nor how to increment the cell reference so that the hyperlink reference changes so that it pointing back to itself.

The columns/rows I am looking to fill are:

Column F: Rows 12-231 (Cell Reference Points to: itself, TextToDisplay: à)
Column G: Rows 12-231 (Cell Reference Points to: itself, TextToDisplay: Manage)
Column H: Rows 12-231 (Cell Reference Points to: itself, TextToDisplay: Connect Via Remote Desktop)
Column I: Rows 12-231 (Cell Reference Points to: itself, TextToDisplay: Open DameWare)
Column J: Rows 12-231 (Cell Reference Points to: itself, TextToDisplay: Log Off Computer)
Column K: Rows 12-231 (Cell Reference Points to: itself, TextToDisplay: See Who's Logged On)
Column L: Rows 12-231 (Cell Reference Points to: itself, TextToDisplay: ß)

(à and ß, when changed to WingDings, are arrows pointing to the right and left)

G11 will point to itself with the text "Manage", G12 will point to itself with the text "Manage" and so on and so forth through row 231. The next column over, H12 will point to itself with the text "Connect Via Remote Desktop", H13 will point to itself with the text "Connect Via Remote Desktop" and so on and so forth through row 231. And so on and so forth.

I hope this makes sense. Please let me know if I need to clarify anything.

Thanks Much for your help!!!


::: Macro Using Excel's Record Macro Feature :::

Sub AddRow()
'
' AddRow Macro
' Adds one row of links
'
' Keyboard Shortcut: Ctrl+Shift+M
'
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"PCs!F13", TextToDisplay:="à"
Columns("F:F").Select
With Selection.Font
.Name = "Wingdings"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
Range("G13").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"PCs!G13", TextToDisplay:="Manage"
Range("H13").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"PCs!H13", TextToDisplay:="Connect Via Remote Desktop"
Range("I13").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"PCs!I13", TextToDisplay:="Open DameWare"
Range("J13").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"PCs!J13", TextToDisplay:="Log Off Computer"
Range("K13").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"PCs!K13", TextToDisplay:="See Who's Logged On"
Range("L13").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"PCs!L13", TextToDisplay:="ß"
With Selection.Font
.Name = "Wingdings"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
End Sub


::: Other Macros I am Using :::

Private Sub CommandButton1_Click()
If ActiveCell.Row > 10 Then
Static OldRange As Range
Static OldRange2 As Range
On Error Resume Next
Set OldRange2 = Target
Cells(Application.ActiveCell.Row, 5).Select
Cells(Application.ActiveCell.Row, 5).Interior.ColorIndex = 6 ' yellow - change as needed
Cells(Application.ActiveCell.Row, 13).Interior.ColorIndex = 6 ' yellow - change as needed
Selection.Copy
End If
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If ActiveCell.Row > 10 Then
Static OldRange As Range
Static OldRange2 As Range
On Error Resume Next
Set OldRange2 = Target
Cells(Application.ActiveCell.Row, 5).Select
Cells(Application.ActiveCell.Row, 5).Interior.ColorIndex = 6 ' yellow - change as needed
Cells(Application.ActiveCell.Row, 13).Interior.ColorIndex = 6 ' yellow - change as needed
Selection.Copy
End If
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
On Error Resume Next

If ActiveCell.Column = 6 Then
Static OldRange4 As Range
On Error Resume Next
ActiveWindow.ScrollColumn = 6
Set OldRange4 = Target
Cells(Application.ActiveCell.Row, 5).Interior.ColorIndex = 6 ' yellow - change as needed
Cells(Application.ActiveCell.Row, 13).Interior.ColorIndex = 6 ' yellow - change as needed
End If

If ActiveCell.Column = 7 Then
ActiveWorkbook.FollowHyperlink Address:= _
"T:\Tech\Tech Private\BTC Frequently Accessed Files\compmgmt.msc"
GoTo SelectName
End If

If ActiveCell.Column = 8 Then
ActiveWorkbook.FollowHyperlink Address:= _
"T:\Tech\Tech Private\BTC Frequently Accessed Files\Launch Remote Desktop\Launch Remote Desktop.bat.lnk"
GoTo SelectName
End If

If ActiveCell.Column = 9 Then
ActiveWorkbook.FollowHyperlink Address:= _
"T:\Tech\Tech Private\BTC Frequently Accessed Files\Launch DameWare\Launch DameWare.bat.lnk"
GoTo SelectName
End If

If ActiveCell.Column = 10 Then
ActiveWorkbook.FollowHyperlink Address:= _
"T:\Tech\Tech Private\BTC Frequently Accessed Files\Log Off Remote Computer\Log Off Remote Computer.bat.lnk"
GoTo SelectName
End If

If ActiveCell.Column = 11 Then
ActiveWorkbook.FollowHyperlink Address:= _
"T:\Tech\Tech Private\BTC Frequently Accessed Files\See Who is Logged On to Remote Computer\See Who is Logged On to Remote Computer.bat.lnk"
GoTo SelectName
End If

If ActiveCell.Column = 12 Then
Static OldRange5 As Range
On Error Resume Next
ActiveWindow.ScrollColumn = 1
Set OldRange5 = Target
Cells(Application.ActiveCell.Row, 5).Interior.ColorIndex = 6 ' yellow - change as needed
Cells(Application.ActiveCell.Row, 13).Interior.ColorIndex = 6 ' yellow - change as needed
End If

Exit Sub

SelectName:
If ActiveCell.Row > 10 Then
Static OldRange As Range
Static OldRange2 As Range
On Error Resume Next
Set OldRange2 = Target
Cells(Application.ActiveCell.Row, 5).Select
Cells(Application.ActiveCell.Row, 5).Interior.ColorIndex = 6 ' yellow - change as needed
Cells(Application.ActiveCell.Row, 13).Interior.ColorIndex = 6 ' yellow - change as needed
Selection.Copy
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Row > 10 Then
Static OldRange As Range
Static OldRange2 As Range
On Error Resume Next
Cells(Application.ActiveCell.Row, 5).Interior.ColorIndex = 6 ' yellow - change as needed
Cells(Application.ActiveCell.Row, 13).Interior.ColorIndex = 6 ' yellow - change as needed
OldRange.Interior.ColorIndex = xlColorIndexNone
OldRange2.Interior.ColorIndex = xlColorIndexNone
Set OldRange = Cells(Application.ActiveCell.Row, 5)
Set OldRange2 = Cells(Application.ActiveCell.Row, 13)
End If
End Sub
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi

List the texts to display in col A ( A1: A7)

paste the following codes in the macro window ( Alt F8)
For a = 12 to 231
for b = 1 to 7
cells(a,b+5) = cells(b,1)
next b
next a
run the macro
 
Upvote 0
Ravishankar,

Thanks so much for your reply. That populated the cells with text exactly as I needed it to.

If I wanted to modify the above so that I am populating these cells with a hyperlink/bookmark instead of just text, how might I do that?

The hyperlink, in each cell, will link to that same cell and the display text will be the same as before (cells A1:A7).

Thanks again, Ravishankar!
 
Upvote 0
Hi
use the following macro to hyperlink cells to itselsf. But I did not follow why you need to do that.

Sub rrr()
For a = 12 To 21
For b = 1 To 7
Cells(a, b + 5) = Cells(b, 1)
c = Cells(a, b + 5).Address
d = Cells(b, 1)
Range(c).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Sheet1!" & c & "", TextToDisplay:=d
Next b
Next a
End Sub

Ravi
 
Upvote 0
Ravi,

Thanks so much! That's perfect and exactly what I was looking for -- thank you!

So, as to why this is useful... I hope this isn't to long winded or self-indulgent. If so, I'll apologize in advance. I certainly don't want it to be.

It started when I first started investigating how a person (or if a person) might be able to activate a macro using a hyperlink instead of through an object (such as a button). After a lot of Google searches, but no real answers, I came across the mrexcel message board through a Google search. Specifically, I found this thread here: http://www.mrexcel.com/board2/viewtopic.php?t=4201&highlight=macro+hyperlink+start Basically what this poster said was that you can execute macros using a hyperlink if you create a hyperlink that points back to the same (or another) cell. I'm not sure why but hyperlinks to outside websites and files don't (or at least don't always) execute a macro using the Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink). The HYPERLINK= and CONCATENATE functions also don't seem to execute macros when the hyperlink has been followed. If anybody knows why that is, I'd be very curious to know.

I experimented with this technique and found that it worked perfectly for what I needed it to do -- to be able to click on a hyperlink and have it both execute the file (batch script) or web page and execute a macro -- that selects and copy the computer name from column E.

The reason for this is because many of my .bat scripts and installed software work remotely on the network and require the computer name to be inputted in order to work (these script and utilities allow me to see if a user is logged onto that computer and whether or not the computer is locked or unlocked, to log a user off the computer, to connect to that computer via terminal software, to manage the local accounts and groups on that remote computer, to install printers and manage shares on a remote computer and to browse that computer's local (hidden) C drive share).

In the beginning these scripts (and applications) simply prompted me to copy and paste the computer name into them. But always trying to increase my productivity (and time spent managing and fixing computer problems) I have since automated the process whereas the scripts simply paste in whatever is currently in the clipboard.

So, then the next step was how I might possibly be able to automate the process of copying the computer name from column E in Excel to the clipboard and this lead me to a macro example, that I was able to modify to work for my purposes, that allowed me to copy and paste the cell in column E of whatever row was currently selected (had focus) at the time. I first added this to a button that sits along top of a frozen pane and I later modified this so that I could double click on any other cell in that row to execute the selecting and copying. This allowed me to search by computer description (column M) and then double click on that description to execute the script that copies column E. I then could click on one of the links in my Excel spreadsheet to execute the appropriate script, paste in the computer name that is in the clipboard, in order to execute the script.

The next logical step then was, could I execute this macro via a hyperlink instead of via double clicking on a cell or clicking on a button. This meant less clicks for me and also meant that my script wasn't executing the accidental pasting of something that wasn't a computer name into itself by accident.

The reason I wanted to use a link and not a button to execute these macros is that the script I modified worked by detecting the cell with focus and then selecting and copying the contents of column E of the same row. This meant one button wouldn't necessarily be useful in my case. Plus, I already had duplicate links (filled down) all the way down each of my columns, for convenience sake, so it made sense to me to make each of those links copy the computer name in the adjacent cell before executing the macro or launching the utility.

I've also read some posts that state the Worksheet_SelectionChange(ByVal Target As Range) could be setup to do the same thing, but ultimately was unable to get this to work correctly without breaking another (cell highlighting) macro that I have setup.

This, I think, brings me back to my discovery of the mrexcel message board and the tip for executing a macro via a hyperlink. My final problem was how to automate the populating of cells with hyperlinks that pointed back to themselves, and your post solved that for me completely.

Thanks again, Ravi, for your help!
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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
Back
Top