Use VBA to add, name, and position checkbox in Excel 2

[VBAjedi]

I'm trying to use the following code to add a checkbox at runtime to each data row in my spreadsheet. (Creating them beforehand and just hiding them is not a good option in this case). I can't figure out how to name the checkboxes as I create them, and I don't know how to set the "TopIndex" value to correspond to row "MyRow".

Dim MyRow as Integer
Dim TopIndex
Dim myBox as Object
For MyRow = 5 To LastRow
TopIndex = Rows(MyRow).SomeProperty?
Set myBox = ActiveSheet.OLEObjects.Add _
(ClassType:="Forms.CheckBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=3.75, _
Top:=TopIndex, Width:=21.75, Height:= 10.5)
myBox.Name = "SelectRow" & MyRow
Next MyRow

Thanks in advance for any help you can offer!

VBAjedi

 

[Loomah]

Good evening

After a bit of mucking about this should do the trick!
There is a problem with it in that I'm having problems deselecting the last checkbox - noted in the code.

It will adapt to differnt column widths & row heights (assuming normal row height or greater) as you want it to work at run time.

Sub ApplyChkBxs()
Dim sngRowHi As Single
Dim sngColsWide As Single
Dim sngTopPos As Single
Dim lLastRow As Long
Dim iLastCol As Integer
Dim lCount As Long
' get last row & col, the easy way!
lLastRow = [a65536].End(xlUp).Row
iLastCol = [iv5].End(xlToLeft).Column
' set actual width of data and find start TOP pos
sngColsWide = Range(Cells(1, 1), Cells(1, iLastCol)).Width
sngTopPos = Rows("1:4").Height + 2
' apply checkboxes to each row of data
For lCount = 5 To lLastRow
    sngRowHi = Rows(lCount).Height
        ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
            DisplayAsIcon:=False, Left:=sngColsWide + 5, Top:=sngTopPos, _
            Width:=11.75, Height:=sngRowHi - 2).Select
        ' Name the check boxes
        Selection.Name = "SelectRow" & lCount
    sngTopPos = sngTopPos + sngRowHi
Next
'this is meant de-select the last checkbox
'it works but sometimes the checkboxes aren't visible
'a feature?
'Range("A1").Select
End Sub

Enjoy
;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[VBAjedi]

Ok - next question. How do I interact programmatically with these new checkboxes? I don't know what the code name (ex. "CheckBox2&quot is that Excel assigned to it when I created it.

I want to be able to work with the checkbox associated with any given row (check its value, delete it, reposition it, etc). How do I address it when all I know is a row number?

Is my question clear? Any help will be greatly appreciated!

VBAjedi

 

[RobBroekhuis]

Loomah's code above explicitly named each checkbox:

'Name the check boxes
Selection.Name = "SelectRow" & lCount

if you didn't incorporate that feature into your own code, then you'll have to iterate through the objects on your sheet and find the one with the correct location. Not all that pleasant, or fast, so you're better off naming them in the first place.
Rob

 

[VBAjedi]

Rob,

Yeah, I used his approach to naming the checkboxes. Call me dumb, but I just don't know how to find out if "SelectRow5" is checked or not, or how to change its other properties! I only know how to refer to controls using their code name (which I don't know in this case, since I didn't manually create the control).

A related problem is that I couldn't find a properties list for checkboxes, and didn't have any success just guessing.

Thanks!

VBAjedi

 

[RobBroekhuis]

Try:

if activesheet.OLEObjects("SelectRow5&quot.object then
msgbox "Row 5 checked!"
end if

Rob

 

[VBAjedi]

Rob,

Just what I needed! That deserves a star.

One last question (for now!): is there a way to return the code name (ex. "Checkbox2&quot when I know the Explicit name (ex. "SelectRow5&quot?

You rock!

VBAjedi

 

[RobBroekhuis]

Jedi,
I don't think the original "code name" exists anymore, after you rename the object as per Loomah's code. For example, if you go into design mode (first button on the control toolbox) and click on the checkbox, you'll see that Excel puts "SelectRowxx" in the name area of the formula toolbar. Your new name is the official name!
Rob