Macro to Run When Certain Cells Change

[Leslie]

I am using Excel 2002 with Windows XP.

I have created a worksheet which contains data about my shareholdings. I
frequently download from the Web a .csv file which includes a column
consisting of the day's closing prices for the shares which I hold and I
then import that data into a column of my worksheet. I have also created a
macro which, when I run it, then updates a number of other columns of my
worksheet by reference to the closing price data just imported.

I used to import the closing price data by copying a column from the .csv
file and pasting into a column of the destination worksheet, but I recently
became aware that I could do the importing more easily by using the "import
external data" option. I therefore began to use that option and I also
ticked the "refresh data on file opening" option.

That has had the effect that, when I open the destination worksheet after
having downloaded the .csv file, I can just run my macro and my worksheet is
almost completely updated. There are only two other things left to do after
the macro has run and I still do them manually. One is to change the date in
the destination worksheet to the current date and the other is to change the
closing average of the Australian equivalent of the Dow Jones to the current
average.

I want, if I can, to make the updating of the destination worksheet
completely automatic (except for saving and closing), in other words, I want
to automate three more things: (1) updating the current date; (2) updating
the current closing average; and (3) the running of my macro after the
automatic refreshing of the closing prices data. Of those three things, it's
the third I want to achieve first.

I've had no education in programming of any type, so I've been trying my
best just by reading the VBA helpfiles and such things as I've found on the
Web.

One of the things I've found on the Web is MSKB article number 213612, "XL:
How to Run a Macro When Certain Cells Change". I typed out the sample code
in that document (omitting the comments) and it worked. It's as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("A1:C10")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
MsgBox "Cell " & Target.Address & " has changed."
End If

End Sub

There's a comment in that sample code, "Place your code here", immediately
before the line beginning "MsgBox ...", so I tried that, at the same time
omitting the line beginning "MsgBox ..." and changing the Range in the "Set
KeyCells" line to "H4:H36", because that's where the data imported from the
..csv file goes in my destination worksheet. The thing therefore looks as
follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

Set KeyCells = Range("H4:H36")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

Columns("D:G").Select
Selection.EntireColumn.Hidden = False
Range("C5:C36").Select
Selection.Copy
ActiveWindow.LargeScroll Down:=-1
Range("E5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("E5"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("E:F").Select
Selection.EntireColumn.Hidden = True
Columns("K:N").Select
Selection.EntireColumn.Hidden = False
Range("J5:J36").Select
Selection.Copy
ActiveWindow.LargeScroll Down:=-1
Range("L5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("L5"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("L:M").Select
Selection.EntireColumn.Hidden = True
Columns("O:R").Select
Selection.EntireColumn.Hidden = False
Range("N5:N36").Select
Selection.Copy
ActiveWindow.LargeScroll Down:=-1
Range("P5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("P5"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("P:Q").Select
Selection.EntireColumn.Hidden = True
Range("A1").Select

End If
End Sub

The lines between "Is Nothing Then" and "End If" have come directly from my
macro which works properly when I run it manually. The only bits I removed
from it for the purpose of trying to cram it into the model in the MSKB
document were the opening line saying "Sub Security_Macro()" and the closing
line saying "End Sub".

The thing doesn't work. (I can tell because more than one of the columns
which changes when I run my macro manually no longer displays figures, but
instead symbols telling me it's not working.)

I hope I've done something obviously stupid, so that I'll be able to be
corrected immediately. Can anyone tell me what it is? Thank you

 

[Nigel]

Hi Leslie

Not sure I understand your entire process but you might consider using the
open workbook event to trigger your macros. This works by placing some code
behind your 'destination' workbook and when you open it it fires the macro.
For example in the ThisWorkbook object listed in the VBA project explorer
put the following......

Private Sub Workbook_Open()
Call macro1
End Sub

You could also put the following line in the same event to automatically put
the current date /time into a specific sheet and cell as follows........

Private Sub Workbook_Open()
Worksheets("Sheet1").Range("A1").Value = Now
Call macro1
End Sub

As far as updating the current closing average you'll presumably need to get
that off the web page you are interrogating.

HTH
Cheers
Nigel

 

[Guest]

Nigel

Thanks for taking the trouble to reply

I haven't tried triggering my macro by using the open workbook (or worksheet) event

That's because I've got the "refresh data on file opening" box ticked in the destination worksheet, so that it automatically imports the data from the .csv file I've earlier downloaded from the Web

I don't know when that refreshing occurs in relation to an open worksheet event

If the refreshing occurs later, then running my macro using the open worksheet event won't produce a useful result for me.

If the refreshing occurs earlier, then I don't know whether the open worksheet event will work after the refreshing

I suppose the only thing to do is to try on a test worksheet and see what happens

Thanks again.