I have a hyperlink in a textbox. How can I delete this hyperlink with vba code.

I have tried

Worksheets(1).Shapes("txtbox1").Hyperlinks.Delete

and similar code, but this doesn't work. Can anyone help?

Thank you


rgds
Jarle

Is this a hyperlink assigned to the textbox or the value of the text in the
textbox?

I put a textbox from the Drawing toolbar on a worksheet and rightclicked on it
and assigned it a hyperlink.

Then this would delete the hyperlink:

Dim TBox As TextBox
Set TBox = ActiveSheet.TextBoxes("Text Box 1")
'in case there isn't a hyperlink
On Error Resume Next
TBox.ShapeRange.Item(1).Hyperlink.Delete
On Error GoTo 0

Jarle wrote:
>
> I have a hyperlink in a textbox. How can I delete this hyperlink with vba code.
>
> I have tried
>
> Worksheets(1).Shapes("txtbox1").Hyperlinks.Delete
>
> and similar code, but this doesn't work. Can anyone help?
>
> Thank you
>
> rgds
> Jarle

--

Dave Peterson

I can't make your code work.

I have assigned the hyperlink with vba code as follows:

sh= Thisworkbook.Worksheets(1)
With sh
.Hyperlinks.Add Anchor:=.Shapes("txtbox1"), _
Address:=destination, _
TextToDisplay:="text"
End With

I am able to clear TextToDisplay with code:

sh.Shapes("txtbox1").TextFrame.Characters.Text = ""

but then the address/hyperlink is still there. It is probably possible to
clear the entire content of textbox (text and hyperlink) in one operation,
but I am not able to figure it out.




I am able to

Dave Peterson skrev:

> Is this a hyperlink assigned to the textbox or the value of the text in the
> textbox?
>
> I put a textbox from the Drawing toolbar on a worksheet and rightclicked on it
> and assigned it a hyperlink.
>
> Then this would delete the hyperlink:
>
> Dim TBox As TextBox
> Set TBox = ActiveSheet.TextBoxes("Text Box 1")
> 'in case there isn't a hyperlink
> On Error Resume Next
> TBox.ShapeRange.Item(1).Hyperlink.Delete
> On Error GoTo 0
>
> Jarle wrote:
> >
> > I have a hyperlink in a textbox. How can I delete this hyperlink with vba code.
> >
> > I have tried
> >
> > Worksheets(1).Shapes("txtbox1").Hyperlinks.Delete
> >
> > and similar code, but this doesn't work. Can anyone help?
> >
> > Thank you
> >
> > rgds
> > Jarle
>
> --
>
> Dave Peterson
>

What did you try?

Jarle wrote:
>
> I can't make your code work.
>
> I have assigned the hyperlink with vba code as follows:
>
> sh= Thisworkbook.Worksheets(1)
> With sh
> .Hyperlinks.Add Anchor:=.Shapes("txtbox1"), _
> Address:=destination, _
> TextToDisplay:="text"
> End With
>
> I am able to clear TextToDisplay with code:
>
> sh.Shapes("txtbox1").TextFrame.Characters.Text = ""
>
> but then the address/hyperlink is still there. It is probably possible to
> clear the entire content of textbox (text and hyperlink) in one operation,
> but I am not able to figure it out.
>
> I am able to
>
> Dave Peterson skrev:
>
> > Is this a hyperlink assigned to the textbox or the value of the text in the
> > textbox?
> >
> > I put a textbox from the Drawing toolbar on a worksheet and rightclicked on it
> > and assigned it a hyperlink.
> >
> > Then this would delete the hyperlink:
> >
> > Dim TBox As TextBox
> > Set TBox = ActiveSheet.TextBoxes("Text Box 1")
> > 'in case there isn't a hyperlink
> > On Error Resume Next
> > TBox.ShapeRange.Item(1).Hyperlink.Delete
> > On Error GoTo 0
> >
> > Jarle wrote:
> > >
> > > I have a hyperlink in a textbox. How can I delete this hyperlink with vba code.
> > >
> > > I have tried
> > >
> > > Worksheets(1).Shapes("txtbox1").Hyperlinks.Delete
> > >
> > > and similar code, but this doesn't work. Can anyone help?
> > >
> > > Thank you
> > >
> > > rgds
> > > Jarle
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

I tried:

Set TBox = sh.TextBoxes("txtbox1")
TBox.ShapeRange.Item(1).Hyperlink.Delete

I get :
Run-time error 1004 Application-defined or object-defined error


Dave Peterson skrev:

