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:
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
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:
Code:
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