none
Writing information as a hyperlink to last available cell using a funciton within VBA RRS feed

  • Question

  • Details of what I am doing:

    I am writing a UserForm within Excel 2010 and have alot of VBA behind it. Based on the users input (yes/no) to each question I am creating deliverables in another worksheet (titled "Next_Steps"). I am also writing the answers to the questions in a third worksheet for reference (titled "Standards"). These Next_Steps are written and refer to (38) hyperlinks in a  references section so that the user can easily access the information. Each Next_Steps has a different URL to reference.

    What I want to accomplish:

    I would like to get rid of the references section and make each Next_Step refer to a specific URL.

    Example of code currently in use:

    If Worksheets("Standards").Cells(2, 3) = "Yes" Then
        Worksheets("Next_Steps").Select
        Range("A" & Rows.Count).End(x1Up).Offset(1) = "Enter and/or Update application entry"
        Worksheets("Standards").Select 
    ElseIf Worksheets("Standards").Cells(2, 3) = "No" Then
        Worksheets("Standards").Cells(3, 3) = N/A
    End If

    What this does:

    This works as I would like it to if I use the references section (which I am hoping to get rid of) by seeing the answer to my first question and writing it in the first available empty space on the Next_Steps worksheet. Here is what I have tried to make the "Enter and/or updated application entry" portion into a hyperlink:

    If Worksheets("Standards").Cells(2, 3) = "Yes" Then
        Worksheets("Next_Steps").Select
        Range("A" & Rows.Count).End(x1Up).Offset(1) = "Enter and/or Update application entry"
        ActiveCell.Hyperlinks.Add ActiveCell, "http://ReferenceUrl.com"
        Worksheets("Standards").Select 
    ElseIf Worksheets("Standards").Cells(2, 3) = "No" Then
        Worksheets("Standards").Cells(3, 3) = N/A
    End If
    The problem with the above code is it ONLY makes the first cell (A2) a hyperlink and continuously overwrites it instead of writing on the next available cell. There could potentially be up to 38 hyperlinks in the Next_Steps section so I would like to make it so that the user only has to click the Nex t_Step and not cross reference that with the references.





    Friday, June 28, 2013 7:07 PM

Answers

  • You are writing to the ActiveCell on your Next_Steps sheet which will be the last selected cell (by user or programatically). In code it's very rare you ever need to Select or Active a cell, instead always write to the cell as required. Adapt the following to your needs -

    Sub test()
    Dim rng As Range
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
        Set ws1 = Worksheets("Standards")
        Set ws2 = Worksheets("Next_Steps")
        If ws1.Cells(2, 3) = "Yes" Then
            Set rng = ws2.Range("A" & Rows.Count).End(xlUp)
            rng.Offset(1, 0) = "Enter and/or Update application entry"
            ws2.Hyperlinks.Add rng.Offset(1, 1), "http://ReferenceUrl.com"
        ElseIf ws1.Cells(2, 3) = "No" Then
            ws1.Cells(3, 3) = "N / a"
        End If
    End Sub

    Peter Thornton

    Saturday, June 29, 2013 11:10 AM
    Moderator