0

Making a simple toroidal 14x14 chess board in Excel, placing multiple copies beside and below each other so it's easier to see how pieces move around the edge of the board. Need all boards to update when a piece is moved, i.e. value is changed, which apparently requires VBA.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row <= 42 And Target.Column <= 42 Then

Dim i As Integer, j As Integer, x As Integer, y As Integer
Dim z As String

x = Target.Row Mod 14
y = Target.Column Mod 14
z = Target.Value

For i = 0 To 2
    For j = 0 To 2
        Dim m As Integer, n As Integer
        m = i * 14 + x
        n = j * 14 + y
        MsgBox (i & "-" & j & "-" & m & "-" & n & "-" & z)
        Cells(m, n).Value = z
    Next j
Next i

End If

End Sub

(The MsgBox line is just test code.)

The line "Cells(m, n).Value = z" is the problem. Without it, the code works fine and the loop exits. When I put it in, it does what I want, fills in the correct cells with the changed value, but then the loop doesn't exit and gets really weird. I realize this is probably a very simple error, but I've been all over here and tutorial sites over the last five hours and have found nothing.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Lapid Palid
  • 3
  • 3
  • 1
    `Cells(m, n).Value = z` is modifying the sheet, which causes the Change again to fire again in a loop. The usual course of action is to temporarily disable events, using `Application.EnableEvents = False`, and then reverting to `True` at the end. – BigBen Apr 22 '21 at 01:09
  • I highly recommend to include proper error handling ([VBA Error Handling – A Complete Guide](https://excelmacromastery.com/vba-error-handling)) when using `Application.EnableEvents` to ensure you call `Application.EnableEvents = True` when ever an error occurs. Otherwise you might end up with events turned off and because this is an application wide property this will not only affect your code but all code (also of other workbooks) in the same Excel instance. So with this you might not only brick your code but code of others. – Pᴇʜ Apr 22 '21 at 06:39

0 Answers0