CODEWITHSUNDEEP
excelvba
ronald1993
ronald1993

Reputation: 75

Is it possible to track the current row where a checkbox is placed and then copy this rows column C

I'm currently working on a line of code that puts checkboxes in every row on Column A (Every row but row A1, so it starts at A2) in Excel. These checkboxes are connected to their neighbour Column B where it puts its true or false data.

Now my goal is: If column B (decided by checkbox) is true, then copy the checkbox row location cell C value to the checkbox row location cell D. When this is not true it has to put a 0 in the target cell (D).

This line of vba is activated as soon as a checkbox will be clicked. My current code is:

Sub TrueFalse()
If Range("B" & ActiveRow).Value = True Then
    MsgBox "True"
ElseIf Range("B" & ActiveRow).Value = False Then
    MsgBox "False"
End If
End Sub

I know "ActiveRow" doesn't work but it is to show where the rowcheck should be.

Hope someone can help, thanks in advance!

Upvotes: 0

Views: 1887

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149335

If the Checkbox is connected via the Cell Link, i.e Format Control | Control Tab | Cell Link then you can get the row of the relevant checkbox using

Sub TrueFalse()
    Debug.Print Range(ActiveSheet.CheckBoxes(Application.Caller).LinkedCell).Row
End Sub

So your code becomes

Sub TrueFalse()
    Dim ActiveRow As Long

    ActiveRow = Range(ActiveSheet.CheckBoxes(Application.Caller).LinkedCell).Row

    If Range("B" & ActiveRow).Value = True Then
        MsgBox "True"
    ElseIf Range("B" & ActiveRow).Value = False Then
        MsgBox "False"
    End If
End Sub

Edit

You can also change the above to work like

Sub TrueFalse()
    Dim CellAddress As String

    CellAddress = ActiveSheet.CheckBoxes(Application.Caller).LinkedCell

    If Range(CellAddress).Value = True Then
        MsgBox "True"
    ElseIf Range(CellAddress).Value = False Then
        MsgBox "False"
    End If
End Sub

Upvotes: 0

Geoff
Geoff

Reputation: 53

Its not clear to me the reason you are using check boxes in this way?

You could achieve something similar using data input validation instead and avoid the hassle of using forms for the checkboxes.

E.g. allow only two values in the range of cell A2:A99 (e.g. "Y" or "N"), or possibly only allow one value and an empty cell (e.g. "X" or blank). Then use VBA to detect data entry into column "A" and use an offset from the active cell to do the work e.g.

if Activecell.value="X" Then
    Activecell.offset(0,1) = "whatever you want to write to the cell right of the checked cell"

Is this the sort of thing you are try to do?

Upvotes: 0

Related Questions