Get Values in Text Box based on Check box Selection

[Dheepak]

Hi All,

Need a help on resolving the below scenario. I have a column A with Months and Column B with unique products ID's. Each months are having checkbox and i need based on the check box selection, the text box and excel sheet should be updated with the all the products from that specific month. Also once its unchecked, the related products for that month should not be updated in that text box. Can anyone please help to resolve this issue. Below are the details.

[TABLE="width: 200"]
<tbody>[TR]
[TD]MONTH[/TD]
[TD]PRODUCT[/TD]
[/TR]
[TR]
[TD]APR[/TD]
[TD]PR1[/TD]
[/TR]
[TR]
[TD]APR[/TD]
[TD]PR2[/TD]
[/TR]
[TR]
[TD]APR[/TD]
[TD]PR3[/TD]
[/TR]
[TR]
[TD]APR[/TD]
[TD]PR4[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]PR5[/TD]
[/TR]
[TR]
[TD]MAY[/TD]
[TD]PR6[/TD]
[/TR]
[TR]
[TD]JUN[/TD]
[TD]PR7[/TD]
[/TR]
</tbody>[/TABLE]

In userform, i have check box for APR, MAY & JUN. Once i checked the APR Check box, text box1 should get updated with the format by adding semicolon in between like PR1;PR2;PR3;PR4 in text box and in excel sheet, it should get updated with each row. If both apr & may check box selected, text box1 should get updated with PR1;PR2;PR33;PR4;PR5;PR6 along with each row in excel sheet. If Apr check box is unchecked, then i would like to see the text box with only may month entries which is PR5;PR6 and the same in excel sheet.

Please let me know how can we achieve this via VBA

 

[sijpie]

What do you mean with "and in excel sheet, it should get updated with each row."?

the rest is quite clearly explained and can easily be done

 

[Dheepak]

What do you mean with "and in excel sheet, it should get updated with each row."?

the rest is quite clearly explained and can easily be done

Hi sijpie,

Thank you for the reply. I meant like once i click the April check box, i need textbox1 to update with PR1;PR2;PR3;PR4 in this format as well column D5 with PR1 and D6 with PR2 and D7 with PR3 and D8 with PR4. If i check the may check box and if i uncheck the April check box, i would like to see textbox1 to update with PR5;PR6 and D5 with PR5 and D6 with PR6. Textbox i need it with ";" based on the check box selection. Same way i would need the data from D5 to get changed based on the check box selection. Please help me to know how can we get this done.

 

[sijpie]

Dheepak, is your userform a real pop-up userform, or is it just an area in your workbook?

 

[Dheepak]

Dheepak, is your userform a real pop-up userform, or is it just an area in your workbook?

Hi sijpie, Its an area in workbook. Ideally, all the ActiveX Check box controls are on left side and in center the text box and below to the text box, i would need the columns to be updated based on the check box selection

 

[sijpie]

The following code should do the trick for the month april. You would need to add a copy for each of the checkboxes (right click on th e checkbox and select view code). Then modify the name of the month

Private Sub CheckBox1_Click()
    'Checkbox for April
    Dim vMP As Variant
    Dim i As Long, UB As Long
    Const sMONTH As String = APR ​'<<<< Modify this for each checkbox
    
    'load the two columns (months and product) into array for fast processing
    vMP = Me.Range("A1").CurrentRegion.Value
    'get number of rows
    UB = UBound(vMP, 1)
    
    Select Case CheckBox1.Value
        Case True   'ticked
            For i = 1 To UB
                'find the month in the array
                If StrComp(vMP(i, 1), sMONTH) = 0 Then
                    'add the product to the textbox
                    If Len(Me.TextBox1.Value) Then Me.TextBox1.Value = Me.TextBox1.Value & ";"
                    Me.TextBox1.Value = Me.TextBox1.Value & vMP(i, 2)
                End If
            Next i
        Case False  'unticked
            For i = 1 To UB
                'find the month in the array
                If StrComp(vMP(i, 1), sMONTH) = 0 Then
                    'remove the product from the textbox
                    Me.TextBox1.Value = Replace(Me.TextBox1.Value, vMP(i, 2), "")
                    Me.TextBox1.Value = Replace(Me.TextBox1.Value, ";;, "";")
                End If
            Next i
    End Select
End Sub