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
>
|
Similar Threads
