Simulating Forms Checkbox click

G

gokhvat

I have a sheet with a set of Forms Checkboxes. Each checkbox is
assigned to the same subroutine. Upon clicking, the subroutine adds
different series to a chart based on the name of the checkbox clicked,
using the "Application.Caller" property.

My question is whether I can simulate clicking these checkboxes in VBA
- so that that the subroutine is launched by them and the
"Application.Caller" property remains intact.

ActiveSheet.CheckBoxes("name").value = 1 or True or xlOn will check
the box, but not fire the associated subroutine.

Alternatively, if there is a way to call a subroutine with a spoofed
Application.Caller, that might be an option as well, but I imagine
that is unlikely.

There is probably a better way to achieve the desired result than the
dependence on "Application.Caller," but these are the circumstances as
they stand.

Thank you.
 
D

Dave Peterson

Maybe something like:

Option Explicit
Sub CalledRoutine(Optional PassedCBXName As String = "")

If PassedCBXName = "" Then
PassedCBXName = Application.Caller
End If

With ActiveSheet.CheckBoxes(PassedCBXName)
MsgBox .Name & vbLf & .Value
End With

End Sub
Sub CallingRoutine()
Dim wks As Worksheet
Set wks = ActiveSheet
With wks.CheckBoxes(1)
.Value = xlOn
Call testme(PassedCBXName:=.Name)
End With
End Sub
 
D

Dave Peterson

Assign the "CalledRoutine" to one of your checkboxes and try clicking that
checkbox.

Next create a test subroutine (call it "CallingRoutine"). This callingroutine
could change the value of a checkbox and call the same routine that is assigned
to the checkbox.

I thought that this was one of the ok options:

(And it's not that unlikely, vbg.)
 
E

eugene.gokhvat

Maybe I did not do a good job explaining the situation. Multiple
checkboxes call the same routine. The routine does different things
based on the name of the checkbox that was clicked
(Application.Caller). I would like to simulate clicking several of
these checkboxes with VBA.
 
D

Dave Peterson

I don't understand the problem.

The calledroutine is the macro that's assigned to each of the checkboxes. Since
I have no idea what your code really does, I just showed the name and its state.

The CallingRoutine is just a macro that shows you that you can change the value
of a checkbox and then call the same procedure based on the name of the checkbox
that you just changed.

So what are we missing?

===
Oops. I see where I didn't change the name of the macro on the Call line in the
CallingRoutine. (I modified the pasted text above.) Maybe that's the cause of
the disconnect????
 
E

eugene.gokhvat

I have included a pared-down version of my function below. This one
function "Graph_Z" is called by six different checkboxes named cZ1m,
cZ2m, cZ3m, etc. and it uses these checkbox's names to manipulate
different series in a chart. Obviously, if you call the function on
its own, without a checkbox, it will not work. That is why I am trying
to figure out a way to simulate clicking on the checkbox with VBA or
spoofing the Application.Caller. Does that make sense?

Sub Graph_Z()

Z = (ActiveSheet.CheckBoxes(Application.Caller).value = 1)
span = Right(Application.Caller, 2)

ActiveSheet.ChartObjects("Chart 2").Activate

With ActiveChart

If Not Z Then

.SeriesCollection(Range("Z" & span).value).Delete

End If

End With

End Sub


I don't understand the problem.

The calledroutine is the macro that's assigned to each of the checkboxes.  Since
I have no idea what your code really does, I just showed the name and its state.

The CallingRoutine is just a macro that shows you that you can change the value
of a checkbox and then call the same procedure based on the name of the checkbox
that you just changed.

So what are we missing?

===
Oops.  I see where I didn't change the name of the macro on the Call line in the
CallingRoutine.  (I modified the pasted text above.)  Maybe that's thecause of
the disconnect????
Click to expand...
 
D

Dave Peterson

Sub Graph_Z(Optional PassedCBXName As String = "")

If PassedCBXName = "" Then
PassedCBXName = Application.Caller
End If

Z = (ActiveSheet.CheckBoxes(passedcbxname)).value = 1)
span = Right(passedcbxname, 2)

