Creating a button with hyperlink plus more

[randy21m]

Hello All,

My apologies if something like this has been asked before, but I was unable to locate exactly what I was looking for. In a nut shell:

1. I have put text in cell A1 which is Science
2. In cell A3 I have created a button with a hyperlink to www.google.com
3. The button works fine and it opens google as expected

What I would like to be able to do, is when I click the button, it copies the text from cell A1, opens google, pastes the text in the search bar and automatically does the search and brings up relative threads having to do with the word Science.
Is this something I can achieve with a simple button, or do I need a macro? Any direction/assistance anyone can provide would be greatly appreciated. Thank you in advance.

Randy

 

[Tom Urtis]

You did not say if the button you are clicking is from the Forms toolbar or the activex control toolbox.

If it is a Forms button, attach this macro to it:

Sub Test1()
Dim strSearch$, strSearchWhat$
strSearchWhat = Range("A1").Text
strSearch = "http://www.google.com/search?"
strSearch = strSearch & "http://www.google.com/search?num=100&hl=en&lr=&ie=ISO-8859-1&q=" & strSearchWhat
If Len(strSearchWhat) > 0 Then ActiveWorkbook.FollowHyperlink strSearch
End Sub

If it is a command button from the activex toolbox, then assuming it is named CommandButton1, right click on your sheet tab, select View Code, and paste this into the sheet module. Press Alt+Q to return to the worksheet.

Private Sub CommandButton1_Click()
Dim strSearch$, strSearchWhat$
strSearchWhat = Range("A1").Text
strSearch = "[URL]http://www.google.com/search[/URL]?"
strSearch = strSearch & "[URL]http://www.google.com/search?num=100&hl=en&lr=&ie=ISO-8859-1&q[/URL]=" & strSearchWhat
If Len(strSearchWhat) > 0 Then ActiveWorkbook.FollowHyperlink strSearch
End Sub

 

[randy21m]

Hi Tom,

Thank you for your quick reply, and my apologies that I did not identify which type of button I am using. I am using a Form Button, attempting to anyway. With your assistance, I am able to make one of my buttons work exactly as expected. Thank you for that. I have another sheet in my workbook that databases my movies. I would like to include a button for each movie that would search http://www.imdb.com as you have done with google above. I have attempted to modify your code with IMDB's URL, and while I can get it to pull up IMDB, it doesn't seem to copy the title of the move over, nor search automatically.
Further thought into my project leads me to wanting to change my design, and instead of creating a button for every single entry I want to do a search on, is there way to create a button that will select the current cell I have selected, and then do the search to the URL specified?
If you need additional information, please let me know. I thank you in advance for your assistance.

Randy

 

[randy21m]

I want to say thank you again to Tom for getting me 90% of the way. I was able to find the correct URL for IMDB. I know it may sound simple to some, but it was not as easy as putting in http://www.imdb.com, and doing a search on IMDB results to /title/randomnumberhere. Searching the internet I found someone else trying to do a search with VB to IMDB and they had the correct URL I needed.
Now the only thing I have left to figure out, is how to make it so that the "Range" is not static. I need it to select the cell I currently have highlighted. Does anyone happen to know the code for this? Here is the code (Tom's code actually, with the URL modified) I currently have in place:

Dim strSearch$, strSearchWhat$
strSearchWhat = Range("A8").Text
strSearch = "http://www.imdb.com/find?"
strSearch = strSearch & "http://www.imdb.com/find?s=all&q=" & strSearchWhat
If Len(strSearchWhat) > 0 Then ActiveWorkbook.FollowHyperlink strSearch

Thank you in advance.

Randy

 

[Tom Urtis]

Now the only thing I have left to figure out, is how to make it so that the "Range" is not static. I need it to select the cell I currently have highlighted.

Would it help if you change this
strSearchWhat = Range("A8").Text

to this
strSearchWhat = ActiveCell.Text

 

[randy21m]

That worked! Thank you very much!

V/r
Randy

 

[randy21m]

As a side note, not that it altered the functionality in any way. But I noticed the address bar when resolving the output was resulting in this:

http://www.imdb.com/find?http://www.imdb.com/find?s=all&q=10,000 BC

Notice the double "http://"? Though it did bring up the search just fine and no one would notice the difference as far as results.

As a test, I removed the line:

strSearch = "http://www.imdb.com/find?"

And it now results in only one "http://".

Thank you again.

Randy

 

[Tom Urtis]

Thanks for the follow-up, good to know.