how to execute a macro assigned to a checkbox in vba

[pieterklinker]

Hi,

For testing purposes i want to control a workbook from another workbook
with vba.

When setting a checkbox to 'checked', the macro assigned to the
checkbox is not executed.

the code i use is:

Dim name As String
Dim cbo As CheckBox

name = "some value read from a cell'
Set cbo = otherWorkbook.ActiveSheet.CheckBoxes(name)
cbo.value = 1 'make checkbox 'checked'

In similar code for buttons i use: Run button.OnAction, this does not
work for a checkbox though. Also i can't get the name of the macro from
the checkbox in vba.

Does anyone know how i can get the macro assigned to a checkbox to
execute from vba? Note that i don't know the macro name in the code,
only the name of the checkbox.

thanks

Pieter

 

[Dave Peterson]

Saved from a previous post:

Here are two subroutines. The first one adds a bunch of checkboxes from the
Forms toolbar to a range in the activesheet (b3:B10).

The second one is the one that would do what you want to do when you
check/uncheck that box.

The first one only needs to be run once--to set up the checkboxes on the
worksheet.

Option Explicit
Sub testme()

Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete
For Each myCell In ActiveSheet.Range("B3:B10").Cells

With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
'.LinkedCell = myCell.Offset(0, 10).Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
.OnAction = "'" & ThisWorkbook.Name & "'!dothework"
End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

Sub DoTheWork()
Dim myCBX As CheckBox
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

If myCBX = xlOn Then
'do something
Else
'do something else
End If

End Sub

 

[pieterklinker]

Dave,

thanks for your help, i managed to achieve what i wanted to do with
your help.

for future reference:
now that i worked out how to do it i can also explain better what i
want to do

i want to execute a macro assigned to a checkbox in vba, while in the
code all i have is the name of the checkbox, my final code looks like
this:

dim myCBX as checkbox
dim checkBoxAction as String

Set myCBX = seriesWorkbook.ActiveSheet.CheckBoxes( checkBoxName)
checkBoxAction = myCBX.OnAction
If "" <> checkBoxAction Then
Run checkBoxAction
End If



before i had allready tried:
Run myCBX.OnAction
in analogy to a button control, but that doesn't work.

thanks again for your help,

Pieter

 

[Dave Peterson]

Just a question/comment...

Things like this always scare me a little:

Set myCBX = SeriesWorkbook.ActiveSheet.CheckBoxes(CheckBoxName)

How do you know for sure what the activesheet in that workbook is?

If possible, I like to specify that sheet by name:

Set myCBX _
= SeriesWorkbook.Worksheets("sheetnamehere").CheckBoxes(CheckBoxName)

 

[pieterklinker]

Dave,

you are right, and i would not use this in production code.
However, the code is called from a workbook that i use to test another
workbook. In these tests i explicitly select a particular sheet and
then 'check' a checkbox on that sheet.
I test for succesfull execution of selecting the sheet, and also i have
a check in the 'check ckeckbox' code to test whether the name provided
for the checkbox is valid.

If despite these tests i should happen do something illegal i actually
want the code to crash so i can improve the testing or tested workbook.
Especcially the testing workbook is a work in progress (besides being a
test itself).

thanks for your comments,

Pieter