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

 

[Krystal]

looking very promising.
I have a problem with my copy of Excel. I know it isn't your file. I have tried it on another computer. I will get back to you when I have it sorted. When I put your excel workbook into design mode, then take it out of design mode, the procedures it stops working, even without making any changes. Thank you very much for your help.
I will get back to you when I have this other issue sorted.

 

[StephenCrump]

When I put your excel workbook into design mode, then take it out of design mode, the procedures it stops working, even without making any changes.

Just re-run: Sub Workbook_Open to initialise.

 

[StephenCrump]

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)

Coincidentally, I just started having the same issue with the MrExcelHTML maker.

RickXL kindly posted this solution which worked for me: http://www.mrexcel.com/forum/excel-...lhtml-maker-pasting-only-r-r.html#post4399126

 

[Krystal]

Fantastic it code worked perfectly. As it is the Attendance check boxes I want to disable, I changed their names to AttendMonday3, AttendMonday4 etc. Then changed the names of the Paid check boxes to Monday3, Tuesday3 etc.

I then inserted the code you gave me into the 'ThisWorkbook module', 'code module' then into the 'class module' changing the word 'other' to Attend' and the length to from 5 to 6.

Once again, 'thank you' so much for your help This is a much better solution than anything that I could have come up with. I really appreciate it.

 

[Krystal]

Thank you for re-posting RickXL's solution to my MrExcelHtml maker issues of just pasting □ □ , or r r

 

[StephenCrump]

You're welcome.

I'm glad you could adapt the CheckBox code successfully for your workbook.