Assign Macro to Checkbox using VBA

[Lehnara]

Hi all,

I am new to VBA and trying to make my Excel Workbook create checkboxes and then have each checkbox add a certain series to a chart if checked, or remove the series if unchecked.

Here is the part of the code, that adds the checkboxes:

Sub CreateMainPage()

ProjectRow = 5
ProjectCol = 3
sumCol = 2
CBLeft = 10
CBTop = 72
CBWidth = 150
CBHeight = 8
CellLinkCol = 1
NameCol = 4
RKSchwelle = Sheets("Auswertung").Cells(1, 7).Value
Dim CB As Object

'...a bit of simple code

Do While Cells(ProjectRow, ProjectCol) <> ""
        
' evaluate travel cost sums to include only those over <...> Euro
    If Evaluate(Sheets("Projekte_RK").Cells(ProjectRow, sumCol).Formula) > RKSchwelle Then
        CurrentRange = Sheets("Projekte_RK").Range(Cells(ProjectRow, valuesstart), Cells(ProjectRow, valuesend)).Address
    
        Sheets("Auswertung").ChartObjects("Chart 2").Activate

    ' create checkbox linked to the row with the corresponding RK-data
                
        Set CB = ActiveSheet.CheckBoxes.Add(CBLeft, CBTop, CBWidth, CBHeight)
        With CB
            .Value = xlOn
            .Name = "Checkbox" & ProjectRow
            .LinkedCell = Cells(ProjectRow, CellLinkCol).Address
            .Display3DShading = False
        End With
        
        CB.Characters.Text = Sheets("Projekte_RK").Cells(ProjectRow, ProjectCol).Value
        
        With CB.CodeModule
            .Insertlines 2, _
                        "Private sub" & CB.Name & "_Click()" & Chr(13) & "Call AddSeries" & Chr(13) & "End sub"
        End With

        CBTop = CBTop + 13
        ProjectRow = ProjectRow + 1

Loop

' ...some more simple code

End Sub

Note: "Add Series" is supposed to be a macro to add a new series to the chart

The code is not working, because CB aka the checkbox obviously does not have a CodeModule property and I get a Run-time error '438': Object does not support this property or method.
Assigning the code to a UserForm this way works, however I need my checkboxes to be directly on the sheet, not in a UserForm.
I tried assigning the macro to the ActiveSheet instead of CB, however this throws the same error.

Is there a way around it to still assign a macro to the checkbox without venturing too deeply into the world of high-level VBA programming, where I still count as an alien?

Thanks in advance!

Cheers,
Lehnara

 

[Andrew Poulsom]

Welcome to MrExcel.

Why don't you assign an existing macro to the Checkbox, using its OnAction property? Controls from the Forms Toolbar can share the same procedure.

 

[Lehnara]

Thanks a lot Andrew, both for the welcome and the hint.

It works perfectly!
I was unaware that there is such a thing as the .OnAction property.