0

I have a sheet where you click a button and then several ActiveX Checkboxes are created. The number depending on the values in some comboboxes but usually it's like 16-32.

If you click the button again they are deleted and new ones are created in their place. It works fine the first couples of times, all the checkboxes are deleted and new ones appear. But after pressing it like 20-30 times it kinda freezes and Excel restarts.

After the restart it works again but only once, the second time you press it Excel freezes and restarts again. Then it works again but only once, then it restarts again and so on.

If you wait a couple of minutes it works again and you can press it over and over, but after like 20-30 presses it freezes again and the whole thing repeats.

This is the code that deletes the checkboxes:

Dim s As Shape
For Each s In Sheets("Choice").Shapes
    If s.Type = 12 Then
        If Not Intersect(s.TopLeftCell, Sheets("Choice").Range("A14:T100")) Is Nothing Then
            s.Delete
        End If
    End If
Next

The code that creates them:

for i = 1 to Ubound(EmpList)
'lots of code that creates the variables offset and col 

Set rng = Choice.Cells(14 + (i * 2) - offset, col)
    cellLeft = rng.Left
    cellTop = rng.Top
    cellwidth = rng.Width
    cellheight = rng.Height
Set obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Checkbox.1", Left:=cellLeft, Top:=cellTop, Width:=cellwidth * 2, Height:=cellheight * 2)
obj.Name = "CheckBox" & i
ActiveSheet.OLEObjects("CheckBox" & i).Object.Value = False
ActiveSheet.OLEObjects("CheckBox" & i).Object.Caption = EmpList(i - 1)
Next i

Anyone knows what might be wrong?

Jens
  • 183
  • 1
  • 10
  • Why do you need to create and delete hundreds of checkboxes? Excel really is not designed for that. – Warcupine Apr 30 '20 at 16:00
  • How are you handling events for these checkboxes ? Like I mentioned in your other question, having the code already existing in the sheet module, or adding the code at runtime, is going to cause lots of problems and I wouldn't even try to make that work. If you *need* dynamic controls then the best way to implement that is using a custom class to handle their events. Eg: see some background here - http://www.siddharthrout.com/index.php/2018/01/15/vba-control-arrays/ Checkboxes: https://stackoverflow.com/questions/39468821/dynamically-add-checkboxes-and-event-handler-to-a-worksheet – Tim Williams Apr 30 '20 at 16:34
  • Basically this is a tool to see the performance of employees. You select two dates and the press a button. That creates checkboxes with the names of the employees that were working those dates. You also get checkboxes with all the different statistics you want to see. You then press another button and a chart is created showing the selected employees and their performance. If you then select different dates and press the button, the checkboxes are deleted and new ones are created (with a different set of employees) and so on. – Jens May 01 '20 at 06:07
  • What is actually happening? When I delete the checkboxes, aren't they fully deleted? Or why is this happening? – Jens May 01 '20 at 06:09
  • BTW I will look into this class thing! It's new to me...great excuse to learn something new! – Jens May 01 '20 at 06:09

0 Answers0