Get Values in Text Box based on Check box Selection

Dheepak

Board Regular
Joined
Aug 25, 2013
Messages
59
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.

MONTHPRODUCT
APRPR1
APRPR2
APRPR3
APRPR4
MAYPR5
MAYPR6
JUNPR7

<tbody>
</tbody>

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
 
Last edited:

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,524
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

Board Regular
Joined
Aug 25, 2013
Messages
59
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,524
Dheepak, is your userform a real pop-up userform, or is it just an area in your workbook?
 

Dheepak

Board Regular
Joined
Aug 25, 2013
Messages
59
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,524
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

Rich (BB code):
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
 

Forum statistics

Threads
1,077,855
Messages
5,336,780
Members
399,102
Latest member
chudson1

Some videos you may like

This Week's Hot Topics

Top