Change Value of One Checkbox Based on Value of Another Checkbox

[NorthbyNorthwest]

Hi, everyone. I am using Excel's new checkboxes. This is what I want to achieve. If check box value in column AG is TRUE, then I want checkbox value in column AH to be FALSE. I have code below that loops through the columns and makes changes if necessary. I have to manually trigger the code. I cannot figure out how to get this code to work as an event. In other words, whenever user makes checkbox in AG TRUE, column AH checkbox will change to FALSE, if it is not already FALSE. Could someone help?

Sub TrueFalse()
Dim i As Integer

'If inactive field checked true in Assignment table then auditors field in table false
Sheet7.Activate
For i = 6 To ActiveSheet.UsedRange.Rows.Count
    If Range("AG" & i).value = True Then
        Range("AH" & i).value = False
    End If
Next i
End Sub

 

[coulombevin]

Hi NorthbyNorthwest,

You're asking too loop from 6 to number of row used which could be less than what you expected. If row 2 is empty, your code would end up one row before the end of your range 6.

Here's the corrected VBA snippet:
*I am using the French version of Excel 365, so there might be some errors due to manual translation or discrepancies in the Excel user manual*

Sub TrueFalse()
    Dim i As Integer, lstRow As Integer 
    With Sheets("NameOfYourSheet")
        lstRow = .Range("AG1048576").End(xlUp).Row
        For i = 6 To lstRow
            If Range("AG" & i).Value = True Then
                Range("AH" & i).Value = False
            End If
        Next i
    End With
End Sub

Bests regards,

Vincent

 

[NorthbyNorthwest]

Hi NorthbyNorthwest,

You're asking too loop from 6 to number of row used which could be less than what you expected. If row 2 is empty, your code would end up one row before the end of your range 6.

Here's the corrected VBA snippet:
*I am using the French version of Excel 365, so there might be some errors due to manual translation or discrepancies in the Excel user manual*

Sub TrueFalse()
    Dim i As Integer, lstRow As Integer
    With Sheets("NameOfYourSheet")
        lstRow = .Range("AG1048576").End(xlUp).Row
        For i = 6 To lstRow
            If Range("AG" & i).Value = True Then
                Range("AH" & i).Value = False
            End If
        Next i
    End With
End Sub

Bests regards,

Vincent

Thanks for quick reply, coulombevin. The code works. I just don't know how to tie it to the worksheet change event, I think. When the user clicks checkbox in column AG, I want checkbox next door in column AH to be unchecked.

 

[coulombevin]

No problem, since you want to trigger an event listener, I would go in your VBA sheet (not a module - see screenshot) and I would write this:

Private Sub Worksheet_Change(ByVal Target As Range)
    'If the changed cell is AG (column 33) and the row is 6 or more
    If Target.Column = 33 And Target.Row >= 6 Then
        ' if the cell that as been changed is now true then change AH to false
        If Range("AG" & Target.Row) = True Then
            Range("AH" & Target.Row) = False
        End If
    End If
End Sub

As you can see here, it is a french version of excel, but my VBA snippet was in the greyish VBA sheet

Bests regards,

Vincent

 

[NorthbyNorthwest]

No problem, since you want to trigger an event listener, I would go in your VBA sheet (not a module - see screenshot) and I would write this:

Private Sub Worksheet_Change(ByVal Target As Range)
    'If the changed cell is AG (column 33) and the row is 6 or more
    If Target.Column = 33 And Target.Row >= 6 Then
        ' if the cell that as been changed is now true then change AH to false
        If Range("AG" & Target.Row) = True Then
            Range("AH" & Target.Row) = False
        End If
    End If
End Sub

View attachment 121177

As you can see here, it is a french version of excel, but my VBA snippet was in the greyish VBA sheet

Bests regards,

Vincent

Works perfectly. Thank so much for the help.

 

[coulombevin]

Your welcome! You can close the topic by writing the last message as Solution!

See you around,

Vincent