How to only allow user tick the Checkbox based on Reference Cell Formula result is TRUE.

[Man_of_Sleep]

Hi,

I've browsed to find if my question already asked before. But I couldn't find any.

My question is:
I have a column with a linked checkbox in each every cell (Column A)
Then I tried to use the data validation function to set: user can only tick the checkbox in column A if formula in reference cell in another column is TRUE (reference column is column B).
If the reference column formula result is FALSE, but user still try to tick the checkbox in column A, error message pop-up.

I have failed many time to set the data validation.
Can anyone teach me how to use the data validation to achieve that?
Or if can use vba code?

Please teach me.

Thank you.

 

[GWteB]

Wouldn't it be nicer to omit the annoying pop-ups by disabling the relevant check box? In such a manner clicking the check box doesn't affect the state of it.
It could be done with VBA code like this:

CheckBox1.Enabled = False

 

[Man_of_Sleep]

Thanks you GWteB for your feedback.

How to insert the VBA if I have this code:

Sub addNewRow_r2()
    
    ' Do not insert a row before the first row.
    Const TopRow As Long = 1
    
    ' Get the active row number.
    Dim rowNum As Long
    rowNum = ActiveCell.Row
    
    If (rowNum > TopRow) Then

        Rows(rowNum).Insert       ' Insert a new row.
        
        ' === add a Check Box ===
        Dim oCB As CheckBox
        Dim c   As Range
        
        Set c = Cells(rowNum, 1)
        With c
            Set oCB = CheckBoxes.Add(.Left, .Top, .Width, .Height)
            oCB.LinkedCell = .Address
            oCB.Caption = vbNullString
        End With
    End If
End Sub

Sorry, I am noob in VBA.

 

[GWteB]

You're in need of another procedure than the one you provide, i.e. a procedure that keeps track of changes in your worksheet. Excel's Change event procedure could be used, however, since the check box's availabilty will be dependent on the result of a formula the Calculation event procedure comes in mind. This has a minor drawback. Excel doesn't notify us which formulae are responsible for the observed change, so we have to iterate the worksheet on each calculation. If you agree on that, the code below will do as wanted and is to be pasted in the module of the worksheet to be affected.

Private Sub Worksheet_Calculate()

    Dim s As Shape, v2 As Variant

    ' search for check boxes on worksheet and enable / disable
    ' availability / changeability of each based on boolean in right adjacent cell

    For Each s In Me.Shapes
        If s.Type = msoFormControl Then
            If s.FormControlType = xlCheckBox Then
                If Not s.ControlFormat.LinkedCell = vbNullString Then
                    v2 = Me.Range(s.ControlFormat.LinkedCell).Offset(0, 1).Value2
                    If VarType(v2) = vbBoolean Then
                        s.ControlFormat.Enabled = v2
                    End If
                End If
            End If
        End If
    Next s
End Sub

 

[Man_of_Sleep]

Dear Master GWteB,

Thank you for your explanation and VBA code.
It works really well.
I am truly appreciate with your great help.

I owed you 2 times.
Sorry for taking your time.

Thanks again.

 

[GWteB]

My pleasure glad it's sorted.

 

[Man_of_Sleep]

Dear GWteB,

Sorry for bothering you again.
This is about above topic.

The code is working really well, thank you.
But I've found out when I choose to protect the sheet.
It became error 400 message pop-up when I run the macro.
I already ticked allowed user to add row when I protect the sheet.

The overall code in my sheet is like this:

Sub addNewRow_r2()
    
    ' Do not insert a row before the first row.
    Const TopRow As Long = 1
    
    ' Get the active row number.
    Dim rowNum As Long
    rowNum = ActiveCell.Row
    
    If (rowNum > TopRow) Then

        Rows(rowNum).Insert       ' Insert a new row.
        
        ' === add a Check Box ===
        Dim oCB As CheckBox
        Dim c   As Range
        
        Set c = Cells(rowNum, 1)
        With c
            Set oCB = CheckBoxes.Add(.Left, .Top, .Width, .Height)
            oCB.LinkedCell = .Address
            oCB.Caption = vbNullString
        End With
    End If
End Sub

Private Sub Worksheet_Calculate()

    Dim s As Shape, v2 As Variant

    ' search for check boxes on worksheet and enable / disable
    ' availability / changeability of each based on boolean in right adjacent cell

    For Each s In Me.Shapes
        If s.Type = msoFormControl Then
            If s.FormControlType = xlCheckBox Then
                If Not s.ControlFormat.LinkedCell = vbNullString Then
                    v2 = Me.Range(s.ControlFormat.LinkedCell).Offset(0, 1).Value2
                    If VarType(v2) = vbBoolean Then
                        s.ControlFormat.Enabled = v2
                    End If
                End If
            End If
        End If
    Next s
End Sub

Sorry to asking you again.

Thank you in advance.

 

[GWteB]

When a worksheet is protected, VBA code is as limited in making changes as a user is. You should realize that the code accomplishes several things:
- insert a new row of which all cells inherit the format of the cells immediately above it;
- place a form control;
- change properties of this control.
Besides eg font and color the format also concerns protection, so if a cell is locked or not. You might consider extending the code with two extra steps: first release the worksheet, then perform the above steps and finally protect the worksheet again. You should determine for yourself what degree of protection you want for your worksheet, and of course in such a way that it remains workable.