> What did you try?
>
> Jarle wrote:
> >
> > I can't make your code work.
> >
> > I have assigned the hyperlink with vba code as follows:
> >
> > sh= Thisworkbook.Worksheets(1)
> > With sh
> > .Hyperlinks.Add Anchor:=.Shapes("txtbox1"), _
> > Address:=destination, _
> > TextToDisplay:="text"
> > End With
> >
> > I am able to clear TextToDisplay with code:
> >
> > sh.Shapes("txtbox1").TextFrame.Characters.Text = ""
> >
> > but then the address/hyperlink is still there. It is probably possible to
> > clear the entire content of textbox (text and hyperlink) in one operation,
> > but I am not able to figure it out.
> >
> > I am able to
> >
> > Dave Peterson skrev:
> >
> > > Is this a hyperlink assigned to the textbox or the value of the text in the
> > > textbox?
> > >
> > > I put a textbox from the Drawing toolbar on a worksheet and rightclicked on it
> > > and assigned it a hyperlink.
> > >
> > > Then this would delete the hyperlink:
> > >
> > > Dim TBox As TextBox
> > > Set TBox = ActiveSheet.TextBoxes("Text Box 1")
> > > 'in case there isn't a hyperlink
> > > On Error Resume Next
> > > TBox.ShapeRange.Item(1).Hyperlink.Delete
> > > On Error GoTo 0
> > >
> > > Jarle wrote:
> > > >
> > > > I have a hyperlink in a textbox. How can I delete this hyperlink with vba code.
> > > >
> > > > I have tried
> > > >
> > > > Worksheets(1).Shapes("txtbox1").Hyperlinks.Delete
> > > >
> > > > and similar code, but this doesn't work. Can anyone help?
> > > >
> > > > Thank you
> > > >
> > > > rgds
> > > > Jarle
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

On which line?

And you deleted the "on error" lines that would have stopped the error from
appearing if there was no hyperlink associated with the textbox.

Jarle wrote:
>
> I tried:
>
> Set TBox = sh.TextBoxes("txtbox1")
> TBox.ShapeRange.Item(1).Hyperlink.Delete
>
> I get :
> Run-time error 1004 Application-defined or object-defined error
>
> Dave Peterson skrev:
>
> > What did you try?
> >
> > Jarle wrote:
> > >
> > > I can't make your code work.
> > >
> > > I have assigned the hyperlink with vba code as follows:
> > >
> > > sh= Thisworkbook.Worksheets(1)
> > > With sh
> > > .Hyperlinks.Add Anchor:=.Shapes("txtbox1"), _
> > > Address:=destination, _
> > > TextToDisplay:="text"
> > > End With
> > >
> > > I am able to clear TextToDisplay with code:
> > >
> > > sh.Shapes("txtbox1").TextFrame.Characters.Text = ""
> > >
> > > but then the address/hyperlink is still there. It is probably possible to
> > > clear the entire content of textbox (text and hyperlink) in one operation,
> > > but I am not able to figure it out.
> > >
> > > I am able to
> > >
> > > Dave Peterson skrev:
> > >
> > > > Is this a hyperlink assigned to the textbox or the value of the text in the
> > > > textbox?
> > > >
> > > > I put a textbox from the Drawing toolbar on a worksheet and rightclicked on it
> > > > and assigned it a hyperlink.
> > > >
> > > > Then this would delete the hyperlink:
> > > >
> > > > Dim TBox As TextBox
> > > > Set TBox = ActiveSheet.TextBoxes("Text Box 1")
> > > > 'in case there isn't a hyperlink
> > > > On Error Resume Next
> > > > TBox.ShapeRange.Item(1).Hyperlink.Delete
> > > > On Error GoTo 0
> > > >
> > > > Jarle wrote:
> > > > >
> > > > > I have a hyperlink in a textbox. How can I delete this hyperlink with vba code.
> > > > >
> > > > > I have tried
> > > > >
> > > > > Worksheets(1).Shapes("txtbox1").Hyperlinks.Delete
> > > > >
> > > > > and similar code, but this doesn't work. Can anyone help?
> > > > >
> > > > > Thank you
> > > > >
> > > > > rgds
> > > > > Jarle
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Thank you for helping and "pushing" me on this one.
I made your code work perfectly. The OnError did the trick.
I have several textboxes and was convinced there were a hyperlink in each.
It wasn't!

THANKS!

Dave Peterson skrev:

