Macro Help in Excel

nigel3a

Hi Everyone,

I need help running a macro when a cell changes value on a sheet. It'll run fine by clicking a button, but I wanted it to do it automatically.
Any help would be appreciated.

Thanks

This is the code

Sub Sort()
'
' Sort Macro
'

'
Range("B3:P22").Select
ActiveWorkbook.Worksheets("Table and stats").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Table and stats").Sort.SortFields.Add Key:=Range( _
"O4:O22"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Table and stats").Sort.SortFields.Add Key:=Range( _
"P4:P22"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Table and stats").Sort
.SetRange Range("B3:P22")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("N23").Select
End Sub

Mirno

The macro needs to be named "Worksheet_Change" and take a range as an argument - this will contain the cell(s) changed in the event.

The (shamelessly stolen from MS's answers site that Google directed me to when I searched) macro example:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("A1:C10")

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

' Display a message when one of the designated cells has been
' changed.
' Place your code here.
MsgBox "Cell " & Target.Address & " has changed."

End If
End Sub

Note that if the macro itself changes anything, especially if it's within the range you're interested in you'll need to disable events for the duration of the macro - otherwise you'll end up in an infinite recursion.

Application.EnableEvents = false
...
Application.EnableEvents = true

Mirno

nigel3a

Hi Mirno,
Thanks for that, however it'll only work if I physically change something on the sheet, I want to run the Macro, if one of the cells change due to an addition in another sheet.
Thanks though

Nigel

paddyrg

Short version - I don't think you can.

---

Long version - VBA is 'event-driven' - the event catcher needs an event to respond to within the same context. If it were to watch the values of all cells in linked sheets it would require statefulness and some kind of polling - a very different requirement indeed.

AngelAlexa

No, I don't think it can be made to work automatically either.

andydiysaver

it can be done, just use public variables - here's an example I did which will monitor range C1 to C100, if you change anything in the sheet that changes a value in that range, then it'll act. The trick is to store the before and one removed values and compare


Public Ctotal As Double
Public Dtotal As Double


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'turns off events so signal is not duplicated

Dim x As Integer, y As Integer
x = Target.Row
y = Target.Column
Ctotal = Excel.WorksheetFunction.Sum(Range("C1", "C100"))
'or whatever range you want
If Ctotal = Dtotal Then GoTo Out




MsgBox ("changes in total of C column, range 1 to 100")
'your act on code change here
Out:
Dtotal = Excel.WorksheetFunction.Sum(Range("C1", "C100"))
Application.EnableEvents = True
'starts up events again
End Sub

andydiysaver

the x and y is just for flex, doesn't need to be there, but you can name ranges using this etc

nigel3a

Hi thanks for your replies. It can be done, as I run my world cup spreadsheet using it. I just don't know how. I had a chap on here do another sheet for me, but I can't adapt it to run mine. and I can't seem to get hold of him.


They're run as modules except the first one which is tagged onto a sheet if is changes. If you want to drop me an email to nigel3a@gmail.com and I'll send you the two files so you can see what I mean. Probably easier to see. A picture paints . . . . and all that.

Nigel

andydiysaver

haha world cup spreadsheet, I have one of these too - it reads quotes from a site called betfair, I am not sure this site is well looked upon on this forum......

nigel3a

Not quite sure what betfair has to do with this? It doesn't read from anywhere . . . it's a spreadsheet

rmg1

From what I can gather, you want a cell-chnage of Sheet2 to run a macro to affect something on sheet1.
If I've got that right, it's quite straight-forward to setup and the complexity depends on what you want to affect.
Again, If I've got that right, let me know what you want the changes to be and I'll see what I can put together.