Macro help

[TheGoldenVision]

At the moment I have auto select markets running and I've got a macro, which I start manually, which simply copies the values of the prices on offer to a different column and then I use that static list of prices to do some calcs as and when the worksheet updates the prices.
What I need is to be able to do is have the macro run automatically whenever the next races details get loaded into the worksheets.
Can this be done?.... I'm messing with the "Worksheet_Change(ByVal Target As Range)" but can't get the macro to run even though the target cell changes (I'm using A1 as the target) value. Is this function only operable whenever the ENTER key is hit?

Any ideas?

[osknows]

Hello,

Does this help?

Code: Select all

Public market_name As String
Public trigger As Boolean


Private Sub Worksheet_Calculate()


'check to see if market is different then reset variables
If market_name <> ThisWorkbook.Sheets("Your Worksheet").Range("a1").Value Then
    trigger = False
    market_name = ThisWorkbook.Sheets("Your Worksheet").Range("a1").Value
End If


    'if trigger hasn't already fired continue
    If trigger = False Then
   
    'enter code here
           
    End If

trigger = True

End Sub


[TheGoldenVision]

Hi Osknows
Thanks for that. I've run it, but it seems to be a bit iffy. My macro ran when the worksheet changed but appeared to repeat itself dozens of times until an error message "Out of stack space" showed. After that when I clicked from race card to race card nothing happens... it seems as if the BA to Excel link has broken and manually running the macro no longer works. Something must have crashed..
Nearly there..

[osknows]

It's hard to say exactly what the problem is without seeing your code but it might be the code is changing the sheet and re-triggering the events in an endless loop.

Use 'Application.EnableEvents = False' before writing to the sheet and 'Application.EnableEvents = true' to turn events back on

If you're still stuck send me a PM with your code and I'll have a look for you

Os