Run a Macro When Loading a New Market

[Steve Voltage]

I have a macro called Macro2 how is it possible to run this macro every time i load a new market please?

[Captain Sensible]

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value


insert macro coding here


End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

[Steve Voltage]

Thanks Captain

[Steve Voltage]

Next question the code below works when the cells are manually changed but not when linked to excel. is there a simple answer to this?

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

For i = 1 To 3

If Target.Row = i And Target.Column = 1 Then
Cells(i, 2) = Cells(i, 2) + 1
End If
Next i
End Sub

[Steve Voltage]

anybody?

[Ian]

What do you mean by "linked to excel" - it is excel ?

[Steve Voltage]

What do you mean by "linked to excel" - it is excel ?

I mean linked to Betting Assistant, sorry

[Ian]

I think you'll have to explain what you are trying to do.

A1 contains the market name - so will only change occasionally.
A2 & A3 contains constants which will never change.

And you are incrementing B2 and B3 but these are updated at
every refresh in any case.

[knot]

I think you need to change

Private Sub Worksheet_Calculate()

for

Private Sub Worksheet_Change()

[Ian]

I think you need to change

Private Sub Worksheet_Calculate()

for

Private Sub Worksheet_Change()

I don't think so because he is trying to determine which cells
have been changed.

[osknows]

BA does 2 types of refreshes so the range object Target returns a different row/column value on each refresh each time. eg

1. Target.Row = 1 and Target.Column = 1
2. Target.Row = 5 and Target.Column = 20

Therefore your code will only run If Target.Row = i And Target.Column = 1

try this and you'll see the returned values on each refresh

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox (Target.Row & Chr(10) & Target.Column)

End Sub