2

I want to apply the following click event for all ActiveX checkboxes on my sheet. It's really simple - it turns the background color yellow if checked and white if unchecked:

Private Sub CheckBox1_Click()
    If CheckBox1.Value = True Then
        CheckBox1.BackColor = vbYellow
    Else
        CheckBox1.BackColor = vbWhite
    End If
End Sub

How can I apply this click event to all checkboxes now and for future checkboxes that I create? I googled it and some people suggested classmodules and Private WithEvents, but I can't seem to figure it out!

Edit: I want to apply this 1 click event for the specific box being clicked, not turn all boxes yellow! I dont want to write 50 CheckBoxn_Click subs!

mikecoolchin
  • 31
  • 3
  • Do you want a single click on one of the boxes to change the color of **all** the boxes or only the **box being clicked** – Gary's Student Feb 01 '18 at 19:18
  • only the box being clicked! – mikecoolchin Feb 01 '18 at 19:33
  • what's wrong with your code? If you want to apply to all your checkboxes then just change the "CheckBox1" to whatever your checkbox name will be. – pokemon_Man Feb 01 '18 at 19:42
  • Class based event handlers doesn't work for sheet controls. Unlike forms, there's no blocking here. Here is a convoluted solution for the same..... https://stackoverflow.com/questions/38424881/excel-vba-how-to-link-a-class-and-a-control/38426574#38426574 – cyboashu Feb 01 '18 at 20:02
  • My code is fine, I just dont want to have to write the same thing for all the checkboxes I have. So if I use sheet controls, am I screwed? I'll have to manually write 50 subs? – mikecoolchin Feb 01 '18 at 21:14

0 Answers0