Capture value of corresponding Textboxes of a Checked Checkboxes to Worksheet

[rakotonirinas]

Hello everyone,

I would like some help please in capturing the data within a textbox next to a checkbox to a worksheet. So if 4 textboxes are checked then there will be 4 rows of data - starting from the value of the textbox next to the first checked checkbox - on the worksheet and so on. No box checked means that no data will be copied and paste on the worksheet.

I have tried to do a nit of macro but it's useless and I don't think it fits the purpose. Your help is much appreciated.

As you can see on the Database worksheet (sample attached). I would like to both capture the value within the next checked box but also the name of the checkbox itself.

Thank you

Capture.PNG

[Marc L]

Hi !

Attach a picture of the userform filled with data and some checked textboxes
and the expected result workbook accordingly, will be easier for any helper …

[rakotonirinas]

I have resubmitted a new image of the form when completed and its rendition when captured.

The checkbox will eventually correspond to components or sub parts which amounts to 12 items.

Screen Shot 2019-05-28 at 21.06.45.png

The real purpose of the userform and workbook is really to capture on a daily or weekly basis revenues made by an agent and store that on the system as a report. Therefore, The capturing list can be cleared when we saved the data for one agent.


Thank you.

[mjr veverka]

... if 4 textboxes are checked then there will be 4 rows of data ... etc.

This code is not for this form (?):

1. You have 6 (not 4 textboxes, but) CheckBoxes to check
2. Where it is TextBox number: 13, 14, 15, 16, etc. ?

For j = 13 To 24
    '...
    Addto.Offset(, 6) = Me.TextBox("TextBox" & j).Value ' ?!
    Addto.Offset(, 7) = ctl.Name
    '...
Next

3. and not as above "Me.TextBox("TextBox" & j)", but :

... Me.Controls("TextBox" & j)...

4. "For ... Next" loops are wrong constructed, maybe it's about something like that ?:

    With Sheets("Database")
        For i = 1 To 6
            If Me.Controls("CheckBox" & i).Value Then
                With .Range("A" & .Rows.Count).End(xlUp).Offset(1)
                    .Resize(, 6) = Array(cmbDate.Value, TextBox1.Value, TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value)
                    For j = 13 To 24 '=> ???!!!
                        .Offset(, 6) = Me.Controls("TextBox" & j).Value '???!!!
                    Next
                    .Offset(, 7) = ctl.Name '???!!!
                End With
            End If
        Next
    End With

5. Where are ?:

Dim ctl As Control
Dim chkBox As CheckBox
Dim lRow As Single

[rakotonirinas]

Apologies porucha vevrku and thank you for your help. This is only a sample one which I quickly created for the query purpose based on the project I'm doing right now.
To answer your questions:
1. 4 was given only as an example. I could have given 2 or 3 or simply 1 but for the purpose of the example, I said if 4 checkboxes [for instance].
2. Again, the real workbook will have 24 textboxes in which 12 are placed next to 12 checkboxes. I did forget to change this and should have read

For j = 7 To 12

(for the purpose of the example).
3. Amended 3
4. Not sure I understand this one
5. I declared some variables for some tests but didn't need it at the end.

[mjr veverka]

Ok, maybe that's what you meant:

Private Sub cmbUpdate_Click()
    Dim i As Byte
    
    For i = 1 To 6
        With Me.Controls("CheckBox" & i)
            If .Value Then
                If Trim(Me.Controls("TextBox" & CStr(i + 5)).Value) <> "" Then
                    With Sheets("Database")
                        With .Range("A" & .Rows.Count).End(xlUp).Offset(1)
                            .Resize(1, 6) = Array(cmbDate.Value, Me.TextBox1.Value, Me.TextBox2.Value, Me.TextBox3.Value, Me.TextBox4.Value, Me.TextBox5.Value)
                            .Offset(0, 6) = Me.Controls("TextBox" & CStr(i + 5)).Value
                            .Offset(0, 7) = Me.Controls("TextBox" & CStr(i + 5)).Name ' .Offset(0, 7) = TextBox" & CStr(i + 5)
                        End With
                    End With
                    Me.Controls("TextBox" & CStr(i + 5)).Value = ""
                End If
                .Value = 0
            End If
        End With
    Next
End Sub

