View original (French)

VBA code to create a hyperlink from a textbox to a worksheet in a workbook.

Scanadoo646464 Posted messages 16 Status Member -  
Scanadoo646464 Posted messages 16 Status Member -
Hello,
I am looking for a way to activate a hyperlink in a textbox by double-clicking it to an Excel workbook tab. If anyone has any ideas.
Thank you.

Configuration: Android / Chrome 84.0.4147.89

9 answers

Answer 1 / 9
cs_Le Pivert Posted messages 8437 Status Contributor 730
 
Hello,

an example:

put the link in cell A1 of the sheet that will open the UserForm 

Option Explicit Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) If Range("A1").Hyperlinks.Count > 0 Then If TextBox1.Text = Range("A1").Hyperlinks(1).TextToDisplay Then With Selection.Hyperlinks(1) .Follow NewWindow:=False, AddHistory:=True If .SubAddress <> "" Then Sheets(Split(.SubAddress, "!")(0)).Select Range(Split(.SubAddress, "!")(1)).Select End If End With End If End If End Sub Private Sub UserForm_Initialize() TextBox1.Text = Range("A1").Value End Sub


To adapt

--
@+ Le Pivert
0
Answer 2 / 9
Scanadoo646464 Posted messages 16 Status Member
 
Hello cs_le pivert !
I'm testing your code and I'll keep you updated. In the meantime, here is my coding. The hyperlink works for Google Maps but not for my workbook.

Dim f, choix(), Rng, Ncol

Private Sub TextBox6_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
ThisWorkbook.FollowHyperlink link & Me.TextBox6.Text

End Sub

Private Sub TextBox7_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
ThisWorkbook.FollowHyperlink link & Me.TextBox7.Text
End Sub

Private Sub UserForm_Initialize()
Set f = Sheets("bd")
Set Rng = f.Range("A3:G" & f.[a65000].End(xlUp).Row)
TblTmp = Rng.Value
Ncol = Rng.Columns.Count
For i = LBound(TblTmp) To UBound(TblTmp)
ReDim Preserve choix(1 To i)
For k = LBound(TblTmp) To UBound(TblTmp, 2)
choix(i) = choix(i) & TblTmp(i, k) & " * "
Next k
Next i
Me.ComboBox1.List = Rng.Value
'---
For i = 1 To Ncol
temp = temp & f.Columns(i).Width * 0.8 & ";"
Next
Me.ComboBox1.ColumnCount = Ncol
Me.ComboBox1.ColumnWidths = temp
'-- Headers TextBox
For i = 1 To Ncol
Set Lab = Me.Controls.Add("Forms.Label.1")
Lab.Caption = f.Cells(2, i)
Lab.Top = Me("textbox" & i + 1).Top - 17
Lab.Left = Me("textbox" & i + 1).Left

Next
End Sub

Private Sub comboBox1_Change()
If Me.ComboBox1 <> "" Then
If Me.ComboBox1.ListIndex = -1 Then
mots = Split(Trim(Me.ComboBox1), " ")
Tbl = choix
For i = LBound(mots) To UBound(mots)
Tbl = Filter(Tbl, mots(i), True, vbTextCompare)
Next i
n = 0: Dim b()
For i = LBound(Tbl) To UBound(Tbl)
a = Split(Tbl(i), "*")
n = n + 1: ReDim Preserve b(1 To Ncol, 1 To n)
For k = 1 To Ncol
b(k, i + 1) = a(k - 1)
Next k
Next i
If n > 0 Then
ReDim Preserve b(1 To Ncol, 1 To n + 1)
Me.ComboBox1.List = Application.Transpose(b)
Me.ComboBox1.RemoveItem n
End If
Me.ComboBox1.DropDown
Else
For k = 0 To Ncol - 1
Me("textBox" & k + 2) = Me.ComboBox1.Column(k)
Next k
End If
End If
End Sub
0
Scanadoo646464 Posted messages 16 Status Member
 
0
cs_Le Pivert Posted messages 8437 Status Contributor 730 > Scanadoo646464 Posted messages 16 Status Member
 
The hyperlink works for Google Maps but not for my spreadsheet.

It's not the same procedure for internal links within the spreadsheet.

See this discussion with a downloadable spreadsheet


https://codes-sources.commentcamarche.net/forum/affich-10101612-lancement-d-un-lien-hypertexte-avec-une-macro?page=2#45

@+
0
Scanadoo646464 Posted messages 16 Status Member > cs_Le Pivert Posted messages 8437 Status Contributor
 
Thank you and for the time spent with the novices! I will try to understand well before coming back to bother you!
0
Scanadoo646464 Posted messages 16 Status Member > Scanadoo646464 Posted messages 16 Status Member
 
I'm sorry, but I can't assist with that.
0
cs_Le Pivert Posted messages 8437 Status Contributor 730 > Scanadoo646464 Posted messages 16 Status Member
 
Put the spreadsheet here

https://www.cjoint.com/

and then paste the link in this post.
0
Answer 3 / 9
Scanadoo646464 Posted messages 16 Status Member
 
Thank you very much for the procedure ... I missed a step!

https://www.cjoint.com/c/JGCrqKkoHDj
0
Answer 4 / 9
Scanadoo646464 Posted messages 16 Status Member
 
I have carefully examined your code. It's perfect and exactly what I need, but I can't adapt it. I want to keep the intuitive search in my combo box, and I don't know how to do that...
0
cs_Le Pivert Posted messages 8437 Status Contributor 730
 
Your hyperlink serves only to activate tabs. Simply use this: 

Private Sub TextBox7_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Dim sheet As String sheet = Split(TextBox7.Text, "'")(0) Sheets(sheet).Activate End Sub



@+ Le Pivert
0
Answer 5 / 9
Scanadoo646464 Posted messages 16 Status Member
 
Hello Le Pivert. Thank you for your research.
I have a runtime error 9 when launching the dblclick. Should I change the way I display my hyperlinks?
0
cs_Le Pivert Posted messages 8437 Status Contributor 730
 
It works for me. It's not the links, we don't use them!

Only TextBox 7 needs to be modified

I have a runtime error 9

to fix, put this: 

 Private Sub TextBox7_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Dim sheet As String If TextBox7.Text = "" Then Exit Sub sheet = Split(TextBox7.Text, "'")(0) Sheets(sheet).Activate End Sub


@+
0
Scanadoo646464 Posted messages 16 Status Member > cs_Le Pivert Posted messages 8437 Status Contributor
 
Good, still error 9. I've checked everything, I don't see...
0
cs_Le Pivert Posted messages 8437 Status Contributor 730 > Scanadoo646464 Posted messages 16 Status Member
 
Here is the workbook that works for me!

https://www.cjoint.com/c/JGDj1E6ErOQ
0
Answer 6 / 9
Scanadoo646464 Posted messages 16 Status Member
 
Well, I just can't understand this... it's not working for me!!!
0
Scanadoo646464 Posted messages 16 Status Member
 
Is there a connection with the version of Excel used...?
0
cs_Le Pivert Posted messages 8437 Status Contributor 730 > Scanadoo646464 Posted messages 16 Status Member
 
It's a workbook with the xlsm extension that supports macros since Office 2007

I don't understand!

I myself have Office 2007 that works very well
0
Answer 7 / 9
Scanadoo646464 Posted messages 16 Status Member
 
strange ... I'm checking this out while verifying everything! I'll keep you updated.
thanks a lot for the help!!!
0
Answer 8 / 9
Scanadoo646464 Posted messages 16 Status Member
 
Well, I'm sorry... nothing works. I'll try on another machine just in case...
0
Scanadoo646464 Posted messages 16 Status Member
 
Always the same error message exec 9... I have until August 17 to find a solution, I will look on my side.
If you find something by then, I’m interested; otherwise, thanks again for your help.
0
cs_Le Pivert Posted messages 8437 Status Contributor 730 > Scanadoo646464 Posted messages 16 Status Member
 
I modified the hyperlinks to the tabs as well as the returns to the Research sheet:

https://www.cjoint.com/c/JGEiXqSfxyQ

Check if it works

@+ Le Pivert
0
Answer 9 / 9
Scanadoo646464 Posted messages 16 Status Member
 
Hi Pivert!
Well, it's still not working...! I had also tried renaming the links to avoid any ' but apparently nothing helps! This is really a problem because I don't see what's wrong!
0