Hyperlink Using a Variable

richciccarella

New Member
Joined
Oct 18, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
 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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,714
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:

VBA Code:
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

New Member
Joined
Oct 18, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,714
Do you mean like this:

VBA Code:
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
 
Solution

richciccarella

New Member
Joined
Oct 18, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

VBA Code:
    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

Well-known Member
Joined
Jul 23, 2007
Messages
4,714
but the hyperlink is still in whatever cell is active

Sorry - you've lost me :confused:

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

richciccarella

New Member
Joined
Oct 18, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 23, 2007
Messages
4,714
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

New Member
Joined
Oct 18, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
No, that didnt change anything. It does everything except the hyperlink. Thank you though. You got half my issue fixed.
 

richciccarella

New Member
Joined
Oct 18, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Thank you for all your help. I added rngMyCell.select to the code and it worked!

VBA Code:
    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 = ""
 

Watch MrExcel Video

Forum statistics

Threads
1,122,632
Messages
5,597,287
Members
414,134
Latest member
Tiyas44

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