Results 1 to 6 of 6

Macro to Populate Cells with Hyperlink to Itself?

This is a discussion on Macro to Populate Cells with Hyperlink to Itself? within the Excel Questions forums, part of the Question Forums category; Hi, I am a network administrator for a Jr. High in Colorado and am new to Excel Macro writing. I ...

  1. #1
    New Member
    Join Date
    Mar 2007
    Posts
    46

    Default Macro to Populate Cells with Hyperlink to Itself?

    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

  2. #2
    Board Regular
    Join Date
    Feb 2006
    Posts
    3,437

    Default

    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

  3. #3
    New Member
    Join Date
    Mar 2007
    Posts
    46

    Default

    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!

  4. #4
    Board Regular
    Join Date
    Feb 2006
    Posts
    3,437

    Default

    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

  5. #5
    New Member
    Join Date
    Mar 2007
    Posts
    46

    Default

    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/viewto...yperlink+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!

  6. #6
    Board Regular
    Join Date
    Feb 2006
    Posts
    3,437

    Default

    Hi matt
    very interesting indeed. I am glad I could be of help in your case.
    Ravi

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com