Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simulating Forms Checkbox click
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simulating Forms Checkbox click
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simulating Forms Checkbox click
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simulating Forms Checkbox click
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 - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simulating Forms Checkbox click
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simulating Forms Checkbox click
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 - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simulating Forms Checkbox click
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simulating Forms Checkbox click
That is the perfect solution. Thank you for taking the time to
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 - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simulating Forms Checkbox click
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Forms Checkbox | Excel Discussion (Misc queries) | |||
Click event for checkbox from Forms toolbar | Excel Discussion (Misc queries) | |||
EXCEL FORMS CHECKBOX | Excel Worksheet Functions | |||
Checkbox Click | Excel Programming | |||
simulating click events | Excel Programming |