Activate checkbox value sent from Excel sheet in userform
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?
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.