Update changes without deselecting/reselecting checkbox

I am trying to have the copy and pasted ranges shown automatically updated when 1) The checkbox is TRUE and 2) When the value in ComboBox1 changes ( in this case from OPEN to CLOSED or vice versa). The code works as intended but I have to deselect and reselect the checkbox after I change to OPEN or CLOSED, this is what I'm trying to overcome.

When the Checkbox is FALSE, there is a different set of executions to be carried out (commented out in the code). So I want to do the changes all while the Checkbox is TRUE.

I have tried to implement a while loop but ended up getting stuck in the loop.

Here is the code:

Private Sub Checkbox1_Change()
If Checkbox1.Value = True Then
    ComboBox1.List = Array("Closed", "Open")
    With ComboBox1.Value = "Open"
        Worksheets("FSM Search Data").Range("$A$1:$AD$2000").AutoFilter Field:=4, Criteria1:=Worksheets("FSM Search").ComboBox1.Value
        Worksheets("FSM Search Data").Range("B2:AD2000").SpecialCells(xlCellTypeVisible).Copy
        AutoFilterMode = False
        Worksheets("FSM Search").Activate
        Worksheets("FSM Search").Range("B4").PasteSpecial xlPasteValues
        Range("B1:AD5").Columns.AutoFit
    End With

    With ComboBox1.Value = "Closed"
        Worksheets("FSM Search Data").Range("$A$1:$AD$2000").AutoFilter Field:=4, Criteria1:=Worksheets("FSM Search").ComboBox1.Value
        Worksheets("FSM Search Data").Range("B2:AD2000").SpecialCells(xlCellTypeVisible).Copy
        AutoFilterMode = False
        Worksheets("FSM Search").Activate
        Worksheets("FSM Search").Range("B4").PasteSpecial xlPasteValues
        Range("B1:AD5").Columns.AutoFit
    End With
End If

'If Checkbox1.Value = False Then
'With Worksheets("FSM Data")
'ComboBox1.List = .Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row).Value
'Worksheets("FSM Search").Activate
'Range("C4").Value = ComboBox1.Value
'Range("B1:AD5").Columns.AutoFit
'End With 'End If End Sub

Thank you for the help!!!!

1 answer

  • answered 2018-01-11 20:48 Trevor Burger

    Changed Private Sub Checkbox1_Change()

    to

    Private Sub ComboBox1_Change()
    

    Thank you Scott