Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catching check/uncheck event accross multiple checkboxes
Hello,
in a sheet with a couple dozens of checkboxes I need to be able to catch the event when each checkbox gets check or unchecked; probably the worst scenario would be defining an event handler for each checkbox. I wonder if there is a way for me to catch 'a global' check / uncheck event and only after that decode which checkbox had sent that event....? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catching check/uncheck event accross multiple checkboxes
Hi Mac, I think that best way to handle this is to have a command button called Continue or Next otherwise you will never know when the user has finished making the selections. The user might just make one or two selections or many more selections. They might even change their mind and uncheck boxes that they previously checked. You can test each check box for true or false after the command button is clicked. -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catching check/uncheck event accross multiple checkboxes
I usually have a macro for each button/box and then call a common routine the handles all the buttons/boxes like this Private Sub CommandButton1_Click() call commonbutton("CommandButton1") End Sub in a module Sub Commonbutton(buttonName as string) Set but = Sheets("test").OLEObjects(buttonname).Object end sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160931 Microsoft Office Help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catching check/uncheck event accross multiple checkboxes
If these checkboxes are on a worksheet, another option would be to use the
checkboxes from the Forms toolbar (not the Control Toolbox toolbar). You could assign the same macro to each of the checkboxes. Option Explicit Sub Testme() Dim CBX As CheckBox Set CBX = ActiveSheet.CheckBoxes(Application.Caller) If CBX.Value = xlOn Then MsgBox "it's checked" Else MsgBox "it's not checked" End If 'for instance... MsgBox CBX.TopLeftCell.Address(0, 0) & vbLf & CBX.Name End Sub Mac wrote: Hello, in a sheet with a couple dozens of checkboxes I need to be able to catch the event when each checkbox gets check or unchecked; probably the worst scenario would be defining an event handler for each checkbox. I wonder if there is a way for me to catch 'a global' check / uncheck event and only after that decode which checkbox had sent that event....? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catching check/uncheck event accross multiple checkboxes
Having a button to serve as an 'orchestrator' is not a problem but I am
looking for a more elegant and promptly way of processing the clicks; the scenario I envision is goes like this: the user clicks a checkbox ( checks or unchecks it, that is not important at this moment), the checkbox broadcasts a 'they clicked me' event with additional data, my own handler consumes this event and processes its data ( what is the id of the box, was it checked or unchecked, ...) then pases it on. Is that feasible? But maybe this gets too low-level ... "OssieMac" wrote: Hi Mac, I think that best way to handle this is to have a command button called Continue or Next otherwise you will never know when the user has finished making the selections. The user might just make one or two selections or many more selections. They might even change their mind and uncheck boxes that they previously checked. You can test each check box for true or false after the command button is clicked. -- Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catching check/uncheck event accross multiple checkboxes
Joel, the last line in your code: Set but = ... Now it appears to me that
'but' is undefined .... "joel" wrote: I usually have a macro for each button/box and then call a common routine the handles all the buttons/boxes like this Private Sub CommandButton1_Click() call commonbutton("CommandButton1") End Sub in a module Sub Commonbutton(buttonName as string) Set but = Sheets("test").OLEObjects(buttonname).Object end sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160931 Microsoft Office Help . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catching check/uncheck event accross multiple checkboxes
This is it, Michael!:-) The truth is that I AM using the Forms type
checkboxes; so - if I iterate through Worksheets(x).Shapes Items and set each items's OnAction to the desired routine, that should solve it. Is that a correct way to do that? "Dave Peterson" wrote: If these checkboxes are on a worksheet, another option would be to use the checkboxes from the Forms toolbar (not the Control Toolbox toolbar). You could assign the same macro to each of the checkboxes. Option Explicit Sub Testme() Dim CBX As CheckBox Set CBX = ActiveSheet.CheckBoxes(Application.Caller) If CBX.Value = xlOn Then MsgBox "it's checked" Else MsgBox "it's not checked" End If 'for instance... MsgBox CBX.TopLeftCell.Address(0, 0) & vbLf & CBX.Name End Sub Mac wrote: Hello, in a sheet with a couple dozens of checkboxes I need to be able to catch the event when each checkbox gets check or unchecked; probably the worst scenario would be defining an event handler for each checkbox. I wonder if there is a way for me to catch 'a global' check / uncheck event and only after that decode which checkbox had sent that event....? -- Dave Peterson . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catching check/uncheck event accross multiple checkboxes
I would use the Checkboxes collection--it's less complicated.
I added some stuff that you may not need. The linkedcell is assigned to the cell that contains the checkbox. But it's formatted as ";;;". This means that the value in the cell doesn't appear in the worksheet--but is still visible in the formula bar. Using a linked cell means that you could count the number of checkboxes that are checked with something like: =countif(a1:a10,true) (Remove any of those things that you don't want--especially captions.) Option Explicit Sub testme() Dim CBX As CheckBox Dim wks As Worksheet Set wks = Worksheets("Sheet1") For Each CBX In wks.CheckBoxes With CBX .LinkedCell = .TopLeftCell.Address(external:=True) .Caption = "" .Name = "CBX_" & .TopLeftCell.Address(0, 0) .OnAction = "'" & ThisWorkbook.Name & "'!dothework" .TopLeftCell.NumberFormat = ";;;" End With Next CBX End Sub Sub DoTheWork() Dim CBX As CheckBox Set CBX = ActiveSheet.CheckBoxes(Application.Caller) If CBX.Value = xlOn Then MsgBox "it's checked" Else MsgBox "it's not checked" End If 'for instance... MsgBox CBX.TopLeftCell.Address(0, 0) & vbLf & CBX.Name End Sub ps. Here's a routine I've posted before that creates the checkboxes and assigns the macros... Option Explicit Sub testme() Dim myCBX As CheckBox Dim myCell As Range With ActiveSheet .CheckBoxes.Delete 'nice for testing For Each myCell In .Range("B3:B10").Cells With myCell Set myCBX = .Parent.CheckBoxes.Add _ (Top:=.Top, Width:=.Width, _ Left:=.Left, Height:=.Height) With myCBX .LinkedCell = myCell.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 ps. If you ever need a similar routine for optionbuttons from the Forms toolbar, visit Debra Dalgleish's site: http://contextures.com/xlForm01.html Mac wrote: This is it, Michael!:-) The truth is that I AM using the Forms type checkboxes; so - if I iterate through Worksheets(x).Shapes Items and set each items's OnAction to the desired routine, that should solve it. Is that a correct way to do that? "Dave Peterson" wrote: If these checkboxes are on a worksheet, another option would be to use the checkboxes from the Forms toolbar (not the Control Toolbox toolbar). You could assign the same macro to each of the checkboxes. Option Explicit Sub Testme() Dim CBX As CheckBox Set CBX = ActiveSheet.CheckBoxes(Application.Caller) If CBX.Value = xlOn Then MsgBox "it's checked" Else MsgBox "it's not checked" End If 'for instance... MsgBox CBX.TopLeftCell.Address(0, 0) & vbLf & CBX.Name End Sub Mac wrote: Hello, in a sheet with a couple dozens of checkboxes I need to be able to catch the event when each checkbox gets check or unchecked; probably the worst scenario would be defining an event handler for each checkbox. I wonder if there is a way for me to catch 'a global' check / uncheck event and only after that decode which checkbox had sent that event....? -- Dave Peterson . -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catching check/uncheck event accross multiple checkboxes
Ok thank you guys for great insights, now I have got all the input that I
need.:-) "Dave Peterson" wrote: I would use the Checkboxes collection--it's less complicated. I added some stuff that you may not need. The linkedcell is assigned to the cell that contains the checkbox. But it's formatted as ";;;". This means that the value in the cell doesn't appear in the worksheet--but is still visible in the formula bar. Using a linked cell means that you could count the number of checkboxes that are checked with something like: =countif(a1:a10,true) (Remove any of those things that you don't want--especially captions.) Option Explicit Sub testme() Dim CBX As CheckBox Dim wks As Worksheet Set wks = Worksheets("Sheet1") For Each CBX In wks.CheckBoxes With CBX .LinkedCell = .TopLeftCell.Address(external:=True) .Caption = "" .Name = "CBX_" & .TopLeftCell.Address(0, 0) .OnAction = "'" & ThisWorkbook.Name & "'!dothework" .TopLeftCell.NumberFormat = ";;;" End With Next CBX End Sub Sub DoTheWork() Dim CBX As CheckBox Set CBX = ActiveSheet.CheckBoxes(Application.Caller) If CBX.Value = xlOn Then MsgBox "it's checked" Else MsgBox "it's not checked" End If 'for instance... MsgBox CBX.TopLeftCell.Address(0, 0) & vbLf & CBX.Name End Sub ps. Here's a routine I've posted before that creates the checkboxes and assigns the macros... Option Explicit Sub testme() Dim myCBX As CheckBox Dim myCell As Range With ActiveSheet .CheckBoxes.Delete 'nice for testing For Each myCell In .Range("B3:B10").Cells With myCell Set myCBX = .Parent.CheckBoxes.Add _ (Top:=.Top, Width:=.Width, _ Left:=.Left, Height:=.Height) With myCBX .LinkedCell = myCell.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 ps. If you ever need a similar routine for optionbuttons from the Forms toolbar, visit Debra Dalgleish's site: http://contextures.com/xlForm01.html Mac wrote: This is it, Michael!:-) The truth is that I AM using the Forms type checkboxes; so - if I iterate through Worksheets(x).Shapes Items and set each items's OnAction to the desired routine, that should solve it. Is that a correct way to do that? "Dave Peterson" wrote: If these checkboxes are on a worksheet, another option would be to use the checkboxes from the Forms toolbar (not the Control Toolbox toolbar). You could assign the same macro to each of the checkboxes. Option Explicit Sub Testme() Dim CBX As CheckBox Set CBX = ActiveSheet.CheckBoxes(Application.Caller) If CBX.Value = xlOn Then MsgBox "it's checked" Else MsgBox "it's not checked" End If 'for instance... MsgBox CBX.TopLeftCell.Address(0, 0) & vbLf & CBX.Name End Sub Mac wrote: Hello, in a sheet with a couple dozens of checkboxes I need to be able to catch the event when each checkbox gets check or unchecked; probably the worst scenario would be defining an event handler for each checkbox. I wonder if there is a way for me to catch 'a global' check / uncheck event and only after that decode which checkbox had sent that event....? -- Dave Peterson . -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
check or uncheck a check box based on a cell value | Excel Discussion (Misc queries) | |||
uncheck checkboxes from a form | Excel Programming | |||
How do I check/uncheck ten or odd Checkboxes by click on one check | Excel Discussion (Misc queries) | |||
globally catching any error event? | Excel Programming | |||
Change event for multiple checkboxes | Excel Programming |