PDA

View Full Version : Check Box Click()



amhathaw
04-27-2009, 11:00 AM
Hey guys I'm trying to set up an excell spreadsheet that has multiple check boxes on it. I am able to get most of them to do what I would like but I am having trouble with a column of check boxes designated "All" meaning that when you tick this box all the boxes in the row will be checked and clicked as well. I am able to tick it in the code but I can't seem to get it to call the boxes Click() method or w/e similiar method. I appreciate all help, thanks!

Aaron-

Bob Phillips
04-27-2009, 12:28 PM
What does your code look like?

amhathaw
04-27-2009, 01:04 PM
Well so far I have:
Dim ThisCBX As CheckBox
Set ThisCBX = ActiveSheet.CheckBoxes(Application.Caller)
If ThisCBX.Value = Checked Then
ActiveSheet.CheckBoxes(1).Value = True
Else
ActiveSheet.CheckBoxes(1).Value = False
End If

Like I said that checks the first check box and then I tried to add a line like ActiveSheet.CheckBoxes(1).Click() to the end so that it will call the click routine of the respective check box.
It's been a very long time since I've used VB so I simply can't remember what the API is to do this.

Bob Phillips
04-27-2009, 03:41 PM
Surely, setting the value of the checkbox via code will trigger its associated event code?

amhathaw
04-28-2009, 12:55 PM
That's what I thought as well, but in other languages I've used it is necessary to call the method yourself. Anyone else have an ideas?

mdmackillop
04-28-2009, 01:23 PM
Can you post a workbook to show the layout and the "Master" checkbox.

mdmackillop
04-28-2009, 01:59 PM
Sub CheckBox1338_Click()
Dim ThisCBX As CheckBox
Dim cb, val, rw
val = ActiveSheet.CheckBoxes(Application.Caller)
rw = ActiveSheet.CheckBoxes(Application.Caller).TopLeftCell.Row
For Each cb In ActiveSheet.CheckBoxes
If cb.TopLeftCell.Row = rw Then
cb.Value = val
End If
Next
End Sub

amhathaw
04-28-2009, 02:09 PM
Cool well that checks the row alright but it still doesn't seem to be actually performing each of the check boxes click() method. Do I need to be using some other kind of method rather than CheckBox_Click()?

mdmackillop
04-28-2009, 03:05 PM
This would be simpler if the checkboxes were regularly numbered. I can't find the solution that will identify each in the row.

amhathaw
04-28-2009, 03:17 PM
Well then for now let's just assume we're working with the top left check box. If the top right check box ("all") is checked then the top left check box should be checked and all actions performed. I'm really just trying to understand how to do it for a single check box and then I can figure out how to apply it to the whole row.

mdmackillop
04-28-2009, 03:21 PM
If you have the name then

Application.Run "CheckBoxXX" & "_Click"

amhathaw
04-28-2009, 03:42 PM
Cool thanks. Is there possibly a way to call the macro assigned to a check box rather than the macro itself? I think that's what is happening with Application.Run "CheckBoxXX" & "_Click" right? Otherwise I can change things around to make it work.

mdmackillop
04-28-2009, 03:52 PM
Is there possibly a way to call the macro assigned to a check box rather than the macro itself?
Is that not the same thing? With the checkbox you have used, I don't think there is another method. ActiveX controls work differently, but a lot of work to change to these. They have different Event routines that don't need the Click to run.

amhathaw
04-30-2009, 12:37 PM
I'm sorry that was confusing. What I meant was is there a way to call the macro linked to a check box rather than explicity calling the macro itself. For example, CheckBox1.Macro.Run or w/e the case might be.

You said that there are other Events that check boxes have. How do I find out what these are? I can't seem to find a library inside of excel or online so is there some kind of website you know of that has a detailed list of said events? That would be awesome for future use as well!

mdmackillop
04-30-2009, 01:12 PM
I'm posting a stripped down version of your workbook for anyone else with ideas. My code for CB1338 will change the values of the other boxes in the same row, but does not trigger the events assigned to these checkboxes. How do we do that?

mdmackillop
04-30-2009, 01:41 PM
As simple solution I missed before; too tired I guess. You'll need to add the checkbox names and relevant ranges to the Select Case statement. Check these carefully, they are not uniform.

Option Compare Text
Sub CheckBox1338_Click()
Dim ThisCBX As CheckBox
Dim cb, val As Long, rw As Long
val = ActiveSheet.CheckBoxes(Application.Caller)
rw = ActiveSheet.CheckBoxes(Application.Caller).TopLeftCell.Row
For Each cb In ActiveSheet.CheckBoxes
If cb.TopLeftCell.Row = rw Then
cb.Value = val
CBAction cb.Name, val
End If
Next
End Sub

Sub CBAction(cb As String, val As Long)
Dim Rng As Range
Select Case cb
Case "Checkbox1"
Set Rng = Range("T26:V26")
Case "Checkbox36"
Set Rng = Range("Z26:AB26")
End Select

With Rng.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
If val = 1 Then
.Color = 16777215
Else
.Color = 16776960
End If
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub