Table keeps resetting with VBA Code

[Mikedwi]

Hi,
I have an excel table which is getting the data for the first 6 (G-L) columns from a sharepoint list that we update, columns 7 and 8 (M & N) have a checkbox in created by a VBA code which I will post below.
(as per this tutorial video

)

However what I am finding, is that when we add a line or remove a line in the sharepoint list, and the data in excel updates, its changing the tickboxes in columns M & N instead of leaving them as they were, would anyone know a fix for this?

VBA Code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("M:N")) Is Nothing Then

Select Case Target
Case Is = Chr(254)
Target = Chr(168)
Cancel = True
Case Is = Chr(168)
Target = Chr(254)
Cancel = True
End Select

End If
End Sub

 

[shknbk2]

Does it work if you put this before your If Not Intersect line?

If Target.Count > 1 Then Exit Sub

 

[Mikedwi]

Does it work if you put this before your If Not Intersect line?

If Target.Count > 1 Then Exit Sub

Hi thank you for your quick response, unfortunately this did not work, I put this line in but its still the same

 

[Mikedwi]

I have even tried changing the code so that the range is the table itself instead of the actual columns but that didnt work either

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim masterTable As ListObject
Set masterTable = Worksheets("Planner").ListObjects("Master")

Dim plannedColumn As ListColumn
Set plannedColumn = masterTable.ListColumns("Planned")

Dim unplannedColumn As ListColumn
Set unplannedColumn = masterTable.ListColumns("Unplanned")

If Not Intersect(Target, plannedColumn.DataBodyRange) Is Nothing Or _
Not Intersect(Target, unplannedColumn.DataBodyRange) Is Nothing Then

Select Case Target.Value
Case Chr(254)
Target.Value = Chr(168)
Cancel = True
Case Chr(168)
Target.Value = Chr(254)
Cancel = True
End Select
End If
End Sub

 

[shknbk2]

Are you comfortable with debugging? Put your blinking cursor on the Set masterTable line and put a breakpoint there (pressing F9) (or alternatively click left mouse button in the gray column to the left of the code text window). Update the sheet, and the breakpoint should stop the code so that you can step through it line by line to follow its progress. Pressing F8 should progress the code line by line. If you put the mouse cursor over variables, you can see what their values are.

Evaluating "Target" this way, what is its range? If nothing pops up with the value of Target when you put your cursor over it, it might be multiple cells. Show the Immediate Window (Ctrl + G) and type this into the window followed by Enter (include the question mark):

?Target.Address

Underneath should appear the address of the target. How does this address match what you are expecting?

(After you eventually get the problem fixed, be sure to un-breakpoint the line so it quits stopping every time)