Adding a link to the worksheet that is being created

[DeezNuts]

I have a script that creates and names a new sheet and also adds information to a second sheet. I would like to have link on the second sheet to the newly created work sheet here is what I currently have


This piece adds the Header name on the page. The txtTerm.Value is also the name of the new sheet.

.Range("A1").Value = txtTerm.Value

I ran a macro to see if I could see how to combine the two but that didnt help much here is the result of the macro

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'Fall 2014'!A1", TextToDisplay:="Fall 2014"


I tried to combine them with no luck

.Range("A1").Value = ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'txtTerm.Value'!A1", TextToDisplay:= txtTerm.Value

Just not sure what I can get rid of and what needs to be kept well actually I am lost on this one.

 

[JoeMo]

If the second sheet (the one where you want the link to the new sheet) is active then try:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
txtTerm.Value, TextToDisplay:="Fall 2014"

Note: you may want to change Selection to a specific cell [e.g. Range("A1")] where you want the link placed on the second sheet.

 

[DeezNuts]

Thank you but I dont think I explained this very well. I have a userform that creates and names a new sheet. The textbox value from the form is what the sheet is named. It also adds that value to a cell in a different sheet. In the next open row. The textbox is named txtTerm so this line .Range("A1").Value = txtTerm.Value adds the name of the cell based on the textbox value. I would like for this line to not just add the value of the box but add it as a link to the sheet which gets its name from the same textbox. I can just click the cell and link it manually but was looking for it to be automated when the sheet is made