Simulating Forms Checkbox click
That is the perfect soltuon. Thanks for taking the time ro respond.
Best,
Eugene
On May 19, 9:32*am, Dave Peterson wrote:
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.
wrote:
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
On May 15, 7:12 pm, Dave Peterson wrote:
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.
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
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.
Sub CallingRoutine()
* * Dim wks As Worksheet
* * Set wks = ActiveSheet
* * With wks.CheckBoxes(1)
* * * * .Value = xlOn
* * * * Call CalledRoutine(PassedCBXName:=.Name) *'<--- changed!!!
* * End With
End Sub
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????
wrote:
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.
On May 14, 5:52 pm, Dave Peterson wrote:
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:
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.
(And it's not that unlikely, vbg.)
wrote:
I'm sorry, but I don't really understand how this answers the
question.
On May 13, 4:12 pm, Dave Peterson wrote:
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
wrote:
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.
--
Dave Peterson- Hide quoted text -
- Show quoted text -
--
Dave Peterson- Hide quoted text -
- Show quoted text -
--
Dave Peterson- Hide quoted text -
- Show quoted text -
--
Dave Peterson- Hide quoted text -
- Show quoted text -
|