Worksheet_SelectionChange not executing

[Krystal]

I am trying to disable individual ActiveX checkboxes depending on the value in a cell. this value is filled in with the click of another ActiveX checkbox.

I have tried to adapt code that I have found on your site "729742-combine-multiple-private-sub-worksheet_-selectionchange.html"

I can not get the code to execute without clicking out of the checkbox then clicking in the target cell ($ value cell)

There are two columns of checkboxes for each day of the week. One for if a person attended and the other for if the person has paid. The attendance checkbox adds a dollar value to an owing column. Then the paid column adds this to a paid column. Which then in turn gives a running total of what the person owes.

I am trying to disable the attendance checkbox after the payment has been made for that day. so that users can not uncheck the attendance after a payment has been made.

here is a section of the code I have used

Private Sub Worksheet_SelectionChange(ByVal target As Range)
Dim r As Range, c As Range, cb As OLEObject


If Not Intersect(target, Range("j3:j22,s3:s22,ab3:ab22,ak3:ak22")) Is Nothing Then
    For Each c In Intersect(target, Range("j3:j22,s3:s22,ab3:ab22,ak3:ak22"))
              For Each cb In Me.OLEObjects
                         
            'target column is $
            'target row is the range covered
            'cb.Name Like "Monday" ... is name of checkbox to be disabled
            
            
            If target.Column = 10 And (target.Row > 2 And target.Row < 23) Then
            If cb.Name Like "Monday" & Val(Split(c.Address, "$")(2)) Then
            cb.Enabled = c.Value = 0
            End If
            
            ElseIf target.Column = 19 And (target.Row > 2 And target.Row < 23) Then
            If cb.Name Like "Tuesday" & Val(Split(c.Address, "$")(2)) Then
            cb.Enabled = c.Value = 0
            End If
            
             ElseIf target.Column = 28 And (target.Row > 2 And target.Row < 23) Then
             If cb.Name Like "Wednesday" & Val(Split(c.Address, "$")(2)) Then
             cb.Enabled = c.Value = 0
            End If
            
             ElseIf target.Column = 37 And (target.Row > 2 And target.Row < 23) Then
            If cb.Name Like "Thursday" & Val(Split(c.Address, "$")(2)) Then
            cb.Enabled = c.Value = 0
            End If
             
             ElseIf target.Column = 46 And (target.Row > 2 And target.Row < 23) Then
            If cb.Name Like "Friday" & Val(Split(c.Address, "$")(2)) Then
            cb.Enabled = c.Value = 0
            End If
                                   
            
            
Next cb
Next c
End If




End Sub

 

[Hercules1946]

I presume its not reporting an error, but have you tried debug to confirm that it's not executing (it could be running and finding nothing to do for some reason).

 

[Krystal]

Thank you for your quick response. I have run the debug and no errors. But the code i supplied here is missing and 'end if' directly above 'next cb'.
I agree that it is running but not finding anything to do until I click away from the checkbox then clicking on the target cell (which is the intersect between target column and current row)

 

[StephenCrump]

Why are you using code for the Worksheet_SelectionChange event, which won't fire if you click a CheckBox?

It sounds like you want the Attendance CheckBox to be disabled when the corresponding Payment CheckBox has been checked?

 

[Krystal]

Hi StephenCrump yes I am trying to disable the attendance checkbox when the payment checkbox is checked. Can you point me in a better direction?

 

[StephenCrump]

Presumably you have code for each PaymentCheckBox_Click event which fills in a payment amount. It would make sense to add the code here to disable the Attendance checkbox.

It will probably be easiest for us to see what's going on if you can post your workbook (with cut-down dummy data) or your entire code?

If that's not easy/possible, can you post your code for a sample PaymentCheckBox and for the corresponding Attendance CheckBox ?

Also, is there an obvious correspondence between PaymentCheckBox and AttendanceCheckBox? e.g. perhaps:

cbPayment1 <---> cbAttendance1
cbPayment2 <---> cbAttendance2
etc

I am also guessing that you have lots of repetitive code for the various CheckBoxes. It should be possible to handle all CheckBox_Click events with a single set of code.

 

[Krystal]

thank you for your help. sorry for the late reply.
I am unable to upload my workbook so I will supply my entire code.

Firstly a description then the code for the worksheet (I apologise for the lengthy description but when I tried to use the MrExcelHtml addin all it gave me was 2 squares)

column A is a list of client names (retrieved from another tab page)
column B is checkbox for attendance named 'Monday' & row reference
column C is linked to checkbox in column B and equals True or False
Column D is a dollar value (retrieved using index match function from another tab page)
Column E formula is =IF(C3=TRUE,D3,0) ie if they attended there is a charge
column F formula is =IF(C3=TRUE,$B$26,0) cell B26 is extra charged calculated by total extra charged divided by number in attendance
column G total owing the formula is =SUM(E3:F3)
column H is a checkbox for paid named 'PaidMonday' etc & row reference
column I is linked to checkbox in column H and equals True or False
column J if paid this equals the amount owing=IF(I3=TRUE,G3,0)

