vendredi 25 août 2017

What type has a spreadsheet checkbox in vba?

I have the following code which is supposed to add a checkbox for every row in a listobject next to it. I want to develop an approval tool, which loads data from a data base and loads it into the listobject. After that i can approve or disapprove the data with the checkbox and save the changes. Since the listobject will have changeing length the checkboxes will need to be added and removed by code.

so here the code:

Sub Approval()
Dim i As Integer
Dim tbl As ListObject
Dim CBcollection As Collection
Dim CB As msforms.CheckBox ' THIS IS WRONG
Dim sht As Worksheet
Dim L As Double, T As Double, H As Double, W As Double
Dim rng As Range

Set sht = Tabelle1
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
      Set CB = sht.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, DisplayAsIcon:=False, Left:=L, Top:=T, Width:=W, Height:=H) 
'the line before will give me an error 13 type missmatch
      CBcollection.Add (CB)
      Set rng = rng.Offset(-1, 0)
      T = rng.Top + rng.Height / 2 - H / 2
Next i

End Sub

Now the question:

What type has Checkbox in a normal spreadsheet?

I always use "Option Explicit" and I will always dim my variables to the right type and I do not want to use variant types.




Aucun commentaire:

Enregistrer un commentaire