Need a little help in my macro please

[qitrader888]

This is the macro code i'm using to import data from YAHOO into excel
The only thing I need is to be able to run the macro everytime I pres
ENTER in any of these 5 boxes (C3, F3, I3, L3, O3) How do I do that?

Thanks and any help is appreciated.

Sub Stock1()
'
' macro1 Macro
' Macro recorded 2/26/2004 by Fongsaiyuk
'
Dim CoSym As String

CoSym = Worksheets("Main").Range("C3").Value

With Sheets("Stock 1").QueryTables(1)
.Connection = _

"URL;http://table.finance.yahoo.com/d?a=10&b=23&c=2003&d=1&e=24&f=2004&g=d&s=
& CoSym
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With

End Sub
Sub Stock2()
'
' macro1 Macro
' Macro recorded 2/26/2004 by Fongsaiyuk
'
Dim CoSym As String

CoSym = Worksheets("Main").Range("F3").Value

With Sheets("Stock 2").QueryTables(1)
.Connection = _

"URL;http://table.finance.yahoo.com/d?a=10&b=23&c=2003&d=1&e=24&f=2004&g=d&s=
& CoSym
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With

End Sub


Sub Stock3()
'
' Stock3 Macro
' Macro recorded 2/26/2004 by Fongsaiyuk
'

Dim CoSym As String

CoSym = Worksheets("Main").Range("I3").Value

With Sheets("Stock 3").QueryTables(1)
.Connection = _

"URL;http://table.finance.yahoo.com/d?a=10&b=23&c=2003&d=1&e=24&f=2004&g=d&s=
& CoSym
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With

End Sub
Sub Stock4()
'
' macro1 Macro
' Macro recorded 2/26/2004 by Fongsaiyuk
'
Dim CoSym As String

CoSym = Worksheets("Main").Range("L3").Value

With Sheets("Stock 4").QueryTables(1)
.Connection = _

"URL;http://table.finance.yahoo.com/d?a=10&b=23&c=2003&d=1&e=24&f=2004&g=d&s=
& CoSym
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With

End Sub

Sub Stock5()
'
' macro1 Macro
' Macro recorded 2/26/2004 by Fongsaiyuk
'
Dim CoSym As String

CoSym = Worksheets("Main").Range("O3").Value

With Sheets("Stock 5").QueryTables(1)
.Connection = _

"URL;http://table.finance.yahoo.com/d?a=10&b=23&c=2003&d=1&e=24&f=2004&g=d&s=
& CoSym
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With

End Su

 

[Guest]

Rather than pressing enter, this one adds a "Update
Stocks" menu item to call your macros. So all you have to
do is right click the mouse while one of these cells is
selected.

Add this code in the sheet where these cells are located:

Private Sub Worksheet_BeforeRightClick(ByVal Target As
Range, _
Cancel As Boolean)
Dim icbc As Object
For Each icbc In Application.CommandBars
("cell").Controls
If icbc.Tag = "brccm" Then icbc.Delete
Next icbc
If Not Application.Intersect(Target, Range("k3:v22")) _
Is Nothing Then
With Application.CommandBars("cell").Controls _
.Add(Type:=msoControlButton, before:=1, _
temporary:=True)
.Caption = "Update Stocks"
.OnAction = "GetStock"
.Tag = "brccm"
End With
End If
End Sub

Then Add this code in a Module:

Public Sub GetStock()
If ActiveCell.Row <> 3 Then Exit Sub
c = ActiveCell.Column
Select Case c
Case 3
Stock1
Case 6
Stock2
Case 9
Stock3
Case12
Stock4
Case15
Stock5
Case Else
Exit Sub
End Select
End Sub

Good Luck!

 

[Don Guillett]

try a double click event instead. Right click sheet tab>view code>insert
this>SAVE
I don't know the layout and ultimate purpose of this (send me a workbook if
you like) but maybe a data validation drop down would work better. If you
are trying to get many at once, there are much easier ways than a separate
sheet for each symbol. But, it looks like you want one at a time.....???? I
work with these projects a lot.

I have available an excel historicals program where you just enter the
symbols, start date, stop date, and choice of daily, weekly, etc. from a
drop down list. Then one mouse click gets all symbols requested at once. It
could even chart the different symbols if desired from a double click on the
symbol. You can even copy the chart as a .jpg with one mouse click.

90 days is Start Date Stop Date
10/31/2003 1/1/2003 1/29/2004
Date ^TYX ^TnX ^FvX
6/20/2003 4.46 3.40 2.30
6/19/2003 4.42 3.34 2.24
6/18/2003 4.39 3.36 2.30




Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Row <> 3 Then Exit Sub
If Target.Column <> 3 And Target.Column <> 6 And Target.Column <> 9 _
And Target.Column <> 12 And Target.Column <> 15 Then Exit Sub
'MsgBox ActiveCell
cosym = ActiveCell.Value
With Sheets(cosym).QueryTables(1)
Connection = _
"URL;http://table.finance.yahoo.com/d?a=10&b=23&c=2003&d=1&e=24&f=2004&g=d&s
=" & cosym
..WebSelectionType = xlEntirePage
..WebFormatting = xlWebFormattingAll
..WebPreFormattedTextToColumns = True
..WebConsecutiveDelimitersAsOne = True
..WebSingleBlockTextImport = True
..WebDisableDateRecognition = False
..WebDisableRedirections = True
..Refresh BackgroundQuery:=False
End With
End Sub

 

[qitrader888]

For some reason i'm getting a compilation error on both written codes
Where can I send you my worksheet? Can you send me a PM?

thank

 

[Don Guillett]

Send to the address shown here...........I will be out for a few hours.

 

[qitrader888]

I try refreshing my data and I have found out that my dates are no
updated. How can fix this in my macro?

Thank