these columns are then repeated for each day of the week
if the paid checkbox is ticked it disables the attendance checkbox (I don't want people to accidentally uncheck the attendance box if a payment has been made)
then there is a weekly payment option which disables the 'PaidMonday', 'PaidTuesday' etc checkboxes

now here is the code currently in Worksheet_SelectionChange which runs as long as the user then clicks another cell (any cell) then click the target cell (intercect between target column and target row)
I have then added a procedure to each paid checkbox which does this automatically, but the issue is that there would then be a lot of repetitive code.

Please note that this whole thing does work, and doesn't generate any errors. But surly there is a better way to either get the 'Worksheet_SelectionChange' to trigger or to have a generic procedure in each checkbox to do the triggering

Private Sub Worksheet_SelectionChange(ByVal target As Range)Dim r As Range, c As Range, cb As OLEObject
Set r = Me.Range("j3:j22,s3:s22,ab3:ab22,ak3:ak22,at3:at22,J3:bc22,b3:at22")


If Not Intersect(target, r) Is Nothing Then
For Each c In Intersect(target, r)
For Each cb In Me.OLEObjects

'target column is $
'target row is the range covered
'cb.Name Like "Monday" ... is name of checkbox to be disabled

If target.Column = 10 And (target.Row > 2 And target.Row < 23) Then
If cb.Name Like "Monday" & Val(Split(c.Address, "$")(2)) Then
cb.Enabled = c.Value = 0
End If

ElseIf target.Column = 19 And (target.Row > 2 And target.Row < 23) Then
If cb.Name Like "Tuesday" & Val(Split(c.Address, "$")(2)) Then
cb.Enabled = c.Value = 0
End If

ElseIf target.Column = 28 And (target.Row > 2 And target.Row < 23) Then
If cb.Name Like "Wednesday" & Val(Split(c.Address, "$")(2)) Then
cb.Enabled = c.Value = 0
End If

ElseIf target.Column = 37 And (target.Row > 2 And target.Row < 23) Then
If cb.Name Like "Thursday" & Val(Split(c.Address, "$")(2)) Then
cb.Enabled = c.Value = 0
End If

ElseIf target.Column = 46 And (target.Row > 2 And target.Row < 23) Then
If cb.Name Like "Friday" & Val(Split(c.Address, "$")(2)) Then
cb.Enabled = c.Value = 0
End If
End If

'want to disable monday, tuesday, wednesday, thursday and friday payment checkboxes_
'if the weekly payment option is selected and in turn disable the weekly payment option_
'if any of the daily options are selected

If target.Column = 54 And (target.Row > 2 And target.Row < 23) Then
If cb.Name Like "Paid*" & Val(Split(c.Address, "$")(2)) Then
'MsgBox "daily payments disabled"
cb.Enabled = c.Value = 0
End If
End If

If (target.Column > 9 And target.Column < 56) And (target.Row > 2 And target.Row < 23) Then
If cb.Name Like "Total*" & Val(Split(c.Address, "$")(2)) Then
'MsgBox "weekly payment disabled"
cb.Enabled = c.Value = 0
End If
End If


Next cb
Next c
End If


End Sub

Private Sub PaidMonday3_Click()'this procedure triggers the disabling of the daily attendance checkboxes
'this procedure will need to be repeated for every 'paid checkbox' for this method to work_
'unless I can find a generic method to do it


If PaidMonday3.Value Then
PaidMonday3.TopLeftCell.Offset(0, 0).Select
PaidMonday3.TopLeftCell.Offset(0, 2).Select
Else
PaidMonday3.TopLeftCell.Offset(0, 0).Select
PaidMonday3.TopLeftCell.Offset(0, 2).Select

End If
End Sub

'this procedure triggers the disabling of the daily payment checkboxes
Private Sub TotalPaid3_Click() 'works but have to name each cell that contains the checkboxes
If TotalPaid3.Value Then
TotalPaid3.TopLeftCell.Offset(0, 0).Select
TotalPaid3.TopLeftCell.Offset(0, 2).Select
Else
TotalPaid3.TopLeftCell.Offset(0, 0).Select
TotalPaid3.TopLeftCell.Offset(0, 2).Select
End If
End Sub

thank you in anticipation
Krystal

 

[StephenCrump]

Private Sub PaidMonday3_Click()

    If PaidMonday3.Value Then
        PaidMonday3.TopLeftCell.Offset(0, 0).Select
        PaidMonday3.TopLeftCell.Offset(0, 2).Select
    Else
        PaidMonday3.TopLeftCell.Offset(0, 0).Select
        PaidMonday3.TopLeftCell.Offset(0, 2).Select
    End If

End Sub

'distils down to:
Private Sub PaidMonday3_Click()

    PaidMonday3.TopLeftCell.Offset(0, 0).Select
    PaidMonday3.TopLeftCell.Offset(0, 2).Select

End Sub

i.e. the code simply select two cells.

This will trigger Worksheet_SelectionChange either once or twice.

I suggest you put in a breakpoint and step through your Worksheet_SelectionChange code when it is triggered. There are lots of IF statements, and you'll need to see which conditions are being satisfied. It could be as simple as the selected cells lying outside the monitored Range("j3:j22,s3:s22,ab3:ab22,ak3:ak22,at3:at22,J3:bc22,b3:at22")?

But if you want to enable/disable another checkbox depending on PaidMonday3, why not just:

Private Sub PaidMonday3_Click()
    
    AttendanceMonday3.Enabled = Not PaidMonday3.Value

End Sub

As I said previously, you can write code to cover multiple checkboxes. However, before going down this route I'd be inclined to ask: do I need so many checkboxes? It must be a nightmare to add/delete people, rollover to a new week etc etc?

Why not bring up one person's details (to top of sheet, separate sheet, UserForm), process there (using a couple of checkboxes if you like) and use this information to update the sheet?

 

[Krystal]

Thank you for your suggestions

Private Sub PaidMonday3_Click()

    PaidMonday3.TopLeftCell.Offset(0, 0).Select
    PaidMonday3.TopLeftCell.Offset(0, 2).Select

End Sub

i.e. the code simply select two cells.
This will trigger Worksheet_SelectionChange either once or twice.

Although this does appear to trigger the Worksheet_SelectionChange twice, without either line of code the checkbox remains disabled, even if, the 'paid' checkbox is unchecked.

As I said previously, you can write code to cover multiple checkboxes.

I would like to know how to do this if, I could be so bold as to ask for your help.

However, before going down this route I'd be inclined to ask: do I need so many checkboxes?

I have to have one checkbox for attending, and one for the payment per person. Most people pay daily, occasionally someone pays per week. Therefore the only check box that I could remove would be the weekly payment.

It must be a nightmare to add/delete people, rollover to a new week etc etc?

The names and their individual attendance charges are added to a 'clients' tab then automatically filled in using Index Match function. I also have a balance carried forward column that will give a weekly running total.

Why not bring up one person's details (to top of sheet, separate sheet, UserForm), process there (using a couple of checkboxes if you like) and use this information to update the sheet?

I would rather not use a use a separate UserForm

Private Sub PaidMonday3_Click()
    
    AttendanceMonday3.Enabled = Not PaidMonday3.Value

End Sub

if I can not find away to either automatically trigger the Worksheet_SelectionChange or find a procedure that can be run on all check boxes, I will probably, do away with my Worksheet_SelectionChange code and use this procedure for each paid checkbox.
Thank you again

 

[StephenCrump]

In summary:

1. You're using Worksheet_SelectionChange as an intermediate tool to get one set of checkboxes to enable/disable another set of Checkboxes. This means there's a lot of overhead: you're monitoring selection changes in quite a large range, and looping through all control names whenever a change is made.

2. Without seeing your workbook, it's impossible to see what code is/isn't being triggered. It might be helpful to put in breakpoints and step through so you can see what's actually happening. I have just noticed in the first code you posted for Worksheet_SelectionChange that you are testing for column=46 ("AT") but this isn't included in the range being monitored, so presumably this code won't work for Fridays.

3. There is a more efficient way. Here's some sample code that assumes we have two sets of CheckBoxes: CheckBoxN and OtherCheckBoxN, where N = 1, 2, 3, ....

If you check CheckBoxN the code will disable OtherCheckBoxN, and vice versa.

Here's the workbook: https://app.box.com/s/dfyho51v1e3qkv4kln5i7ts4coc6r2jg

'In the ThisWorkbook module
Private Sub Workbook_Open()
    
    Dim o As OLEObject
    Dim clsTrapCheckBox As CTrapCheckBox
    
    Set col = New Collection
    Set ws = Sheet1   'Where you want to trap CheckBoxes, or use sheetname
    For Each o In ws.OLEObjects
        If TypeOf o.Object Is MSForms.CheckBox Then
            Set clsTrapCheckBox = New CTrapCheckBox
            Set clsTrapCheckBox.cb = o.Object 
            col.Add clsTrapCheckBox
        End If
    Next
    
    Set clsTrapCheckBox = Nothing

End Sub

'In a code module
Public col As Collection
Public ws As Worksheet
'In a class module called CTrapCheckBox
Public WithEvents cb As MSForms.CheckBox
Private Sub cb_Click()

    'Put event code here. Example:
    
    If Left(cb.Name, 5) <> "Other" Then _
        ws.OLEObjects("Other" & cb.Name).Enabled = Not cb.Value

End Sub