[rakotonirinas]

porucha vevrku. Thank you ever so much for your help. Your code worked like magic. I didn't know we could achieve so much with a little coding.
I've tried to make sense of everything though but lost in understanding the steps. Would you be able to help please explain what the steps means, particularly the ones coloured in green.
Much appreciated.

Private Sub cmbUpdate_Click()
    Dim i As Byte
    
    For i = 1 To 6
        With Me.Controls("CheckBox" & i)
            If .Value Then
                If Trim(Me.Controls("TextBox" & CStr(i + 5)).Value) <> "" Then
                    With Sheets("Database")
                        With .Range("A" & .Rows.Count).End(xlUp).Offset(1) 'How would you explain the Offset here? 
                            .Resize(1, 6) = Array(cmbDate.Value, Me.TextBox1.Value, Me.TextBox2.Value, Me.TextBox3.Value, Me.TextBox4.Value, Me.TextBox5.Value)
                            .Offset(0, 6) = Me.Controls("TextBox" & CStr(i + 5)).Value
                            .Offset(0, 7) = Me.Controls("TextBox" & CStr(i + 5)).Name ' .Offset(0, 7) = TextBox" & CStr(i + 5)
                        End With
                    End With
                    Me.Controls("TextBox" & CStr(i + 5)).Value = ""
                End If
                .Value = 0
            End If
        End With
    Next
End Sub

I guess what I am trying to understand is that how would I write the same code if the database worksheet, for instance, started from E10 (header) instead of A1 so therefore the range where the data will be captured will start from E11:L11.

[rakotonirinas]

Hello,

This is just a real version of what I asked earlier on. Upon applying porucha vevrku's code, which works perfectly in my initial workbook, I didn't manage to get the same result on my real data workbook.

I reckon I'm writing the code incorrectly as per below. Would anyone help fix where I get it wrong please?

Thank you so much.

Statement.PNG

Private Sub cmdPublish_Click()

Dim inv2Wks As Worksheet
Dim lRow As Long
Dim i As Byte

Set inv2Wks = Sheets("Inv_doc")
lRow = inv2Wks.Range("K" & Rows.Count).End(xlUp).Offset(1)

Application.ScreenUpdating = False

    For i = 1 To 12
        With Me.Controls("CheckBox" & i)
         If .Value Then
            If Trim(Me.Controls("TextBox" & CStr(i + 12)).Value) <> "" Then
                With inv2Wks
                 With .Range("A" & Rows.Count).End(xlUp).Offset(1)
                      .Offset(17, 10) = Me.Controls("Textbox" & CStr(i + 12)).Value
                 End With
                End With
            End If
            .Value = 0
         End If
        End With
    Next
    
Application.ScreenUpdating = True

End Sub

[rakotonirinas]

Anyone could enlighten me on this one please?

[mjr veverka]

Try this way:

Private Sub cmdPublish_Click() 'CommandButton3_Click
    Dim i As Byte
    
    Application.ScreenUpdating = False
    
    For i = 1 To 12
        With Me.Controls("CheckBox" & i)
            If .Value Then
                If Trim(Me.Controls("TextBox" & CStr(i + 12)).Value) <> "" Then
                    With Sheets("Inv_doc")
                        'Commands for the sheet
                        With .Range("A17").CurrentRegion
                            'Commands for the table
                            '...
                            'Data transfer to the table
                            With .Rows(.Rows.Count).Offset(1)
                                .Cells(1).Value = Me.Controls("Textbox" & CStr(i)).Name
                                .Cells(2).MergeArea.Cells(1).Value = Me.Controls("Textbox" & CStr(i)).Value ' ... :)
                                .Cells(11).Value = Me.Controls("Textbox" & CStr(i + 12)).Value
                            End With
                        End With
                    End With
                End If
                .Value = False
            End If
        End With
    Next
    
    Application.ScreenUpdating = True
End Sub

You must keep the gap of one row and one column around the table beginning in "A17".


Ps.: GetRebateSum ... what is this ... hmm .. invention ? What was the purpose ? ...

[rakotonirinas]

Thanks porucha vevrku. I will test and let you know. As for the GetRebateSum, it's the sum of commission and agent has made from the sale for products.

[rakotonirinas]

