Run Macro If Cell Value = 1

[boohah]

Hi

I have a spreadsheet with several worksheets and I need to run a macro to copy a webpage and paste it into one of the worksheets. This I am doing fine already. What I would like to do is have the macro run automatically if a cell equals a certain value.

The first Worksheet is named "Data" and if the value of cell AE1 = 1 I want the macro to activate. The second worksheet is named "Arg" and it is here that i want the webpage pasted.
Here is the code I have:

Private Sub Worksheet_Calculate()
If [AE3] = 1 Then
     Sheets("Arg").Select
     Range("G3:BV1000") = "" ' erase previous data
    Range("G3").Select
     
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "http://www.google.com" ' should work for any URL
        Do Until .ReadyState = 4: DoEvents:  Loop
        End With
         
        IE.ExecWB 17, 0 '// SelectAll
        IE.ExecWB 12, 2 '// Copy selection
        ActiveSheet.Paste
        Range("G3").Select
        IE.Quit
        IE.Quit ' just to make sure
   
     Sheets("Data").Select
    Range("A1").Select
End If
End Sub

what have I done wrong? Any help would be appreciated.

Doug

[boohah]

I have tried it a different way with the following code and still no luck:

Private Sub Worksheet_Calculate()
' change RangeName to the range you need
If Range("AE3").Value = 1 Then
Sub Test()
    Dim IE As Object
     
     Sheets("Arg").Select
     Range("G3:BV1000") = "" ' erase previous data
    Range("G3").Select
     
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "http://www.leagueday.com/latest.asp?league=argentina" ' should work for any URL
        Do Until .ReadyState = 4: DoEvents:  Loop
        End With
         
        IE.ExecWB 17, 0 '// SelectAll
        IE.ExecWB 12, 2 '// Copy selection
        ActiveSheet.Paste
        Range("G3").Select
        IE.Quit
        IE.Quit ' just to make sure
        
     Sheets("Data").Select
    Range("A1").Select
    End Sub
End If
End Sub

Any help would be appreciated

[wamp]

in "ThisWorkbook" add the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Sheets("Data").Range("AE1").Value = 1 Then   ' specify the cell you want to activate your macro here
Run "Test"
End If
End Sub

then in a module, insert your macro. for example

Sub Test()
    Dim IE As Object
     
     Sheets("Arg").select
     Range("G3:BV1000").clearcontents
     Range("G3").Select
     
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "http://www.leagueday.com/latest.asp?league=argentina" ' should work for any URL
        Do Until .ReadyState = 4: DoEvents:  Loop
        End With
         
        IE.ExecWB 17, 0 '// SelectAll
        IE.ExecWB 12, 2 '// Copy selection
        ActiveSheet.Paste
        Range("G3").Select
        IE.Quit
        IE.Quit ' just to make sure
        
     Sheets("Data").Select
    Range("A1").Select
    End Sub
End If
End Sub

[snb]

I think you'd be better off using a webquery (menubar/data/external data, webquery). one of its properties is the refreshtime.

[boohah]

Thanks snb, i think i am good with what you have said already. Thank you for your help

Doug