1

I have a workbook in which I want to disable activex check boxes. 1 check box in a row determines whether all other check boxes in that row are enabled or disabled.
My code works fine but is very inefficient. Is there a way to refer to multiple activeX check boxes without naming each one. At this stage I have 22 rows and this code is repeated for each row. and then again to enable the same check boxes. your help would be appreciated. Here is the section of code I need help to simplify.

If .Row = 1 Then
     ActiveSheet.OLEObjects("AttendMonday5").Enabled = False
     ActiveSheet.OLEObjects("AttendTuesday5").Enabled = False
     ActiveSheet.OLEObjects("AttendWednesday5").Enabled = False
     ActiveSheet.OLEObjects("AttendThursday5").Enabled = False
     ActiveSheet.OLEObjects("AttendFriday5").Enabled = False
     ActiveSheet.OLEObjects("Monday5").Enabled = False
     ActiveSheet.OLEObjects("Tuesday5").Enabled = False
     ActiveSheet.OLEObjects("Wednesday5").Enabled = False
     ActiveSheet.OLEObjects("Thursday5").Enabled = False
     ActiveSheet.OLEObjects("Friday5").Enabled = False
  End If
Community
  • 1
  • 1
Kris12
  • 65
  • 9
  • What is the name of the checkbox which triggers these changes? – SilentRevolution Jan 30 '16 at 23:49
  • I have 1 column of form control checkboxes, probably unconventional, but it does work, the name of checkbox that triggers the change in row 1 is 'Check Box 1'. the name of the checkbox that triggers the change in row 2 is 'Check Box 2' etc – Kris12 Jan 31 '16 at 00:13
  • so the triggers are form controls in a column and they trigger activeX controls on the same row, these are named `AttendMonday1, AttendTuesday1...etc.`. is that is tied into the `worksheet_change() ` or `workbook_sheetchange()` event? – SilentRevolution Jan 31 '16 at 00:16

3 Answers3

1

You can achieve it using an array of objects, as follows:

If .Row = 1 Then
  ActiveSheet.OLEObjects(Array("AttendMonday5", "AttendTuesday5", "AttendWednesday5", "AttendThursday5", "AttendFriday5", "Monday5", "Tuesday5", "Wednesday5", "Thursday5", "Friday5")).Enabled = False
End If
ThunderFrame
  • 9,352
  • 2
  • 29
  • 60
  • thank you Thunderframe for your quick response. yes your solution works better than I had :) – Kris12 Jan 31 '16 at 01:19
1

EDIT

Assign this macro to all form control Checkboxes. The form Control Checkboxes need to be link to the correct row in column BH. The code checks all filled cells in column BH and enables or disables all ActiveX checkboxes in the row provided the ActiveX controls have the correct row number at the end of their names (not to be confused with the control's caption). You don't need to add code if you add a new row, just make sure the names of the new controls are correct and this macro is assigned to the form control on the new row.

Sub enableCheckboxes()
'Macro to enable or disable a row of activeX checkboxes based on a cell value in that row

    'Declaration of variable
    Dim rngLinked As Range, rngRow As Range
    Dim enableRow As Boolean
    Dim ws As Worksheet
    Dim chkBox As oleobject
    Dim rowNum As Long, rowNumLength As Long

    'Setting of object variables
    Set ws = ThisWorkbook.Worksheets("Sheet1") 'Change to the actual name of your sheet.
    With ws
        Set rngLinked = .Range(.Cells(1, 60), .Cells(.Rows.Count, 60).End(xlUp)) 'Range set to column BH
    End With

    'Nested loops enable disable objects
    Application.ScreenUpdating = False 'Prevent screenupdating
    For Each rngRow In rngLinked 'Check column BH
        enableRow = rngRow 'See if has to be enabled or not
        rowNum = rngRow.Row 'Row to enable or disable
        rowNumLength = Len(CStr(rowNum)) 'Used to extract the rownumber from the checkbox' names
        For Each chkBox In ws.OLEObjects 'Iterate through the OLE objects on the sheet.
            If TypeName(chkBox.Object) = "CheckBox" And CLng(Right(chkBox.Name, rowNumLength)) = rowNum Then 'Check if object is checkbox and is on the current row
                If enableRow Then 'Check to enable or disable object
                    chkBox.Enabled = True
                Else
                    chkBox.Enabled = False
                End If
            End If
        Next chkBox
    Next rngRow
    Application.ScreenUpdating = True

End Sub

END OF EDIT

You could try something like

Option Explicit

Sub disableAllCheckboxes()
    'This sub assumes all checkboxes on the sheet need to be disabled. Add logictests accordingly.

    Dim chkBox As OLEObject
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sheet1") 'Replace Sheet1 with the name of your actual worksheet

    For Each chkBox In ws.OLEObjects
        If TypeName(chkBox.Object) = "CheckBox" Then
            chkBox.Enabled = False
        End If
    Next

End Sub
SilentRevolution
  • 1,495
  • 1
  • 16
  • 31
  • Hi SilentRevolution. I am actually trying to disable only one row of activeX Checkboxes at a time. the Trigger is a form control checkbox in the same row. ThunderFrame's code works better than what I started with. – Kris12 Jan 31 '16 at 01:22
  • I understand, and I am tinkering with a solution but I need a bit more info, is the form control linked to a cell? If so which cell? Did you write and link a sub to each form control for each row? – SilentRevolution Jan 31 '16 at 01:27
  • the form control checkbox is linked to the cell in column BH in the same row as checkboxes to be disabled. – Kris12 Jan 31 '16 at 02:37
  • And the controls to disable on that row all contain a number in the name? Like in your example, I'm assuming these controls refer to row 5. – SilentRevolution Jan 31 '16 at 02:39
  • The following code is linked to each form control checkbox. 'Sub CheckBox_Click()' This Determines what checkbox was clicked & its row. It runs 'sub Lock_Row() If Not oCheckBox Is Nothing Then With oCheckBox bChecked = .Value = 0 With .TopLeftCell lRow = .Row If Not bChecked Then If .Row = 3 Then' this is where my repetitive code is. If .row = 4 then' repetitive code etc – Kris12 Jan 31 '16 at 02:53
  • sorry but my example was not quite accurate. this is correct all activex checkboxes to be disabled in row 3, have 3 at the end of their name. and all activex checkboxes to be disabled in row 4, have 4 at the end of their name. and so on till row 22 – Kris12 Jan 31 '16 at 02:57
  • firstly thank you for your help. I am trying your Sub enableCheckboxes() and get a type mismatch error in line 24. I have ensured that each activex checkbox to be disabled has the correct row number at end of name. does the name of the form control checkbox affect it. or just the row number of the linked cell, as long as it is in column 60? – Kris12 Jan 31 '16 at 05:34
  • The name of the triggering form control is irrelevant, the code is triggered by any form control checkbox and then it works down the column 60. Which line is line 24 for you? This isn''t necessarily the same line 24 for me as for you. – SilentRevolution Jan 31 '16 at 07:49
  • sorry, line 24 is 'If TypeName(chkBox.Object) = "CheckBox" And CLng(Right(chkBox.Name, rowNumLength)) = rowNum Then' – Kris12 Jan 31 '16 at 08:42
  • SilentRevolution Thank you so much for your solution to my problem. without it I would never have been able to do this. I have made a couple of changes and posted my answer. :) – Kris12 Jan 31 '16 at 09:26
