Checkbox tick validation

[Celticfc]

Hi all,

My code below is for a checkbox in a userform.

When I load a manager and click the button, it saves a "flight risk" entry to their row in the database.

The problem is, when I close and re-open the userform for the same person, the tick disappears even though I already saved them as flight risk. 

In other words, if someone is already flight risk, the tick on the checkbox should appear.

Otherwise I have no indication whether if I have previously marked the person as flight risk.

Private Sub CheckBox1_Click()
Dim FindString As String
    Dim Rng As Range
    FindString = Range("e11").Value
    
    If Trim(FindString) <> "" Then
        With Sheets("manager 1").Range("f:f")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                            
            If Not Rng Is Nothing Then
            With Application
                .ScreenUpdating = False
                .EnableEvents = False
                .Calculation = xlCalculationManual
                .Goto Rng, False
            'End With
                    With ActiveCell
                    .Offset(0, 45).Value = "flightrisk"
                        
                        
                        End With
                'With Application
                  
                   .EnableEvents = True
                   .Calculation = xlCalculationAutomatic
                   
                         'MsgBox "Comment Saved"
                    'Else
                End With
                Sheets("one-pager profile").Select
                MsgBox "Saved as Flight-risk"
                
                
               ' Call ViewManager1
 Application.ScreenUpdating = True
            End If
        'End With
    End With
     End If
     
End If
If CheckBox1.Value = False Then
    If Trim(FindString) <> "" Then
        With Sheets("manager 1").Range("f:f")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                            
            If Not Rng Is Nothing Then
            With Application
                .ScreenUpdating = False
                .EnableEvents = False
                .Calculation = xlCalculationManual
                .Goto Rng, False
            'End With
                        With ActiveCell
                      .Offset(0, 45).Value = ""
                         
                        
                        End With
                'With Application
                  
                   .EnableEvents = True
                   .Calculation = xlCalculationAutomatic
                   
                         'MsgBox "Comment Saved"
                    'Else
                End With
             Sheets("one-pager profile").Select
                MsgBox "Removed Flight-risk"
                
                
                
               ' Call ViewManager1
 Application.ScreenUpdating = True
            End If
        'End With
    End With
     End If
End If
 
End Sub

 

[mumps]

The only way I can think of doing what you want is to use a helper cell in your sheet. Choose a cell which will never be used on you sheet and that would not be visible, for example cell AA1. then at the beginning of your Private Sub CheckBox1_Click() macro insert this code:

If CheckBox1 = True Then
    Range("AA1") = "True"
Else
    Range("AA1") = ""
End If

This code will insert the word "True" in cell AA1 when the checkbox is clicked and clear AA1 when the checkbox is not clicked.
In the code module for your userform, paste this macro:

Private Sub UserForm_Initialize()
    If Range("AA1") = "True" Then CheckBox1 = True
End Sub

When you open the userform, it will check if AA1 contains the word "True". If it does, then the checkbox will be selected, if not the checkbox will not be selected.

 

[mikerickson]

I would right a function (RiskCell) and use that to locate the cell. Note the full qualification of ranges in RiskCell.
Then the initialize event could look at the RiskCell to set the value for the Checkbox.

Private Sub UserForm_Initialize()
    On Error Resume Next
        CheckBox1.Value = LCase(RiskCell.Value) = "flight risk"
    On Error GoTo 0
End Sub

Private Sub CheckBox1_Change()
    Dim Rng As Range
    Set Rng = RiskCell
    Me.Caption = CheckBox1.Value & Rng.Address
    If Not Rng Is Nothing Then
        Rng.Value = IIf(CheckBox1.Value, "Flight Risk", vbNullString)
    End If
End Sub

Private Function RiskCell() As Range
    Dim FindString As String
    Dim Rng As Range
    FindString = ThisWorkbook.Sheets("Sheet1").Range("e11").Value
    
    If Trim(FindString) <> "" Then
        With ThisWorkbook.Sheets("manager 1").Range("f:f")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Set RiskCell = Rng.Offset(0, 45)
            End If
        End With
    End If
End Function

 

[Celticfc]

The only way I can think of doing what you want is to use a helper cell in your sheet. Choose a cell which will never be used on you sheet and that would not be visible, for example cell AA1. then at the beginning of your Private Sub CheckBox1_Click() macro insert this code:

If CheckBox1 = True Then
    Range("AA1") = "True"
Else
    Range("AA1") = ""
End If

This code will insert the word "True" in cell AA1 when the checkbox is clicked and clear AA1 when the checkbox is not clicked.
In the code module for your userform, paste this macro:

Private Sub UserForm_Initialize()
    If Range("AA1") = "True" Then CheckBox1 = True
End Sub

When you open the userform, it will check if AA1 contains the word "True". If it does, then the checkbox will be selected, if not the checkbox will not be selected.

Hello,

Thank you.

I've tried this but it simply actions the True value when I open the userform. It will mark every person that I open in userform as flight risk.

I think I'm going to add a normal button istead with a textbox underneath to indicate if the user is a flightrisk or not.

 

[mikerickson]

Have you looked at my reply, simulposted with your post.

 

[Celticfc]

I would right a function (RiskCell) and use that to locate the cell. Note the full qualification of ranges in RiskCell.
Then the initialize event could look at the RiskCell to set the value for the Checkbox.

Private Sub UserForm_Initialize()
    On Error Resume Next
        CheckBox1.Value = LCase(RiskCell.Value) = "flight risk"
    On Error GoTo 0
End Sub

Private Sub CheckBox1_Change()
    Dim Rng As Range
    Set Rng = RiskCell
    Me.Caption = CheckBox1.Value & Rng.Address
    If Not Rng Is Nothing Then
        Rng.Value = IIf(CheckBox1.Value, "Flight Risk", vbNullString)
    End If
End Sub

Private Function RiskCell() As Range
    Dim FindString As String
    Dim Rng As Range
    FindString = ThisWorkbook.Sheets("Sheet1").Range("e11").Value
    
    If Trim(FindString) <> "" Then
        With ThisWorkbook.Sheets("manager 1").Range("f:f")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Set RiskCell = Rng.Offset(0, 45)
            End If
        End With
    End If
End Function

Hello,

Thank you for your time.

I have pasted the form to my UserForm code, is that fine?

When I click on checkbox to tick it, I get runtime 91 error for code:

 Me.Caption = CheckBox1.Value & Rng.Address

 

[mikerickson]

Sorry that like was a testing line. It can be omitted.

Also make sure that the definition of FindString in RiskCell points to the correct worksheet.