Excel – Activate checkbox value sent from Excel sheet in userform

excelvba

Since a while I have been trying to make this work but in spite of my thorough Internet searches the answer keeps eluding me.
I made a userForm in VBA Excel with some textboxes en checkboxes. These values are sent to a sheet in the excel file. The checkboxes represent categories so multiple choices can be selected. The value is translated to true = "x" and false = "" before it is send to the sheet.

If userForm.checkbox1.Value = True Then
    ws.Rows.Cells(row, 9).Value = "X"
Else
    ws.Rows.Cells(row, 9).Value = ""
End If

The information put in the sheet can be retrieved and edited by using almost the same userForm.
This form has a row selection box with which the desired row can be summoned and the corresponding data populates the userform. The "x" is translated back like this:

If ws1.Rows.Cells(row, 9).Value = "X" Then
    userFormedit.checkbox1.Value = True
Else
    userFormedit.checkbox1.Value = False
End If

The problem is that the selected checkboxes show the desired value but in grey.
When sending the information back to the sheet, these values are not "seen".
So on the sheet these values disappear. One option is to re-check all the boxes again.
This is not what I desire since a lot of people are going to use this form and that would be time consuming and impractical.
Is there a way to activate/enable the checkboxes?

Best Answer

You should consider sending from User form to sheet in a _Click() and loading sheet values to Form in an _Activate sub, e.g. 

Private Sub CheckBox1_Click()
    If CheckBox1.Value Then
        [A1] = "X"
    Else
        [A1] = ""
    End If
End Sub

Private Sub UserForm_Activate()
    If [A1] = "X" Then
        Me.CheckBox1 = True
    Else
        Me.CheckBox1 = False
    End If
End Sub

If a checkbox is shown in grey, the reason could be that it was disabled by the same or another piece of code or from the element's property window (CheckBox1.Enabled = False).

Also mind that Excel is not really multi-user friendly. Once multiple users have opened the same Excel, it resides in their PC's memory and other peoples' changes are not visible unless they are closing & reopening the file.

Related Topic