how to execute a macro assigned to a checkbox in vba

[[email protected]]

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

[email protected] wrote:
>
> 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

[[email protected]]

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)




[email protected] wrote:
>
> 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

[[email protected]]

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

[USAMax]

Can someone please help me, I have a checkbox that when created has the name Check Box 1. I can right click on it and choose Format Control. There I have six tabs: Colors and Lines, Size, Protection, Properties, Alt Text and Control.

I do not know if the boxes that are used are form checkboxes or Activex checkboxes and that is why I am giving this information.

I need to know how to run a macro once the check box is changed............

Please any help would be appreciated,

David

[arlu1201]

USAMax,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.