How to add and delete Hyperlinks with VBA in Excel

How to add and delete Hyperlinks with VBA in Excel


I recently showed you how to select a
cell name that name a sheet this cell that you had selected and then copy a
sheet to that name so what we have here is students and we’re copying we’re
creating a name for each of the students and then we are copying the mark
breakdown to those those students names so for example if I click on the cell
below here and I type Mickey Mouse and then I click away and I click in there
and then I click the add button you have a sheet now that’s hyperlinked to Mickey
Mouse and you go back here now so if Mickey Mouse decided to not be in my
class anymore what I would do is I would click on Mickey Mouse and I would click
on the add button and it comes up with a message box this sheet already exists
did you want to delete it and so I’m gonna say yes the hyperlink is taken off
of Mickey Mouse and the Mickey Mouse she is deleted so let’s let’s explain how I
did this with my code so I I defined some values here sheet name to create SH
as a worksheet and as a range cont as worksheet always a range I respond to
the response that you get from your message box so she came to create is the
active cell whatever cell is created you’re naming that she she came to
create set o range is to the active cell set sheet two sheets one and that’s
sheet one is where I have all my marks okay so now let’s go
so for repetition of one to the number of worksheets you have if the sheet wrap
name equals the lower case sheet name to create
then you have you would have a message box here and this is where my eye
response comes message box this already exists do you want to delete it and yes
No so now if you select yes then you go
select case I response and so yes the selection hyperlink that there’s this
there you delete application display alerts false because it’s because when
you delete the sheet it’s going to tell you ask you do you really want to delete
the sheet and so worksheets sheet name to create is deleted application display
Litster so you’re just putting the displays or the alerts on again and then
there’s the other case which is VB VBA know which is to select at the end we’re
going to go to the end select and before you go to the end if you’re gonna go to
Andy which is a location in my script that it’s just before I set my own range
to nothing okay so then if if it’s not already there then it’s going to just
say we have sheets mark breakdown visible we have in the background sheets
mark breakdown copy after the sheets count so put it at the end active window
name equals sheet name to create so you’re renaming it the the name of the
cell that you’ve created she marked down break break down visible fall so you’re
you’re hiding that sheet you don’t need to see it she activate now hyperlinks
you’re adding the hyperlink of arranged to sheet name create and a one go to
sheet name to create sheet name to create so there’s my set o ring arranged
to nothing so it doesn’t have that always sticking there and then you end
sub so that’s how you create and delete hyperlinks please subscribe my code is
available if you want to have a copy of this file just email me directly thank
you

Leave a Reply

Your email address will not be published. Required fields are marked *