Trigger Sub when ANY checkbox is clicked

[figment222]

Hello, I have a macro that will duplicate the value cells C3:C10 to the cell in column B directly next to it. So, if C4=Bacon, then it will automatically put "Bacon" into B4. Awesome... now, I need to find a way to have this be triggered by ANY checkbox on the sheet being checked. I can link the checkbox to C4, but B4 won't change until I select something else. I need it to change when I click the checkbox.

If anyone sees this and recommends the obvious answer to use the CheckBox1_Click() method, then please know that I need to trigger the sub when ANY checkbox is clicked. Not having much luck finding anything, though...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim i As Long


    For i = 3 To 11
        Range("B" & i).Value = Range("c" & i)
    Next i

I've seen a few articles talking about making a class module, but I can't get it to work. I'm new to VBA, so please explain it like you would to an 8-yr old.

 

[RoryA]

How did you create the checkboxes - using ActiveX or Form controls?

 

[My Aswer Is This]

You need to use this script:
This script requires no check boxs

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C:C")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Target.Offset(0, -1).Value = Target.Value
End If
End Sub

 

[figment222]

How did you create the checkboxes - using ActiveX or Form controls?

I am using ActiveX Checkboxes. Good question.

 

[figment222]

You need to use this script:
This script requires no check boxs

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C:C")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Target.Offset(0, -1).Value = Target.Value
End If
End Sub

Thank you for your response. I pasted your code into the VBA edit window for the sheet and that is the only code I have in there, but nothing seems to happen when I click the checkbox, nor when I manually change a value in the sheet. I have linked checkbox1 to cell D10. I have a formula in C10: "=D10", so that when the checkbox changes D10 to True, C10 also changes to true. What I need is for when I check that box, I want B10 to also say true, without having to have a formula in there. Might seem crazy, but once I can get this behavior going, then I can apply a modified version of this to my actual sheet and have cells in one column equal the cells in another column without a formula, anytime ANY checkbox is clicked.

 

[My Aswer Is This]

With this script any time you manually change any value in Column "C" the value you entered in column "C" will now be entered into column "B"

For example:
If you manually enter "Cake" into any cell in column "C" then "Cake" will be entered into Column "B" on the same row.

You need no formula in the cell and you need no Checkbox

 

[figment222]

With this script any time you manually change any value in Column "C" the value you entered in column "C" will now be entered into column "B"

For example:
If you manually enter "Cake" into any cell in column "C" then "Cake" will be entered into Column "B" on the same row.

You need no formula in the cell and you need no Checkbox

Thank you for the clarification. Your code indeed works the way you describe. when I manually type "cake" into C9, B9 says: "cake". Wonderful. However, when I link a checkbox to C9 and click the box, C9 will say True, but B9 continues to say: "Cake". I know I can write a code that triggers by "Checkbox1_Click()", but I need it to trigger whenever ANY checkbox is clicked. do you know of a way to do that?

 

[Fluff]

Why not just put a formula in col B
=C3

 

[My Aswer Is This]

Ok. So you do not want a sheet chage event script.

So lets start over and you tell us what your wanting to do.

I understand this much:

You want to click any Checkbox on your sheet and have it do something.
Tell us what you want to happen when you click a checkbox.
And you should know all a checkbox can do is change its value from True or False
Now you can say if checkbox1.value=True then do this. If checkbox1.value=False do this.

You could use a script like this if this is what you want.

It looks at the active cell. And if checkbox is true active cell value will also be put in 1 cell to right of active cell. If checkbox is false that action will be revered.

But how to have all checkboxes on your sheet to perform this same action. Without putting this code in all checkboxes is not something I know how to do.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
ActiveCell.Offset(, 1).Value = ActiveCell.Value
Else
ActiveCell.Offset(, 1).Value = ""
End If
End Sub

Thank you for the clarification. Your code indeed works the way you describe. when I manually type "cake" into C9, B9 says: "cake". Wonderful. However, when I link a checkbox to C9 and click the box, C9 will say True, but B9 continues to say: "Cake". I know I can write a code that triggers by "Checkbox1_Click()", but I need it to trigger whenever ANY checkbox is clicked. do you know of a way to do that?

 

[figment222]

In your example, you used the click event for a specific checkbox, but I need an event triggered by the clicking of ANY checkboxes. I think I need a class module to define all checkboxes on the sheet as a group and run a sub on a click event whenever one of them is checked. Fortunately, I did find the answer I needed from a good chap on another site, who was kind enough to modify some code he wrote for a similar situation with command buttons. I'd post the link to that page, but I'm not sure that's allowed. If anybody's interested, i'd be happy to post the code at least that finally allows me to see column B match column C when ANY checkbox is clicked!