Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Checkboxes trigger the Worksheet_Change code?
I have developed a spreadsheet with 78 checkboxes on it. I have linked each
checkbox to the cell underneath it. For ease of use for the end-user, I have a Worksheet_Change code, and the keyboard user can simply enter 1/0, T/F, True/False or a space to change the checkbox. The Worksheet_Change code looks at the entry and adjusts the checkbox accordingly. Of course, these checkboxes also have an effect on other parts of the spreadsheet. Everything works fine for the keyboard user. For the mouse-user, though, clicking the checkbox changes the linked-cell entry, but does not trigger the Worksheet_Change event. Can I assign the same macro to each checkbox, which will, in turn, edit the linked cell of the box which was just checked? If so, my question is -- how do I determine the checkbox number which was just checked, without writing code for each checkbox? The cell edit (cell.value = cell.value) would then trigger the Worksheet_Change code, which I know is working correctly. -- TIA, Brad E. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Checkboxes trigger the Worksheet_Change code?
If you used checkboxes from the Forms toolbar, then you can assign a common
macro to each of the checkboxes. If you used checkboxes from the control toolbox toolbar, then you can't. You could create a common procedure that each of the _click subroutines calls, though. But I think I would drop the linked cells completely. This code goes into a General module. The first routine populates A2:A79 with checkboxes from the Forms toolbar and names the checkboxes based on its location. The second routine is the macro that is assigned to each of the checkboxes. Option Explicit Sub RunOnce() Dim CBX As CheckBox Dim myRange As Range Dim wks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim WhatCol As String Set wks = ActiveSheet FirstRow = 2 LastRow = 79 WhatCol = "A" With wks 'remove any existing checkboxes .CheckBoxes.Delete For iRow = FirstRow To LastRow With .Cells(iRow, WhatCol) Set CBX = .Parent.CheckBoxes.Add _ (Top:=.Top, _ Left:=.Left, _ Height:=.Height, _ Width:=.Width) .NumberFormat = ";;;" End With With CBX .Name = "CBX_" & .TopLeftCell.Address(0, 0) .Caption = "" .OnAction = "'" & ThisWorkbook.Name & "'!CbxClick" End With Next iRow End With End Sub Sub CBXClick() Dim CBX As CheckBox Set CBX = ActiveSheet.CheckBoxes(Application.Caller) Application.EnableEvents = False ActiveSheet.Range(Mid(CBX.Name, 5)).Value = CBool(CBX.Value = xlOn) Application.EnableEvents = True End Sub ======== This code goes behind the worksheet with the checkboxes: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRngToInspect As Range Dim myIntersect As Range Dim myCell As Range Dim CBX As CheckBox Set myRngToInspect = Me.Range("a2:A79") Set myIntersect = Intersect(Target, myRngToInspect) If myIntersect Is Nothing Then Exit Sub End If For Each myCell In myIntersect.Cells With myCell Set CBX = Nothing On Error Resume Next Set CBX = Me.CheckBoxes("CBX_" & .Address(0, 0)) On Error GoTo 0 If CBX Is Nothing Then MsgBox "Error in design with: " & .Address Exit Sub 'get it fixed right away! End If Application.EnableEvents = False Select Case LCase(.Value) Case Is = " ", lcase("t"), lcase("true") CBX.Value = xlOn .Value = True Case Else CBX.Value = xlOff .Value = False End Select Application.EnableEvents = True End With Next myCell End Sub Brad E. wrote: I have developed a spreadsheet with 78 checkboxes on it. I have linked each checkbox to the cell underneath it. For ease of use for the end-user, I have a Worksheet_Change code, and the keyboard user can simply enter 1/0, T/F, True/False or a space to change the checkbox. The Worksheet_Change code looks at the entry and adjusts the checkbox accordingly. Of course, these checkboxes also have an effect on other parts of the spreadsheet. Everything works fine for the keyboard user. For the mouse-user, though, clicking the checkbox changes the linked-cell entry, but does not trigger the Worksheet_Change event. Can I assign the same macro to each checkbox, which will, in turn, edit the linked cell of the box which was just checked? If so, my question is -- how do I determine the checkbox number which was just checked, without writing code for each checkbox? The cell edit (cell.value = cell.value) would then trigger the Worksheet_Change code, which I know is working correctly. -- TIA, Brad E. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Checkboxes trigger the Worksheet_Change code?
You can call change event code. If you are calling the procedure from outside
of the sheet then you need to change the declaraion of the event code to public... Sub test() Call Worksheet_Change(Range("A1")) End Sub Public Sub Worksheet_Change(ByVal Target As Range) MsgBox Target.Value End Sub -- HTH... Jim Thomlinson "Dave Peterson" wrote: If you used checkboxes from the Forms toolbar, then you can assign a common macro to each of the checkboxes. If you used checkboxes from the control toolbox toolbar, then you can't. You could create a common procedure that each of the _click subroutines calls, though. But I think I would drop the linked cells completely. This code goes into a General module. The first routine populates A2:A79 with checkboxes from the Forms toolbar and names the checkboxes based on its location. The second routine is the macro that is assigned to each of the checkboxes. Option Explicit Sub RunOnce() Dim CBX As CheckBox Dim myRange As Range Dim wks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim WhatCol As String Set wks = ActiveSheet FirstRow = 2 LastRow = 79 WhatCol = "A" With wks 'remove any existing checkboxes .CheckBoxes.Delete For iRow = FirstRow To LastRow With .Cells(iRow, WhatCol) Set CBX = .Parent.CheckBoxes.Add _ (Top:=.Top, _ Left:=.Left, _ Height:=.Height, _ Width:=.Width) .NumberFormat = ";;;" End With With CBX .Name = "CBX_" & .TopLeftCell.Address(0, 0) .Caption = "" .OnAction = "'" & ThisWorkbook.Name & "'!CbxClick" End With Next iRow End With End Sub Sub CBXClick() Dim CBX As CheckBox Set CBX = ActiveSheet.CheckBoxes(Application.Caller) Application.EnableEvents = False ActiveSheet.Range(Mid(CBX.Name, 5)).Value = CBool(CBX.Value = xlOn) Application.EnableEvents = True End Sub ======== This code goes behind the worksheet with the checkboxes: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRngToInspect As Range Dim myIntersect As Range Dim myCell As Range Dim CBX As CheckBox Set myRngToInspect = Me.Range("a2:A79") Set myIntersect = Intersect(Target, myRngToInspect) If myIntersect Is Nothing Then Exit Sub End If For Each myCell In myIntersect.Cells With myCell Set CBX = Nothing On Error Resume Next Set CBX = Me.CheckBoxes("CBX_" & .Address(0, 0)) On Error GoTo 0 If CBX Is Nothing Then MsgBox "Error in design with: " & .Address Exit Sub 'get it fixed right away! End If Application.EnableEvents = False Select Case LCase(.Value) Case Is = " ", lcase("t"), lcase("true") CBX.Value = xlOn .Value = True Case Else CBX.Value = xlOff .Value = False End Select Application.EnableEvents = True End With Next myCell End Sub Brad E. wrote: I have developed a spreadsheet with 78 checkboxes on it. I have linked each checkbox to the cell underneath it. For ease of use for the end-user, I have a Worksheet_Change code, and the keyboard user can simply enter 1/0, T/F, True/False or a space to change the checkbox. The Worksheet_Change code looks at the entry and adjusts the checkbox accordingly. Of course, these checkboxes also have an effect on other parts of the spreadsheet. Everything works fine for the keyboard user. For the mouse-user, though, clicking the checkbox changes the linked-cell entry, but does not trigger the Worksheet_Change event. Can I assign the same macro to each checkbox, which will, in turn, edit the linked cell of the box which was just checked? If so, my question is -- how do I determine the checkbox number which was just checked, without writing code for each checkbox? The cell edit (cell.value = cell.value) would then trigger the Worksheet_Change code, which I know is working correctly. -- TIA, Brad E. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can Checkboxes trigger the Worksheet_Change code?
Thanks, a lot, Dave. Everything works great.
-- Brad E. "Dave Peterson" wrote: If you used checkboxes from the Forms toolbar, then you can assign a common macro to each of the checkboxes. If you used checkboxes from the control toolbox toolbar, then you can't. You could create a common procedure that each of the _click subroutines calls, though. But I think I would drop the linked cells completely. This code goes into a General module. The first routine populates A2:A79 with checkboxes from the Forms toolbar and names the checkboxes based on its location. The second routine is the macro that is assigned to each of the checkboxes. Option Explicit Sub RunOnce() Dim CBX As CheckBox Dim myRange As Range Dim wks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim WhatCol As String Set wks = ActiveSheet FirstRow = 2 LastRow = 79 WhatCol = "A" With wks 'remove any existing checkboxes .CheckBoxes.Delete For iRow = FirstRow To LastRow With .Cells(iRow, WhatCol) Set CBX = .Parent.CheckBoxes.Add _ (Top:=.Top, _ Left:=.Left, _ Height:=.Height, _ Width:=.Width) .NumberFormat = ";;;" End With With CBX .Name = "CBX_" & .TopLeftCell.Address(0, 0) .Caption = "" .OnAction = "'" & ThisWorkbook.Name & "'!CbxClick" End With Next iRow End With End Sub Sub CBXClick() Dim CBX As CheckBox Set CBX = ActiveSheet.CheckBoxes(Application.Caller) Application.EnableEvents = False ActiveSheet.Range(Mid(CBX.Name, 5)).Value = CBool(CBX.Value = xlOn) Application.EnableEvents = True End Sub ======== This code goes behind the worksheet with the checkboxes: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRngToInspect As Range Dim myIntersect As Range Dim myCell As Range Dim CBX As CheckBox Set myRngToInspect = Me.Range("a2:A79") Set myIntersect = Intersect(Target, myRngToInspect) If myIntersect Is Nothing Then Exit Sub End If For Each myCell In myIntersect.Cells With myCell Set CBX = Nothing On Error Resume Next Set CBX = Me.CheckBoxes("CBX_" & .Address(0, 0)) On Error GoTo 0 If CBX Is Nothing Then MsgBox "Error in design with: " & .Address Exit Sub 'get it fixed right away! End If Application.EnableEvents = False Select Case LCase(.Value) Case Is = " ", lcase("t"), lcase("true") CBX.Value = xlOn .Value = True Case Else CBX.Value = xlOff .Value = False End Select Application.EnableEvents = True End With Next myCell End Sub Brad E. wrote: I have developed a spreadsheet with 78 checkboxes on it. I have linked each checkbox to the cell underneath it. For ease of use for the end-user, I have a Worksheet_Change code, and the keyboard user can simply enter 1/0, T/F, True/False or a space to change the checkbox. The Worksheet_Change code looks at the entry and adjusts the checkbox accordingly. Of course, these checkboxes also have an effect on other parts of the spreadsheet. Everything works fine for the keyboard user. For the mouse-user, though, clicking the checkbox changes the linked-cell entry, but does not trigger the Worksheet_Change event. Can I assign the same macro to each checkbox, which will, in turn, edit the linked cell of the box which was just checked? If so, my question is -- how do I determine the checkbox number which was just checked, without writing code for each checkbox? The cell edit (cell.value = cell.value) would then trigger the Worksheet_Change code, which I know is working correctly. -- TIA, Brad E. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RAND() Won't Trigger Worksheet_Change | Excel Programming | |||
Run VBA code only worksheet change, but don't trigger worksheet_change event based on what the code does | Excel Programming | |||
Trigger Macro by Worksheet_Change | Excel Discussion (Misc queries) | |||
Worksheet_Change wont Trigger | Excel Programming | |||
Worksheet_Change sub does not trigger a called macro | Excel Programming |