CODEWITHSUNDEEP
vbaexcel
Xander
Xander

Reputation: 97

Make a cell active when checkbox is checked

I have a checkbox sitting in a cell in an Excel spreadsheet. I'm writing a macro that places text in certain cells using the Activecell.Offset function, like so:

Activecell.Offset(3,1).Value = "Needed"

However, to make this work as desired, I need to have the cell which contains the checkbox be made the Active cell when the box is checked. How do I do this?

Upvotes: 1

Views: 2796

Answers (1)

kaza
kaza

Reputation: 2327

This works for Form Control CheckBox Object
Make sure that your index of checkbox and the index in ActiveSheet.CheckBoxes(1) are the same...

Sub Checkbox1_Click()
 Dim chkBox As CheckBox
 Set chkBox = ActiveSheet.CheckBoxes(1)
 Range(chkBox.LinkedCell).Offset(3, 1).Value="Needed"
End Sub

You can also do the fill-with-Needed based on the value in the LinkedCell.

If your check box is an ActiveX Control. You can do the similar action method on it.

EDITED to show more detail

The workflow for Checkbox form control would be as below:-
1. RightClick the check box-> Format Control
enter image description here
2. Cell Link, this would typically be the cell behind the check-box, you want anchored(you can set the font color to white so that it doesn't show the values)
enter image description here
3. RightClick the check box-> Assign Macro
enter image description here
4. Click "New"
enter image description here
5. It show create the below snippet for you

Sub CheckBox2_Click()

End Sub

6. Fill in the Sub with the code I had shown you before..

Sub Checkbox2_Click()
 Dim chkBox As CheckBox
 Set chkBox = ActiveSheet.CheckBoxes(2)
 Range(chkBox.LinkedCell).Offset(3, 1).Value="Needed"
End Sub

Upvotes: 1

Related Questions