You are here:

Excel/macro within a formula

Advertisement


Question
QUESTION: Morning Tom

question is this

we want to create a macro that will call up a website url based on the value (site info) we enter into a cell, without having to keep going back into vba and changing the address.  ie

cells
A1 = google.com
A2 = bing.com


Macro Cells
C1 = A1
C2 = A2

we want to be able to click on C1 and have it call up the url that is typed in A1, thereby making it dependent on what the user enters into cells A1 and A2 and if they want to change the site they can do so without us having to back into vba and update

Hope this is clear

Thanks Tony

ANSWER: Tony,

You would need to supply the code you are using now with the hard coded URL.  Then I could show you how to replace some of that with a variable or cell reference to get th4 address.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: i thought i had figured it out but doesn't work

this code i found and it works for one cell but not more than one, we want 6 cells to be variables

Sub Automate_IE_Load_Page()
'This will load a webpage in IE
   Dim i As Long
   Dim URL As String
   Dim IE As Object
   Dim objElement As Object
   Dim objCollection As Object

   'Create InternetExplorer Object
   Set IE = CreateObject("InternetExplorer.Application")

   'Set IE.Visible = True to make IE visible, or False for IE to run in the background
   IE.Visible = True

   'Define URL
   URL = Range("A1") ' "http://www.automateexcel.com/"

   'Navigate to URL
   IE.Navigate URL

   ' Statusbar let's user know website is loading
   Application.StatusBar = URL & " is loading. Please wait..."

   ' Wait while IE loading...
   'IE ReadyState = 4 signifies the webpage has loaded (the first loop is set to avoid inadvertently skipping over the second loop)
   Do While IE.ReadyState = 4: DoEvents: Loop   'Do While
   Do Until IE.ReadyState = 4: DoEvents: Loop   'Do Until

   'Webpage Loaded
   Application.StatusBar = URL & " Loaded"
   
   'Unload IE
   Set IE = Nothing
   Set objElement = Nothing
   Set objCollection = Nothing
   
End Sub
   

i call up this macro and it calls up the what's in cell A! but cant figure out how to call up A2:A6 entries....

before that this is the code i was using that worked fine to call up a tutorial video from website but cant figure out how to have it call up a cell value

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cancel = True

If Target.Count > 1 Then Exit Sub

Select Case Target.address(0, 0)
   
      
Case "C1"
       
   Application.ScreenUpdating = False
   
   ActiveWorkbook.FollowHyperlink "google.com"
   
   Exit Sub
   
   Application.ScreenUpdating = True
   
Case "C2"
 
   Application.ScreenUpdating = False
   
   ActiveWorkbook.FollowHyperlink "msn.com"
   
   Exit Sub
   
   Application.ScreenUpdating = True

End Select
End Sub

Answer
Tony,

I guess the first question would be why don't you just put a hyperlink in the cells in A1:A6.  

There is a hyperlink worksheet function which you could use if you want to change information in column C to change the behavior of the hyperlink.   You can see the details in help.

anyway, assuming the address of the web page is in column A then

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cancel = True

If Target.Count > 1 Then Exit Sub

set r = Range("C1:C6")
if not intersect(target, r) is nothing then
       
   Application.ScreenUpdating = False
   
   ActiveWorkbook.FollowHyperlink cells(Target.row, "A").Text
      
   Application.ScreenUpdating = True

End if    
End

would be the type of code I would use.

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.