VBA clear code

[Shaun]

Hi all.

I am trying to get this code to work

Code: Select all

Option Explicit

Dim wb1 As Workbook
Dim ws1 As Worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
  ' Only process whole updates
  If Target.Columns.Count <> 16 Then Exit Sub
 
  ' Set-up workbook variables
  Set wb1 = ThisWorkbook
  Set ws1 = wb1.Sheets("Results")
 
  ' Stop any further updates until we have completed
  Application.EnableEvents = False
 
  ' Check for 2 in cell H1
  If ws1.Range("H1").Value = 2 Then
     ' Clear bet references
     ws1.Range("A2:f12").ClearContents
  End If
 
  ' Re-enable updates
  Application.EnableEvents = True
 End Sub


What i need it to do is every time a result is known i have a fomula on "Results" that checks if it won or lost if i get 2 losses in a row i need the vba code to clear the list of results so i can start again.

[dgs2001]

Shaun
Your problem may be the

Code: Select all

 Private Sub Worksheet_Change(ByVal Target As Range)
  ' Only process whole updates
  If Target.Columns.Count <> 16 Then Exit Sub
 

This tells the code to only run if 16 columns of info has changed on your sheet.
If there is other code already in the change event then you could put your code in a worksheet calculate event instead.

Duncan

[Roger]

Shaun, the test for target columns count being 16 is for the prices refresh sheet code. On the Results sheet, you won't have that many columns ever, so you need to take that test out.