2 questions on checkboxes

[MBjorn]

Hello,

I am quite new to VBA so excuse me if this is a simple question. My objective is this:

I want a number of checkboxes that:

1) When clicked make all the others go 'false'
2) Updates a lot of data, based on wich checkbox was clicked

Since all the checkboxes do almost the same thing, and I want to be able to make them programatically, Id like to have them calling the same macro, and then in the macro base my actions on which of the checkboxes were clicked.

is that possible?

Alternatively, is their a way to programatically write a new macro, when making a checkbox?

 

[Sektor]

Where are checkboxes located: sheet or UserForm?

 

[Andrew Poulsom]

Welcome to MrExcel.

You need Option Buttons. Here is some code for Check Boxes:

http://www.mrexcel.com/forum/showthread.php?t=109556

 

[MBjorn]

As it is right now, I just placed them on the sheet manually.

 

[Sektor]

To be able to use one event handler for several checkboxes, use following steps:

1. Create class. Call it, say, EventsTrapper.
2. In this class create public variable, say, Chk.

Class EventsTrapper.

    Public WithEvents Chk As MSForms.CheckBox

3. Create standard module (name it whateve) and create variable of EventsTrapper and Collection variable:

    Public col As Collection
    Public EvTrapper As EventsTrapper

4. In Workbook_Open event write down following (assume that checkboxes are on Sheet1):

Private Sub Workbook_Open()

    Dim ch As MSForms.CheckBox
    Dim ole As OLEObject

    Set col = New Collection
    
    For Each ole In Sheet1.OLEObjects
        If TypeOf ole.Object Is MSForms.CheckBox Then
            Set ch = ole.Object
            Set EvTrapper = New EventsTrapper
            EvTrapper.Chk = ch
            col.Add EvTrapper
            Set EvTrapper = Nothing
        End If
    Next

End Sub

5. In class module choose Chk from top drop-down menu (you'll automatically get Chk_Click event handler).

Private Sub Chk_Click()

End Sub

Here you write your code. Chk variable is a checkbox which was clicked.