0

I have been working on an excel sheet that utilizes VBA to mark squares on a grid as occupied or vacant. I have two sets of code, one that is triggered by a cell change to mark the grid as occupied and the other that is triggered by a button to mark the grid as vacant and clear that row. The former of these works fine but the latter keeps throwing "Run-time error '1004': Application-defined or object-defined error". Both of these use the same sub to handle the grid changing but one does not get the error.

Code to Mark Vacant

Sub clearPlot()
    Dim rClear As Integer
    Dim clearedPlot As Integer
    rClear = ActiveCell.Row 'Find currently selected row
    If rClear > 1 Then 'Ignore header
        clearedPlot = Cells(rClear, 5).Value 'Get plot value
        If clearedPlot > 0 Then 'Ignore blanks
            garden clearedPlot, 0, 255, 0
            With Worksheets("Plot Log")
                .Range(.Cells(rClear, 1), .Cells(rClear, 7)).ClearContents
            End With
        End If
    End If
End Sub

Sub to find plot location and change color

If plot < 8 Then 'Find location of plot on grid
        r = 1
        c = plot
    ElseIf plot < 15 And plot > 7 Then
        If plot Mod 7 = 0 Then
            r = Int(plot / 7)
            c = 7
        Else
            r = Int(plot / 7) + 1
            c = plot Mod 7
        End If
    ElseIf plot < 21 And plot > 14 Then
            r = Int(plot / 7) + 1
            c = (plot Mod 7) + 1
    ElseIf plot < 26 And plot > 20 Then
         If plot Mod 7 = 0 Then
            r = Int(plot / 7) + 1
            c = 3
        Else
            r = Int(plot / 7) + 1
            c = (plot Mod 7) + 3
        End If
     ElseIf plot < 29 And plot > 25 Then
         If plot Mod 7 = 0 Then
            r = Int(plot / 7) + 1
            c = 5
        Else
            r = Int(plot / 7) + 2
            c = (plot Mod 7) - 2
        End If
        ElseIf plot < 31 And plot > 28 Then
            r = Int(plot / 7) + 1
            c = (plot Mod 7) + 5
        End If
    Worksheets("Plots").Cells(r, c).Interior.Color = RGB(red, green, blue) 'Source of error when run from clearPlot
End Sub

Code for marking plot as occupied

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rPlot As Integer
    Dim cPlot As Integer
    Dim occupiedPlot As Integer
    Set plots = Sheets("Plot Log").Range("E:E") 'Column to check for changes, plot numbers entered here
    
If Not Application.Intersect(plots, Worksheets("Plot Log").Range(Target.Address)) Is Nothing And Worksheets("Plot Log").Range(Target.Address).Row <> 1 And Not IsEmpty(Worksheets("Plot Log").Range(Target.Address).Value) Then 'Excludes header row and deletion
        rPlot = Sheets("Plot Log").Range(Target.Address).Row
        cPlot = Sheets("Plot Log").Range(Target.Address).Column
        occupiedPlot = Sheets("Plot Log").Cells(rPlot, cPlot).Value 'Get number for the plot rented
        garden occupiedPlot, 255, 0, 0
End If
End Sub

I have tried to specify the workbook and call the cells by using Range(Cells, Cells) but nothing stops the error from coming. Furthermore, when the error pops up, the code still executes and it marks the grid as it should and deletes the row.

Any help would be appreciated, thanks in advance!

Isaiah Malcolm
  • 1
  • 1
  • 1
    You're getting bitten by `Range(Cells, Cells)` in `Worksheets("Plot Log").Range(Cells(rClear, 1), Cells(rClear, 7))`. See [this](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) why you need to qualify the `Cells` calls. – BigBen Nov 05 '21 at 20:48
  • It happens for every value I try, those being from 1-30. All of those values work fine from the other sub though – Isaiah Malcolm Nov 05 '21 at 20:49
  • The problematic line is actually `Worksheets("Plot Log").Range(Cells(rClear, 1), Cells(rClear, 7))`. See the linked thread why, and the fix: `With Worksheets("Plot Log")`, `.Range(.Cells(rClear, 1), .Cells(rClear, 7)).ClearContents`, `End With`. Note the `.` in front of `Range` and `Cells`. – BigBen Nov 05 '21 at 20:51
  • Oh, let me take a look at that one. I assumed it was the other one because every time I hit debug that's where VBA would take me. – Isaiah Malcolm Nov 05 '21 at 20:53
  • I updated that section like so: ```With Worksheets("Plot Log")``` ```.Range(.Cells(rClear, 1), .Cells(rClear, 7)).ClearContents``` ```End With``` I am still getting the same error – Isaiah Malcolm Nov 05 '21 at 21:12
  • Put `Debug.Print r, c, red, green, blue` right before `Worksheets("Plots").Cells(r, c).Interior.Color = RGB(red, green, blue)`. What is the result in the Immediate Window? – BigBen Nov 05 '21 at 21:26
  • For some reason, it is giving me two sets of numbers when I run the sub. 1,3,0,255,0 and 1,0,255,0,0 Also when I remove the section for clearing the contents from the code, everything works without the error – Isaiah Malcolm Nov 05 '21 at 21:36
  • What's happening is that the clear contents code is causing the `Worksheet_Change` event to fire. Put the clear contents call back in, but put `Application.EnableEvents = False` before it, and `Application.EnableEvents = True` after it. There are other solutions as well. – BigBen Nov 05 '21 at 21:40
  • That worked! Thank you so much. I had thought that excluding blank cells in the if statement that would have taken care of that but I guess I missed something – Isaiah Malcolm Nov 05 '21 at 21:44
  • Thanks, just fixed that. Plot Log sheet is the active one that this is run from so I guess it never caused errors. – Isaiah Malcolm Nov 05 '21 at 21:52
  • 1
    Got it to work without using EnableEvents, thanks for all your help – Isaiah Malcolm Nov 05 '21 at 21:55

0 Answers0