Situation: I have a userform as shown below:
current solution when i click OK, the following result is displayed
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.
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
CodePudding user response:
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.
CodePudding user response:
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.