VBA help needed

[Captain Sensible]

Can anyone give me an idots guide to

Worksheet_Calculate()

Now the profit and loss is included in the worksheets I end up with redundant data left on the spreadsheet if the number of runners is less than the number of runners in the previous race when it auto selects markts.

As my calculations use the profit and loss figure i need to clear previous markets data. Some sheets start at B10 some A1 so an idiots guide to resetting data to "" would be useful so I can write any amendments myself.

Someone posted

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
Range(Cells(5, 17), Cells(100, 20)).Value = ""
End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub


But adjusting cell references doesn't seem to work for me

[Captain Sensible]

Used

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
Range(Cells(5, 17), Cells(100, 20)).Value = ""
End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

Instead and for some reason selection by change works fine all the ranges are easy to amend no idea why Worksheet_Calculate() wasn't working

[Ian]

I posted this code on the BF forum ... the bit you need to change is

Range(Cells(5, 17), Cells(100, 20)).Value = ""

Cells(5, 17) means Row 5 Column 17 ie. Q5
Cells(100, 20) means Row 100 Column 20 ie T100

so to extend to range by columns the code should be changed as follows

Range(Cells(5, 17), Cells(100, 22)).Value = ""

[Captain Sensible]

Thanks Ian, I'd figured out the cell references but for some reason it would recalculate.

Using

Private Sub Worksheet_SelectionChange(ByVal Target As Range

works fine so I'll stick with that.

[Ian]

I think SelectionChange only works if you physically change the active cell or range so changing the market in BA won't on its own cause the macro to run.

[Captain Sensible]

Got it working with

Private Sub Worksheet_Calculate()

no idea why it wouldn't work correctly before.

[markmac1]

Is there any trick to get this working in Excel 2000.
I know the procedure works Ok as I have triggered it manually but it doesn't seem to get initiated when the market refreshes.

Thanks

[GeorgeUK]

Are there any formulas in your workbook?
this is the only thing that would activate a worksheet_calculate event

If you want a macro to run on each refresh, does worksheet_change not do it?

[markmac1]

George,
I know diddly squat about VBA but i thought the intention of this macro was to clear the "trigger" are of the worksheet when the next race refreshed.
I tried setting up a simple formula which flip flopped the value in a cell on the spreadsheet but it didn't seem to work either.