Referencing a cell in the active worksheet as part of a hyperlink

[Aseagz15]

I want to create a macro that follows a hyperlink that references a cell in the active workbook such as the example below. Say cell A1 says "baskeball", this would search for basketball in google.

Private Sub Label1_Click()


ActiveWorkbook.FollowHyperlink Address:="https://www.google.com/search?q=A1", NewWindow:=True


Unload Me


End Sub

Thanks!

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Application.EnableEvents = False
On Error GoTo M
Dim ans As String
Dim anss As String
Dim Search As String
ans = "https://www."
Search = Target.Value
anss = "Google.com/search?q=" & Search
Link = ans & anss
ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
Target.Value = ""
Application.EnableEvents = True
End If
Exit Sub
M:
Application.EnableEvents = True
MsgBox "Bad Link"
End Sub

 

[Aseagz15]

Hi My Answer is This. This is a great macro! My new question is:

Is it possible to reference multiple cells in a macro? Say that B1 has a year in it, and C1 has a month in it?

Here is an example of what I have done with the formula code: https://fakeexamplesite.com/yearin="&YEAR($B$1)&"&monthin="&MONTH($C$1). Cell B1 has the year being looked up, and cell C1 has the month being looked up.

What I would like to do is look up different types of accounting forms on a website such as balance sheet, income statement, etc.

Each of these will have a unique month, year, and account number. I have seen a macro open a grey form that has buttons and each button searched something different on a website.

If I can't get a grey form I would just like to know how to do what your macro had me do, but I need the hyperlink to be able to reference 4-5 different cells each time I type something different in cell A1.

Hopefully this makes sense, I am new to VBA.


Thanks for your help!

 

[My Aswer Is This]

You lost me.
I'm not sure what your wanting.
Your original request was very simple.

Your still wanting to search Google for something. But I'm not sure what.

I would need more specific details. Or maybe someone else here at Mr. Excel will see this posting and be able to understand more clearly your request.

 

[Aseagz15]

Sorry this is difficult to explain, let me try again.

Searching google was just an example.

At work I have a URL that has multiple parts, and I am able to reference specific cells in each sheet in excel. Some parts of the URL are the month, year, account, and the specific report type I would like to access.

I have created a hyperlink that takes me to our company website and to a specific report. The following is what I have (I have substituted the website name).
=HYPERLINK("https://examplewebsite.com/example?cmd=new&unit=01&action=balsht&yearin="&YEAR($A$1)&"&monthin="&MONTH($B$1)&"&account="&$C$1,$C$1)

The balsht is for balance sheet, that is the specific report I am accessing with the hyperlink. Other reports are sumrev (summary of revenue), detail (journal detail), etc.

I would like to click Ctrl + Shift + S to open a macro that will reference cells in each sheet (my spreadsheet has about 20 different sheets but the year, month, and account are in the same cells on each sheet). I would like the macro to open a pop up menu with different buttons labeled "balsht", "sumrev", "detail", etc. When I click the cells I want chrome to open a new tab that will open these specific reports on our company website.

Does this make more sense? Sorry this is hard to explain, I am an accountant but I am trying to make our spreadsheets more user friendly.