I have developed the following two subs which create and remove a collection of checkboxes next to a listobject. Each distinct ID in the listobject gets a checkbox. Like this I can approve the listobject entries.
The code is the follwing:
Public CBcollection As Collection
Public CTRLcollection As Collection
Sub create_chbx()
If Approval.CBcollection Is Nothing Then
Dim i As Integer
Dim tbl As ListObject
Dim CTRL As Excel.OLEObject
Dim CB As MSForms.CheckBox
Dim sht As Worksheet
Dim L As Double, T As Double, H As Double, W As Double
Dim rng As Range
Dim ID As Long, oldID As Long
Set CBcollection = New Collection
Set CTRLcollection = New Collection
Set sht = ActiveSheet
Set tbl = sht.ListObjects("ApprovalTBL")
Set rng = tbl.Range(2, 1).Offset(0, -1)
W = 10
H = 10
L = rng.Left + rng.Width / 2 - W / 2
T = rng.Top + rng.Height / 2 - H / 2
For i = 1 To tbl.ListRows.count
ID = tbl.Range(i + 1, 1).Value
If Not (ID = oldID) Then
Set CTRL = sht.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, DisplayAsIcon:=False, Left:=L, Top:=T, Width:=W, Height:=H)
Set CB = CTRL.Object
CBcollection.Add Item:=CB
CTRLcollection.Add Item:=CTRL
End If
Set rng = rng.Offset(1, 0)
T = rng.Top + rng.Height / 2 - H / 2
oldID = ID
Next i
End If
End Sub
Sub remove_chbx()
If Not Approval.CBcollection Is Nothing Then
With Approval.CBcollection ' Approval is the module name
While .count > 0
.Remove (.count)
Wend
End With
With Approval.CTRLcollection
While .count > 0
.Item(.count).Delete
.Remove (.count)
Wend
End With
Set Approval.CBcollection = Nothing
Set Approval.CTRLcollection = Nothing
End If
End Sub
This all works pretty well. No double checkboxes and no errors if there are no checkboxes. I am developing an approval scheme were I need to develop and test other modules. If I now run this sub:
Sub IdoStupidStuff()
Dim i As Integer
Dim Im As Image
i = 1
Set Im = i
End Sub
It will give me an error. If I then try to run one of my checkbox subs they will not work properly anymore. The collection is deleted by the error and I am no longer able to access the collections. Why does this happen and am I able to counter act this other then just not causing errors? Is there a better way to implement such a system were loss of collections is not an issue?
Aucun commentaire:
Enregistrer un commentaire