porucha vevrku. It was exactly what I was looking for. Thank you very much.
Last question on this please. I want to change the value in the Range A17 as per

.Cells(1).Value = Me.Controls("Textbox" & CStr(i)).Name

to Item number instead of the checkbox number. So it starts with Item 1 and finishing with Item # whichever the number of checked boxes. Could you help please?

Thank you very much.

[rakotonirinas]

Hello,
Any suggestion on this?
Much appreciated.

[mjr veverka]

1. In this fragment of code there is no object "CheckBox" but only "TextBox"
2. What is this "Item" for you, or is it perhaps "i" enumeration from the loop ? ...
If so, then ".Cells(1).Value = CStr(i)"

[rakotonirinas]

Hello,

1. In this fragment of code there is no object "CheckBox" but only "TextBox"

- Not sure I completely understand this statement.

2. Its a classic itemisation of the entry in the columns B and K starting from B18 and K18, as per the image below
Screen Shot 2019-05-30 at 21.47.45.png

I did

.Cells(1).Value = "Item" & CStr(i)

but it didn't start from Item 1 if, for instance, the checkbox August was selected first which came back as Item 4 where I would like it to be Item 1.

Thank you.

[mjr veverka]

... the checkbox August was selected first which came back as Item 4 where I would like it to be Item 1 ...

I do not know what is going on, your "checkboxes" have the following names: CheckBox1, CheckBox2, CheckBox3, etc.
Where did you get the names of the months ?

[rakotonirinas]

Screen Shot 2019-05-31 at 00.56.44.png

Would that help you understand? Actually I said it incorrectly, the checkbox 4 with the corresponding textbox 4 in which August 2018 was entered as a value. This will also correspond to textbox16 for the amount value. So with the current code, if I tick (or check) checkbox 4 that will write in the worksheet
A18: checkbox 4, B18 (merged cells): August 2018, K18: £210 (as an example).
And what I would like to achieve is the same but instead of naming the checkbox in in column A18, it would be "Item 01". The code you gave earlier on gives #4 on A18 where I would like it to be always Item 1 and the next Cell down A19 will be Item 2 if there is something in the B19 and K19 etc.

Thank you.

[mjr veverka]

... it would be "Item 01" ... and the next Cell ... will be "Item 02" ...

You have a strange way of transmitting relevant and full information at the very end, try:

Private Sub cmdPublish_Click() 'CommandButton3_Click
    Dim i As Byte, j As Byte
    
    Application.ScreenUpdating = False
    
    For i = 1 To 12
        With Me.Controls("CheckBox" & i)
            If .Value Then
                If Trim(Me.Controls("TextBox" & CStr(i + 12)).Value) <> "" Then
                    With Sheets("Inv_doc")
                        'Commands for the sheet
                        With .Range("A17").CurrentRegion
                            'Commands for the table
                            '...
                            'Data transfer to the table
                            j = j + 1
                            With .Rows(.Rows.Count).Offset(1)
                                .Cells(1).Value = "Item " & Format(j, "00") 'Me.Controls("Textbox" & CStr(i)).Name
                                .Cells(2).MergeArea.Cells(1).Value = Me.Controls("Textbox" & CStr(i)).Value ' ... :)
                                .Cells(11).Value = Me.Controls("Textbox" & CStr(i + 12)).Value
                            End With
                        End With
                    End With
                End If
                .Value = False
            End If
        End With
    Next
    
    Application.ScreenUpdating = True
End Sub

[rakotonirinas]

porucha vevrku. You're awesome. Thanks so much. That did the trick. And please accept my apologies for all the inconsistencies in my explanation and requests.

Last request on this please. Would it be possible to have a sum (total) on the userform (under Inv. total inc. VAT - which should read exc. VAT) of any corresponding textboxes if one or more checkbox is checked?

Thank you.

[mjr veverka]

You can do it this way (the macros have been slightly changed) see attachment.
However, you must take into account that I have other system settings (decimal separator, thousand separator, etc.) and in some situations the verification of numbers may not work as it should.

[rakotonirinas]

Hello porucha vevrku,

Amazing job you done here. Thank you. Am I right to suggest that initially all checkboxes will be checked and the calculation starts from the sum of all corresponding textboxes and will reduce as few or some checkboxes are unchecked? At least that is the behaviour I get right now.