Hyperlink Using a Variable

[richciccarella]

Hello everyone,

I have been following this forum for a while but this is my first post. I am stuck on my hyperlink code. I want the user to input data into a userform and then that data is saved in column AA2, 3, 4... on sheet userinputlist. Then it creates a sheet with its name. Then on sheet Summary, I want that name to be in A179, 180, 181... with a hyperlink to its page. When the user clicks done, it resets the form to enter a new data with the same procedure. Right now, everything works except the hyperlink is created on whatever cell its currently active on the summary sheet. and two or more inputs just overwrites the first one. This is the code I have. (I didn't include code to setup the page since its pretty long). Thank you in advance for your help.

 Sheets("UserInputList").Select
    Dim EmptyLine As Long
'
'Finds the next empty line
    EmptyLine = Worksheets("UserInputList").Cells(Rows.Count, 27).End(xlUp).Row + 1

'Adds the name value
    Cells(EmptyLine, 27).Value = TextBox2.Value
'
'
'Finds the next empty line in Summary sheet
    Sheets("Summary").Select
    Dim EmptyLine3 As Long
'
    EmptyLine3 = Worksheets("Summary").Cells(Rows.Count, 1).End(xlUp).Row + 178
'Adds the name value to the empty cell
    Cells(EmptyLine3, 1).Value = TextBox2.Value

 

[Trebor76]

Hi richciccarella,

Welcome to MrExcel!!

I think the issue is that the entries are appearing further down Col. A than you realize. For example if there's something in cell A182 the code will be put whatever is in TextBox2 into cell A360.

As such, see how this goes:

Option Explicit
Sub Macro1()

    Dim EmptyLine3 As Long

    With Sheets("Summary")
        'If there is nothing in Col. A of the 'Summary' sheet, then...
        If WorksheetFunction.CountA(.Columns("A")) = 0 Then
            '...output the contents of 'TextBox2' to cell A179
            .Cells(179, 1).Value = TextBox2.Value
        'Else, if there is an entry in Col. A of the 'Summary' sheet, then...
        Else
            '...find the last used row, increment it by one and output the contents of 'TextBox2' to it
            EmptyLine3 = .Cells(Rows.Count, 1).End(xlUp).Row + 1
            .Cells(EmptyLine3, 1).Value = TextBox2.Value
        End If
    End With

End Sub

Regards,

Robert

 

[richciccarella]

ok that might be the problem. Is there anyway I can force to go into a range like A179:A182? but if say cell G1 is selected on the summary page, the link will go there and same if cell F2 is selected.

 

[Trebor76]

Do you mean like this:

Option Explicit
Sub Macro1()

    Dim rngMyCell As Range
    Dim blnReturnedValue As Boolean

    With Sheets("Summary")
        'If there is an empty cell in the range A179:A182, then...
        For Each rngMyCell In .Range("A179:A182")
            If Len(rngMyCell) = 0 Then
                '...output the value of 'TextBox2' to it, set the 'blnReturnedValue' variable to True and quit the loop
                rngMyCell.Value = TextBox2.Value
                blnReturnedValue = True
                Exit For
            End If
        Next rngMyCell
        'If the 'blnReturnedValue' variable has not been set to True then...
        If blnReturnedValue = False Then
            '...select the 'Summary' tab and output the value of 'TextBox2' to the activecell
            .Select
            ActiveCell.Value = TextBox2.Value
        End If
    End With

End Sub

 

[richciccarella]

Thank you so much. it works that it list the inputs in the right cell but the hyperlink is still in whatever cell is active. in the test run i did it was cell G184.

    Dim rngMyCell As Range
    Dim blnReturnedValue As Boolean
    Sheets("Summary").Select
    With Sheets("Summary")
        'If there is an empty cell in the range A179:A182, then...
        For Each rngMyCell In .Range("A179:A182")
            If Len(rngMyCell) = 0 Then
                '...output the value of 'TextBox2' to it, set the 'blnReturnedValue' variable to True and quit the loop
                rngMyCell.Value = TextBox2.Value
                blnReturnedValue = True
                ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
                 "=" & TextBox2.Value & "!" & "A1", TextToDisplay:=TextBox2.Value
                Exit For
            End If
        Next rngMyCell
        'If the 'blnReturnedValue' variable has not been set to True then...
        If blnReturnedValue = False Then
            '...select the 'Summary' tab and output the value of 'TextBox2' to the activecell
            .Select
            ActiveCell.Value = TextBox2.Value
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
             "=" & TextBox2.Value & "!" & "A1", TextToDisplay:=TextBox2.Value
        End If
    End With

 

[Trebor76]

but the hyperlink is still in whatever cell is active

Sorry - you've lost me

Where should the hyperlink go? What does TextBox2 have in it?

 

[richciccarella]

Sorry, it is confusing. So the userform is asking the user to input their expenses for a particular expense. This particular one is for vacation expenses. Textbox2 is something like fuel or hotel. This is initially inputted into AA2 in UserInputList (code works). From there, it creates a sheet with that title (code works). Then i want that same item to be placed in a sheet called summary (Code Works) from where a hyperlink is set to the page that was created (doesnt work).

Does that make sense? There can be 1 expense inputted or multiple until they click finish.

 

[Trebor76]

Still not 100 percent sure, but what happens if you change "ActiveSheet" from this line...

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"=" & TextBox2.Value & "!" & "A1", TextToDisplay:=TextBox2.Value

...to rngMyCell?

 

[richciccarella]

No, that didnt change anything. It does everything except the hyperlink. Thank you though. You got half my issue fixed.

 

[richciccarella]

Thank you for all your help. I added rngMyCell.select to the code and it worked!

    Dim rngMyCell As Range
    Dim blnReturnedValue As Boolean
    Sheets("Summary").Select
    With Sheets("Summary")
        'If there is an empty cell in the range A179:A182, then...
        For Each rngMyCell In .Range("A179:A182")
            If Len(rngMyCell) = 0 Then
                rngMyCell.Select
                '...output the value of 'TextBox2' to it, set the 'blnReturnedValue' variable to True and quit the loop
                rngMyCell.Value = TextBox2.Value
                rngMyCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
                 "=" & rngMyCell.Value & "!" & "A1", TextToDisplay:=rngMyCell.Value
                blnReturnedValue = True
                Exit For
            End If
        Next rngMyCell
        'If the 'blnReturnedValue' variable has not been set to True then...
        If blnReturnedValue = False Then
            '...select the 'Summary' tab and output the value of 'TextBox2' to the activecell
            '.Select
            rngMyCell.Select
            ActiveCell.Value = TextBox2.Value
            rngMyCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
             "=" & rngMyCell.Value & "!" & "A1", TextToDisplay:=rngMyCell.Value
        End If
    End With
 TextBox2.Value = ""