1
    Sub enableCheckboxes()
'Macro to enable or disable a row of activeX checkboxes based on a cell value in that row

    'Declaration of variable
    Dim rngLinked As Range, rngRow As Range
    Dim enableRow As Boolean
    Dim ws As Worksheet
    Dim chkBox As OLEObject
    Dim rowNum As Long, rowNumLength As Long

    'Setting of object variables
    Set ws = ThisWorkbook.Worksheets("sheet1") 'Change to the actual name of your sheet.
    With ws
        Set rngLinked = .Range(.Cells(1, 60), .Cells(.Rows.Count, 60).End(xlUp)) 'Range set to column BH
    End With

    'Nested loops enable disable objects
    Application.ScreenUpdating = False 'Prevent screenupdating
    For Each rngRow In rngLinked 'Check column BH
        enableRow = rngRow 'See if has to be enabled or not
        rowNum = rngRow.Row 'Row to enable or disable
        rowNumLength = Len(CStr(rowNum)) 'Used to extract the rownumber from the checkbox' names
        For Each chkBox In ws.OLEObjects 'Iterate through the OLE objects on the sheet.

           If chkBox.Name Like "Attend*" & rowNum Then
                    If enableRow Then 'Check to enable or disable object
                    chkBox.Enabled = False
                    Else
                    chkBox.Enabled = True
                    End If
                    End If

           If chkBox.Name Like "*day" & rowNum Then 'rowNum Then
                    If enableRow Then
                    chkBox.Enabled = False
                    Else
                    chkBox.Enabled = True
                    End If
                    End If
Next chkBox
Next rngRow

Application.ScreenUpdating = True
End Sub
SilentRevolution
  • 1,495
  • 1
  • 16
  • 31
Kris12
  • 65
  • 9
  • Very good, I'm glad you were able to adapt it to suit your needs. If I may I'd like to make a couple of suggestions. 1) remove variables you no longer use, like `rowNumLength`. 2) with the `Like "Attend*" & rowNum` if `rowNum` is 1, it will also trigger on checkboxes on rows `11` and `21`, Since all checkboxes end on `"*day" & rowNum` anyway, remove the `"Attend* & rowNum` altogether since all checkboxes on the row are caught by the `Like "*day" & rowNum` 3) for readability use proper indenting, you might have a look at my answer as a reference but there are plenty of other examples. – SilentRevolution Jan 31 '16 at 13:00
  • Thank you again for your help. it is very, very much appreciated. never would have got to this point without your help. Yes I will, do your suggested cleanup. I have just checked and row 11 and 21 are not triggered with row 1. so all good. Great thank you again – Kris12 Jan 31 '16 at 19:04