Getting the Cell Address of Checkbox when ticked

Question

Hy, I am working on Excel VBA which will get me the Cell Address of the Checkbox I tick at the moment. I am able to get the cell address using following code.

Sub test()

    Dim ws As Worksheet
    Set ws = Sheets("Sheet 1")
    Dim chk As CheckBox

For Each chk In ws.CheckBoxes
          Debug.Print chk.TopLeftCell.Offset(0, 0).Address
Next chk

End Sub

This code returns the Cell address of all Check-boxes but I only want one cell address of the checkbox I tick. How can I achieve this.
Currently I am trying events to achieve this but have no success so far.

I think this would probably be solved by enabling click event on check-boxes but I can't find how can I enable click event on all check-boxes — abdulsamad, Jan 14 '21 at 18:20
@Warcupine How can I add **_change** event to all check-boxes as I don't have names for all check-boxes and number of check-boxes may change. — abdulsamad, Jan 14 '21 at 18:21
It looks that your check boxes are of the type sheet Form. Is it correct? — FaneDuru, Jan 14 '21 at 18:34
I'm not sure what type of checkbox you have or how they are created but you can set a command with ```.onaction``` — Warcupine, Jan 14 '21 at 18:34
Yes, I have sheet form check boxes. I created them from Developer -> Insert -> Form Control -> Check box — abdulsamad, Jan 14 '21 at 18:42
Assign the same macro to all the checkboxes, then within that macro you can use `Application.Caller` to get the name of the clicked-on checkbox. From there you can use something like `ActiveSheet.Shapes(checkBoxName).TopLeftCell` Note that this relies on your checkboxes being positioned accurately and not overlapping any other cells. — Tim Williams, Jan 14 '21 at 18:44
@TimWilliams ok I find this solution interesting, let me study Application.caller and try it. But it has one fault that I have to assign the macro to all check-boxes. We may also have new check-boxes in future, Is there a workaround using click event on all check-boxes. — abdulsamad, Jan 14 '21 at 18:49
Check my answer code, please. It will assign a single macro to all your existing check boxes. — FaneDuru, Jan 14 '21 at 18:51

FaneDuru · Accepted Answer · 2021-01-14T19:35:01.143

3

Use the next way, please:

Copy the next Sub in a standard module:

Sub GetChkBoxAddress()
  MsgBox ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address
End Sub

Copy the next code, too, and run it:

Sub textAssignMacroChkBox()
  Dim sh As Worksheet, s As Shape, chkB As CheckBox
  
  Set sh = ActiveSheet
  For Each s In sh.Shapes
     If TypeName(s.OLEFormat.Object) = "CheckBox" Then
        s.OnAction = "GetChkBoxAddress"
     End If
  Next
End Sub

Try checking and unchecking the check boxes...

If you need to return the cell address only when the check box is checked (not for unchecking) the code can be adapted...

Edited:

In order to retrieve the cell address only for checking, you can use the next adapted Sub:

Sub GetChkBoxAddress()
 If ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Value = 1 Then
    MsgBox ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address
 End If
End Sub

edited Jan 14 '21 at 19:35

answered Jan 14 '21 at 18:50

FaneDuru

38,298
4
19
27

It is a great solution but not perfect for current situation, What if I had to add new check boxes in future ? – abdulsamad Jan 14 '21 at 18:56
@abdulsamad: You will simple run `textAssignMacroChkBox` again and it will assign the same macro to all existing ones... Isn't it convenient? – FaneDuru Jan 14 '21 at 18:58
yeah some what I will add it in Workbook_Open event so that it automatically adds macro to all check-boxes. But perfect solution would something like using **checkbox click event which runs the same code**. – abdulsamad Jan 14 '21 at 19:02
@abdulsamad: I am afraid you did not understand anything from my code. **This is what it does**! It assigns **the same macro, able to identify the clicked check box** for all existing check boxes in the sheet... You can use the Workbook_Open event, but you must run the code **if you add new check boxes**, as you said. – FaneDuru Jan 14 '21 at 19:06
I have understood everything from your code, but I didn't wanted to run the same code over and over again each time I create new check-box that's why I was trying to find the perfect solution of using click event. – abdulsamad Jan 14 '21 at 19:16
I think I can go with your code for now and thanks for it :) – abdulsamad Jan 14 '21 at 19:19
@abdulsamad: Then, do not run it! Pray to God or Alag to make a miracle... How do you imagine that such a check box will be born with such a macro allocation by itself? – FaneDuru Jan 14 '21 at 19:21
@abdulsamad: Should I understand that my code did not answer your question? If so, can you clearly specify which are your expectations? I mean, how would you like to have this macro allocation to the new added check boxes? – FaneDuru Jan 14 '21 at 19:22
No I didn't mean that. Your solution is very good and would do my work for now. I was only emphasising a minor problem it could have in future while adding new check-boxes. – abdulsamad Jan 14 '21 at 19:32
@abdulsamad: If you do not like the automate assignation, you can simple execute a right click on the newly created check box, choose 'Assing Macro...' and assign the macro `GetChkBoxAddress`... Does this solution look being more convenient for you? – FaneDuru Jan 14 '21 at 19:34
@abdulsamad: When a a piece of code, or a solution answers our question, we here tick the code left side check box, in order to make it **Accepted answer**. In this way, somebody else searching for a similar issue, will know that the code works. Our purpose here is not to write macros for somebody needed it. We should make people learn as much as possible... – FaneDuru Jan 14 '21 at 19:41

Getting the Cell Address of Checkbox when ticked

1 Answers1