Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to execute a macro assigned to a checkbox in vba
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to execute a macro assigned to a checkbox in vba
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to execute a macro assigned to a checkbox in vba
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to execute a macro assigned to a checkbox in vba
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use Macro To Change Which Macro Assigned To Command Button | Excel Discussion (Misc queries) | |||
Macro assigned to a button | Excel Discussion (Misc queries) | |||
How do I know what macro is assigned to a button? | Excel Discussion (Misc queries) | |||
Which Macro Is Assigned to a Button | Excel Programming | |||
Buttons assigned to a macro | Excel Programming |