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, befo=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!
-----Original Message-----
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 press
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 Sub
---
Message posted from http://www.ExcelForum.com/
.