ExcelBanter - View Single Post

Dave Peterson

Glad you got it working.

wrote:

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 -

--

Dave Peterson