ActiveSheet.ChartObjects("Chart 2").Activate

With ActiveChart

If Not Z Then

.SeriesCollection(Range("Z" & span).value).Delete

End If

End With

End Sub

Untested.
 
E

eugene.gokhvat

That is the perfect solution. Thank you for taking the time to
respond.

Best,
Eugene


Sub Graph_Z(Optional PassedCBXName As String = "")

    If PassedCBXName = "" Then
        PassedCBXName = Application.Caller
    End If

    Z = (ActiveSheet.CheckBoxes(passedcbxname)).value = 1)
    span = Right(passedcbxname, 2)

    ActiveSheet.ChartObjects("Chart 2").Activate

    With ActiveChart

        If Not Z Then

            .SeriesCollection(Range("Z" & span).value).Delete

        End If

    End With

End Sub

Untested.





[email protected] said:
I have included a pared-down version of my function below. This one
function "Graph_Z" is called by six different checkboxes named cZ1m,
cZ2m, cZ3m, etc. and it uses these checkbox's names to manipulate
different series in a chart. Obviously, if you call the function on
its own, without a checkbox, it will not work. That is why I am trying
to figure out a way to simulate clicking on the checkbox with VBA or
spoofing the Application.Caller. Does that make sense?
Click to expand...
Sub Graph_Z()
Click to expand...
    Z = (ActiveSheet.CheckBoxes(Application.Caller).value = 1)
    span = Right(Application.Caller, 2)
Click to expand...
    ActiveSheet.ChartObjects("Chart 2").Activate
Click to expand...
    With ActiveChart
Click to expand...
        If Not Z Then
Click to expand...
            .SeriesCollection(Range("Z" & span).value).Delete
Click to expand...
        End If
Click to expand...
    End With
Click to expand...
Click to expand...
 
E

eugene.gokhvat

That is the perfect soltuon. Thanks for taking the time ro respond.

Best,
Eugene

Sub Graph_Z(Optional PassedCBXName As String = "")

    If PassedCBXName = "" Then
        PassedCBXName = Application.Caller
    End If

    Z = (ActiveSheet.CheckBoxes(passedcbxname)).value = 1)
    span = Right(passedcbxname, 2)

    ActiveSheet.ChartObjects("Chart 2").Activate

    With ActiveChart

        If Not Z Then

            .SeriesCollection(Range("Z" & span).value).Delete

        End If

    End With

End Sub

Untested.





[email protected] said:
I have included a pared-down version of my function below. This one
function "Graph_Z" is called by six different checkboxes named cZ1m,
cZ2m, cZ3m, etc. and it uses these checkbox's names to manipulate
different series in a chart. Obviously, if you call the function on
its own, without a checkbox, it will not work. That is why I am trying
to figure out a way to simulate clicking on the checkbox with VBA or
spoofing the Application.Caller. Does that make sense?
Click to expand...
Sub Graph_Z()
Click to expand...
    Z = (ActiveSheet.CheckBoxes(Application.Caller).value = 1)
    span = Right(Application.Caller, 2)
Click to expand...
    ActiveSheet.ChartObjects("Chart 2").Activate
Click to expand...
    With ActiveChart
Click to expand...
        If Not Z Then
Click to expand...
            .SeriesCollection(Range("Z" & span).value).Delete
Click to expand...
        End If
Click to expand...
    End With
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Form Dropdown: click event macro 3
name of checkbox that was clicked in a userform 2
How to have Checkbox A uncheck with checked Checkbox B 3
Dialogsheet Checkbox to Select All Checkboxes 0
Set value of checkbox on userform without triggering Click event 7
Alternative to Application.Caller in array functions to avoid wrong results? 11
Passing reference to a checkbox to a subroutine. 4
CheckBox values based on Worksheet condition 2

Top