how to set the value of multiple checkbox of the userform to a single cell

user_16376185 0
Viewed 129

Situation: I have a userform as shown below:

Userform with 3 checkbox

current solution when i click OK, the following result is displayed current result from the checkbox

That means the checkbox values are stored in individual cells.

desired solution
Can I possibly modify my code such that the value is entered in a single cell for example like in the following picture with various combinations of the checkbox result is shown. desired result from the userform

My code is as follows:

Private Sub CommandButton1_Click()
If CheckBox1.Value = True Then Worksheets("Sheet1").Cells(10, 2).Value = "Checked" Else Worksheets("Sheet1").Cells(10, 15).Value = ""
If CheckBox3.Value = True Then Worksheets("Sheet1").Cells(10, 3).Value = "Forwarded" Else Worksheets("Sheet1").Cells(10, 14).Value = ""
If CheckBox4.Value = True Then Worksheets("Sheet1").Cells(10, 4).Value = "Notified" Else Worksheets("Sheet1").Cells(10, 16).Value = ""

End Sub

userform checkbox vba excel
2 Answers

You should be able to build a string in a variable and then populate the cell with it:

Private Sub CommandButton1_Click()
  Dim Value as String
  If CheckBox1.Value Then Value = "Checked"
  If CheckBox3.Value Then Value = Value & IIf(Value = "", "", " / ") & "Forwarded"
  If CheckBox4.Value Then Value = Value & IIf(Value = "", "", " / ") & "Notified"
  Worksheets("Sheet1").Cells(10, 2).Value = Value
End Sub

Note the use of IIf to determine whether to conditionally add the separator (" / ") when appropriate, rather than trying to trim it off later.

user_65405910

This is the optimal solution that i could find. I look forward for corrections but so far it works according to my needs.

Private Sub CommandButton1_Click()
        Dim Value As String
      If CheckBox1.Value Then Value = "Checked"
      If CheckBox3.Value Then Value = "Forwarded"
      If CheckBox1.Value And CheckBox3.Value Then Value = IIf(Value = "", "", "Checked/") & "Forwarded"
      If CheckBox4.Value Then Value = "Notified"
      If CheckBox3.Value And CheckBox4.Value Then Value = IIf(Value = "", "", "Forwarded/") & "Notified"
      If CheckBox1.Value And CheckBox4.Value Then Value = IIf(Value = "", "", "Checked/") & "Notified"
      If CheckBox1.Value And CheckBox3.Value And CheckBox4.Value Then Value = IIf(Value = "", "", "Checked/Forwarded") & "Notified"
      Worksheets("Sheet1").Cells(10, 2).Value = Value
    
    End Sub

Thank you.

user_163761850
Related