> On which line?
>
> And you deleted the "on error" lines that would have stopped the error from
> appearing if there was no hyperlink associated with the textbox.
>
> Jarle wrote:
> >
> > I tried:
> >
> > Set TBox = sh.TextBoxes("txtbox1")
> > TBox.ShapeRange.Item(1).Hyperlink.Delete
> >
> > I get :
> > Run-time error 1004 Application-defined or object-defined error
> >
> > Dave Peterson skrev:
> >
> > > What did you try?
> > >
> > > Jarle wrote:
> > > >
> > > > I can't make your code work.
> > > >
> > > > I have assigned the hyperlink with vba code as follows:
> > > >
> > > > sh= Thisworkbook.Worksheets(1)
> > > > With sh
> > > > .Hyperlinks.Add Anchor:=.Shapes("txtbox1"), _
> > > > Address:=destination, _
> > > > TextToDisplay:="text"
> > > > End With
> > > >
> > > > I am able to clear TextToDisplay with code:
> > > >
> > > > sh.Shapes("txtbox1").TextFrame.Characters.Text = ""
> > > >
> > > > but then the address/hyperlink is still there. It is probably possible to
> > > > clear the entire content of textbox (text and hyperlink) in one operation,
> > > > but I am not able to figure it out.
> > > >
> > > > I am able to
> > > >
> > > > Dave Peterson skrev:
> > > >
> > > > > Is this a hyperlink assigned to the textbox or the value of the text in the
> > > > > textbox?
> > > > >
> > > > > I put a textbox from the Drawing toolbar on a worksheet and rightclicked on it
> > > > > and assigned it a hyperlink.
> > > > >
> > > > > Then this would delete the hyperlink:
> > > > >
> > > > > Dim TBox As TextBox
> > > > > Set TBox = ActiveSheet.TextBoxes("Text Box 1")
> > > > > 'in case there isn't a hyperlink
> > > > > On Error Resume Next
> > > > > TBox.ShapeRange.Item(1).Hyperlink.Delete
> > > > > On Error GoTo 0
> > > > >
> > > > > Jarle wrote:
> > > > > >
> > > > > > I have a hyperlink in a textbox. How can I delete this hyperlink with vba code.
> > > > > >
> > > > > > I have tried
> > > > > >
> > > > > > Worksheets(1).Shapes("txtbox1").Hyperlinks.Delete
> > > > > >
> > > > > > and similar code, but this doesn't work. Can anyone help?
> > > > > >
> > > > > > Thank you
> > > > > >
> > > > > > rgds
> > > > > > Jarle
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Glad you got it working.

Jarle wrote:
>
> Thank you for helping and "pushing" me on this one.
> I made your code work perfectly. The OnError did the trick.
> I have several textboxes and was convinced there were a hyperlink in each.
> It wasn't!
>
> THANKS!
>
> Dave Peterson skrev:
>
> > On which line?
> >
> > And you deleted the "on error" lines that would have stopped the error from
> > appearing if there was no hyperlink associated with the textbox.
> >
> > Jarle wrote:
> > >
> > > I tried:
> > >
> > > Set TBox = sh.TextBoxes("txtbox1")
> > > TBox.ShapeRange.Item(1).Hyperlink.Delete
> > >
> > > I get :
> > > Run-time error 1004 Application-defined or object-defined error
> > >
> > > Dave Peterson skrev:
> > >
> > > > What did you try?
> > > >
> > > > Jarle wrote:
> > > > >
> > > > > I can't make your code work.
> > > > >
> > > > > I have assigned the hyperlink with vba code as follows:
> > > > >
> > > > > sh= Thisworkbook.Worksheets(1)
> > > > > With sh
> > > > > .Hyperlinks.Add Anchor:=.Shapes("txtbox1"), _
> > > > > Address:=destination, _
> > > > > TextToDisplay:="text"
> > > > > End With
> > > > >
> > > > > I am able to clear TextToDisplay with code:
> > > > >
> > > > > sh.Shapes("txtbox1").TextFrame.Characters.Text = ""
> > > > >
> > > > > but then the address/hyperlink is still there. It is probably possible to
> > > > > clear the entire content of textbox (text and hyperlink) in one operation,
> > > > > but I am not able to figure it out.
> > > > >
> > > > > I am able to
> > > > >
> > > > > Dave Peterson skrev:
> > > > >
> > > > > > Is this a hyperlink assigned to the textbox or the value of the text in the
> > > > > > textbox?
> > > > > >
> > > > > > I put a textbox from the Drawing toolbar on a worksheet and rightclicked on it
> > > > > > and assigned it a hyperlink.
> > > > > >
> > > > > > Then this would delete the hyperlink:
> > > > > >
> > > > > > Dim TBox As TextBox
> > > > > > Set TBox = ActiveSheet.TextBoxes("Text Box 1")
> > > > > > 'in case there isn't a hyperlink
> > > > > > On Error Resume Next
> > > > > > TBox.ShapeRange.Item(1).Hyperlink.Delete
> > > > > > On Error GoTo 0
> > > > > >
> > > > > > Jarle wrote:
> > > > > > >
> > > > > > > I have a hyperlink in a textbox. How can I delete this hyperlink with vba code.
> > > > > > >
> > > > > > > I have tried
> > > > > > >
> > > > > > > Worksheets(1).Shapes("txtbox1").Hyperlinks.Delete
> > > > > > >
> > > > > > > and similar code, but this doesn't work. Can anyone help?
> > > > > > >
> > > > > > > Thank you
> > > > > > >
> > > > > > > rgds
> > > > > > > Jarle
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson