make a data entry into hyperlink using vba?

[Anton]

I use vba for a programme where people can enter data into a sheet by using
textboxes. My problem is that I need to make the entry into textbox9 to
appear as hyperlink. and I want the entry into textbox9 to be a hyperlink
that links to ENTRY.dwg. so let say the person enters ANTON in textbox9 the
entry will become hyperlink to the file ANTON.dwg. Hope this helps to give a
clearer picture. my programme is as follow


Private Sub CommandButton1_Click()
Dim LastRow As Object

Set LastRow = Sheet3.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
LastRow.Offset(1, 3).Value = TextBox4.Text
LastRow.Offset(1, 4).Value = TextBox5.Text
LastRow.Offset(1, 5).Value = TextBox6.Text
LastRow.Offset(1, 6).Value = TextBox7.Text
LastRow.Offset(1, 7).Value = TextBox8.Text
LastRow.Offset(1, 8).Value = TextBox9.Text
LastRow.Offset(1, 9).Value = TextBox10.Text
MsgBox "One entry added to Database"

End If

response = MsgBox("Do you want to add another entry?", _
vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""

TextBox1.SetFocus

Else
MsgBox "Thank you, God bless you"
Unload Me
End If

End Sub

Private Sub CommandButton2_Click()
MsgBox "Thank you, God bless you"
End
End Sub




Thanks lots!
Anton

 

[carlo]

I use vba for a programme where people can enter data into a sheet by using
textboxes. My problem is that I need to make the entry into textbox9 to
appear as hyperlink. and I want the entry into textbox9 to be a hyperlink
that links to ENTRY.dwg. so let say the person enters ANTON in textbox9 the
entry will become hyperlink to the file ANTON.dwg. Hope this helps to givea
clearer picture. my programme is as follow

Private Sub CommandButton1_Click()
Dim LastRow As Object

Set LastRow = Sheet3.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
LastRow.Offset(1, 3).Value = TextBox4.Text
LastRow.Offset(1, 4).Value = TextBox5.Text
LastRow.Offset(1, 5).Value = TextBox6.Text
LastRow.Offset(1, 6).Value = TextBox7.Text
LastRow.Offset(1, 7).Value = TextBox8.Text
LastRow.Offset(1, 8).Value = TextBox9.Text
LastRow.Offset(1, 9).Value = TextBox10.Text
MsgBox "One entry added to Database"

End If

response = MsgBox("Do you want to add another entry?", _
vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""

TextBox1.SetFocus

Else
MsgBox "Thank you, God bless you"
Unload Me
End If

End Sub

Private Sub CommandButton2_Click()
MsgBox "Thank you, God bless you"
End
End Sub

Thanks lots!
Anton

That would look something like that:

with sheet3

.Hyperlinks.Add Anchor:=Selection, Address:= _
TextBox9.Text & ".dwg", TextToDisplay:= _
TextBox9.Text

end with

 

[Anton]

Hi Carlo, thanks for the help. However there is still a small problem. The
hyperlink does not appear on the intended cell but instead it appears on
wherever cell that I have clicked on before I run the macro. Is there any way
that maybe we can align the address with the textbox9 address?

Thank you so much

 

[carlo]

Hi Carlo, thanks for the help. However there is still a small problem. The
hyperlink does not appear on the intended cell but instead it appears on
wherever cell that I have clicked on before I run the macro. Is there any way
that maybe we can align the address with the textbox9 address?

Thank you so much









- Show quoted text -

Hi Anton

sorry about that it should of course look like that:

.Hyperlinks.Add Anchor:=LastRow.Offset(1, 9), _
Address:= TextBox9.Text & ".dwg", _
TextToDisplay:= TextBox9.Text

hth

Carlo