0

I am just a newbie in excel and vb scripts and have added ActiveX checkbox control with a custom function to color the row if the checkbox is checked and remove it if unchecked.

Macro code:

Public Sub GenerateCheckBox()
    Dim i As Integer
    Dim linkedcell
    linkedcell = "B"
    initialTop = 15
    totalRow = (Cells(Rows.Count, 4).End(xlUp).row - 1)

    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    Dim checkbox As OLEObject

    Range("A:C").Insert
    Range("A1").Value = "Resolved"
    
    For i = 1 To totalRow
        activeRow = i
        Set checkbox = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
            DisplayAsIcon:=False, Left:=0, Top:=initialTop, Width:=13, Height:=15)
        checkbox.linkedcell = linkedcell & activeRow
        checkbox.Object.Caption = ""
        checkbox.Object.BackStyle = 0
        checkbox.Object.Value = False
        initialTop = initialTop + 15

        ws.Range("C" & activeRow).Formula = "=ColorRowIF(B" & activeRow & "=True)"
    Next i
End Sub

Public Function ColorRowIF(Condition As Boolean, Optional r As Integer = 100, Optional g As Integer = 255, Optional b As Integer = 4) As String
    Dim row As Integer
    row = Application.Caller.row

    If Condition = True Then
        ' Paints the checked row with color
        ActiveSheet.Evaluate "ColorRow(" & row & ", " & r & ", " & g & ", " & b & ")"
    Else
        'Removes the color from the unchecked row
        ActiveSheet.Evaluate "RemoveRowColor(" & row & ")"
    End If

    ColorRowIF = Condition
End Function

Sub DeleteAll()
    Dim obj As OLEObject
    For Each obj In ActiveSheet.OLEObjects
        obj.Delete
    Next obj
    Columns("A:C").Delete
End Sub

This code works perfectly until the rows exceed more than 2000. After 2000 rows, column C returns the "#VALUE!" error for all the existing cells and does not evaluate the result.

Also, I have added a method to delete the ActiveX checkbox and the first 3 columns which also work perfectly fine for 2000 rows added with the checkbox and formulas.

After that VB throws below error:

Run-time error '-2147319765 (8002802b)':

Automation error Element not found

When I click the debug option the cursor shows For Each obj In ActiveSheet.OLEObjects method as having the error.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
PaladiN
  • 4,625
  • 8
  • 41
  • 66
  • You are using [a trick](https://stackoverflow.com/a/23437280/11683) to force Excel to do something it is not designed to do (i.e. allow sheet functions to change sheets). You should be glad it doesn't [just crash](https://stackoverflow.com/q/52698777/11683). You are not supposed to change sheets from functions called from sheets. – GSerg Jun 24 '21 at 11:07
  • @GSerg Is there any better way to perform the changes? – PaladiN Jun 24 '21 at 11:30
  • Remove your `ColorRowIF` from the sheet. Do the colouring from `CheckBox1_Change`. – GSerg Jun 24 '21 at 12:09
  • The way I would implement this is to have a column which is monitored for selection changes (using `Private Sub Worksheet_SelectionChange(ByVal Target As Range)` and `Target = IIf(Target = "a", "", "a") 'Switches cell value "a" <=> ""; "a" is a tick in Marlett`) and when selected alters the cell contents between empty and 'a'. Conditional formatting can then be used to colour the rows when there is an 'a' present in the cell. I can generate some code as an answer if this method is of interest to you. – Tragamor Jun 24 '21 at 15:23

0 Answers0