Activate pasted URL in Macro

[designgeek]

Hi,

I have a spreadsheet where details of a URL hyperlink are concatenated and pasted depending on what is being viewed. I have no problem with the concatenation, the result is perfect.

I have a problem with having pasted the 'value' of the forumula generated URL, that I want it to become an active hyperlink.

I have a macro that picks up and pastes into a new presentation sheet a lot of details, one of which is the hyperlink. I want the hyperlink to be immediately active, so just to click on it for it to open a browser and go straight there. Instead of having to copy and paste into the browser the formulated URL.

.

Does anyone out there know how to get a macro to activate a cells URL?
By hand its simple to just activate the cell and then hit return to apply the change. This then activates the URL. I just can't get a Macro to do so.

Thanks
Catherine

 

[Legacy 98055]

The user can control that behavior by way of Auto Correct options. I would not depend upon it. See the hyperlinks.add method in vba help...

 

[designgeek]

Okay I can see where you going but I still have questions.

.Hyperlinks.Add Anchor:=.Range("f6"), _
Address:=" ", _
ScreenTip:="Click to access document in myWorkshop"

is the code that is relevant if I follow your theory. However in the address i want it to be able to pick up the value from the cell itself and paste it back as an active hyperlink.

.

Alternatively is there a way to do a concatenate macro to combine for example =concatenate("http://www.", C6, ".com") so that it becomes http://www.amazon.com if the value in C6 was 'amazon'.

If I 'paste special' in that concatenated formula as a value into a new cell, the hyperlink isn't active. Is there a macro way to do this?

I'm sorry, my only extent of knowledge in VBA is to deconstruct recorded VBA script when recording a macro!

Thanks for your help.
Catherine.

 

[Norie]

Catherine

There's a few things you could look at.

1 The HYPERLINK worksheet function. Using that you could create active hyperlinks from the result of the concatenation.

2 The FollowHyperlink VBA method. You could use that in conjuction with an event such as SelectionChange.

 

[designgeek]

Catherine

There's a few things you could look at.

1 The HYPERLINK worksheet function. Using that you could create active hyperlinks from the result of the concatenation.

2 The FollowHyperlink VBA method. You could use that in conjuction with an event such as SelectionChange.

*slaps forehead* how easy was that, the HYPERLINK worksheet function. I wasn't aware of that one and its saved my bacon. Thank you!

 

[Legacy 98055]

"...my only extent of knowledge in VBA is to deconstruct recorded VBA script when recording a macro!"

I do that all the time! However, there are many things that can be done by way of code that you will never be able to duplicate with the recorder. In your case, we can probably do away with the Copy...Paste and use value. Please post your code, as is, and it will be edited here by somebody...

 

[designgeek]

Thanks for your help Tom, but the hyperlink function that I wasn't aware of has solved my problem.

However I do plan to be doing more with Excel and my Excel VBA script book should be arriving soon!

Cheers to all who've helped me.

 

[Legacy 98055]

My pleasure. Enjoy learning...