Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event with Check Box?
I want to fire code based on a check box being checked or unchecked. The
worksheet change event doesn't seem to recognise the True/False changes when I check box is checked. My code is simple. Private Sub Worksheet_Change(ByVal Target As Range) 'If Not Application.Intersect(Range("i31:i60"), Target) Is Nothing Then If Not Intersect(Target, Range("i31:i60")) Is Nothing Then MsgBox "test" End If End Sub Where column I contains true or false as a cell value based on the checkbox. Can I get this to fire off of check boxes themselves? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event with Check Box?
Yes, use the Change event of the checkbox.
-- Regards, Tom Ogilvy "John" wrote: I want to fire code based on a check box being checked or unchecked. The worksheet change event doesn't seem to recognise the True/False changes when I check box is checked. My code is simple. Private Sub Worksheet_Change(ByVal Target As Range) 'If Not Application.Intersect(Range("i31:i60"), Target) Is Nothing Then If Not Intersect(Target, Range("i31:i60")) Is Nothing Then MsgBox "test" End If End Sub Where column I contains true or false as a cell value based on the checkbox. Can I get this to fire off of check boxes themselves? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event with Check Box?
Thanks Tom, can this be done for the entire list of checkboxes? and would
you mind giving an example of the change event for a or any check box in the sheet? "Tom Ogilvy" wrote: Yes, use the Change event of the checkbox. -- Regards, Tom Ogilvy "John" wrote: I want to fire code based on a check box being checked or unchecked. The worksheet change event doesn't seem to recognise the True/False changes when I check box is checked. My code is simple. Private Sub Worksheet_Change(ByVal Target As Range) 'If Not Application.Intersect(Range("i31:i60"), Target) Is Nothing Then If Not Intersect(Target, Range("i31:i60")) Is Nothing Then MsgBox "test" End If End Sub Where column I contains true or false as a cell value based on the checkbox. Can I get this to fire off of check boxes themselves? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event with Check Box?
Private Sub CheckBox1_Change()
msgbox "Test - CheckBox1") End Sub If you want to handle all checkboxes with a single event, you could use a technique documented by John Walkenbach: http://www.j-walk.com/ss/excel/tips/tip44.htm -- Regards, Tom Ogilvy "John" wrote: Thanks Tom, can this be done for the entire list of checkboxes? and would you mind giving an example of the change event for a or any check box in the sheet? "Tom Ogilvy" wrote: Yes, use the Change event of the checkbox. -- Regards, Tom Ogilvy "John" wrote: I want to fire code based on a check box being checked or unchecked. The worksheet change event doesn't seem to recognise the True/False changes when I check box is checked. My code is simple. Private Sub Worksheet_Change(ByVal Target As Range) 'If Not Application.Intersect(Range("i31:i60"), Target) Is Nothing Then If Not Intersect(Target, Range("i31:i60")) Is Nothing Then MsgBox "test" End If End Sub Where column I contains true or false as a cell value based on the checkbox. Can I get this to fire off of check boxes themselves? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event with Check Box?
Thanks again, I have two questions.
1. One what does this code do... here is my attempted modification 2. Where do I insert a Call to do what I want to happen when one of the 20 or so checkboxes is checked? Thanks so much... "Tom Ogilvy" wrote: Private Sub CheckBox1_Change() msgbox "Test - CheckBox1") End Sub If you want to handle all checkboxes with a single event, you could use a technique documented by John Walkenbach: http://www.j-walk.com/ss/excel/tips/tip44.htm -- Regards, Tom Ogilvy "John" wrote: Thanks Tom, can this be done for the entire list of checkboxes? and would you mind giving an example of the change event for a or any check box in the sheet? "Tom Ogilvy" wrote: Yes, use the Change event of the checkbox. -- Regards, Tom Ogilvy "John" wrote: I want to fire code based on a check box being checked or unchecked. The worksheet change event doesn't seem to recognise the True/False changes when I check box is checked. My code is simple. Private Sub Worksheet_Change(ByVal Target As Range) 'If Not Application.Intersect(Range("i31:i60"), Target) Is Nothing Then If Not Intersect(Target, Range("i31:i60")) Is Nothing Then MsgBox "test" End If End Sub Where column I contains true or false as a cell value based on the checkbox. Can I get this to fire off of check boxes themselves? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event with Check Box?
the code....
Option Explicit Dim CBX() As New Class1 Sub ShowDialog() Dim CBXCount As Integer Dim ctl As Control ' Create the Button objects CBXCount = 0 For Each ctl In ActiveSheet.Controls If TypeName(ctl) = "Checkbox" Then If ctl.Name < "OKButton" Then 'Skip the OKButton CBXCount = CBXCount + 1 ReDim Preserve CBX(1 To CBXCount) Set CBX(CBXCount).checkboxGroup = ctl End If End If Next ctl 'UserForm1.Show End Sub "Tom Ogilvy" wrote: Private Sub CheckBox1_Change() msgbox "Test - CheckBox1") End Sub If you want to handle all checkboxes with a single event, you could use a technique documented by John Walkenbach: http://www.j-walk.com/ss/excel/tips/tip44.htm -- Regards, Tom Ogilvy "John" wrote: Thanks Tom, can this be done for the entire list of checkboxes? and would you mind giving an example of the change event for a or any check box in the sheet? "Tom Ogilvy" wrote: Yes, use the Change event of the checkbox. -- Regards, Tom Ogilvy "John" wrote: I want to fire code based on a check box being checked or unchecked. The worksheet change event doesn't seem to recognise the True/False changes when I check box is checked. My code is simple. Private Sub Worksheet_Change(ByVal Target As Range) 'If Not Application.Intersect(Range("i31:i60"), Target) Is Nothing Then If Not Intersect(Target, Range("i31:i60")) Is Nothing Then MsgBox "test" End If End Sub Where column I contains true or false as a cell value based on the checkbox. Can I get this to fire off of check boxes themselves? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MsgBox in Enter event causes combobox not to run Change event | Excel Programming | |||
Paste Event Check | Excel Programming | |||
Change event and calculate event | Excel Programming | |||
Change Event.....Spell Check | Excel Programming | |||
change event/after update event?? | Excel Programming |