CODEWITHSUNDEEP
excelvba
Jamie Walker
Jamie Walker

Reputation: 213

How to change code to add one check box to ActiveCell instead of ActiveSheet?

I have a code written that has an if statement to add check boxes to certain cells in ActiveSheet if certain conditions are met. My code below shows that code.

'To add checkboxes
Dim ToRow As Long
Dim LastRowCB As Long
Dim MyLeft As Double
Dim MyTop As Double
Dim MyHeight As Double
Dim MyWidth As Double

LastRowCB = Range("B1000").End(xlUp).Row
For ToRow = 11 To LastRowCB
    If Not IsEmpty(Cells(ToRow, "B")) Then
        '-
        MyLeft = Cells(ToRow, "A").Left
        MyTop = Cells(ToRow, "A").Top
        MyHeight = Cells(ToRow, "A").Height
        MyWidth = MyHeight = Cells(ToRow, "A").Width
        '-
        ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
         With Selection
            .Caption = ""
            .Value = xlOff
            .LinkedCell = "R" & ToRow
            .Display3DShading = False
        End With
    End If
Next

I am now trying to alter this code with little success to add the exact same kind of check box to one cell I have activated. Like I said I have the cell activated I just need to alter the code to make it do just that ActiveCell instead of the ActiveSheet. Below is the attempt I made to alter the code. I excluded the If statement because my thought is since I have the cell activated I don't need all of that.

ActiveCell.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
         With Selection
            .Caption = ""
            .Value = xlOff
            .LinkedCell = "R" & ToRow
            .Display3DShading = False
        End With

When I try and run this code I get a Debug on the following line.

ActiveCell.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select

Any advice is much appreciated.

Upvotes: 1

Views: 1728

Answers (1)

NickSlash
NickSlash

Reputation: 5100

Checkboxes (and other controls) are part of the worksheet, and not a cell.

While you can put a control "over" a cell, its still not part of that cell.

Just continue to use ActiveSheet and use ActiveCell calculate your MyTop, MyLeft, MyHeight and MyWidth values.

MyLeft = ActiveCell.Left
MyTop = ActiveCell.Top
MyHeight = ActiveCell.Height
MyWidth = ActiveCell.Width

ActiveSheet.Checkboxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select

With Selection
    .Caption = ""
    .Value = xlOff
    .LinkedCell = ActiveCell.Address
    .Display3DShading = False
End With

I've updated the code to also link the cell and stuff. Your first code example in your question does show how to do it minus the ActiveCell.Address!

If you want it to be linked explicitly to a cell in column R then you will need to make it yourself

eg: .LinkedCell = "R" & ActiveCell.Row otherwise ActiveCell.Address will link to the cell your adding the checkbox over.

Upvotes: 4

Related Questions