DataEntry using VBA - multiple lines at once

[xdciro]

Hello,

I would like to create a data entry form for a table which would create multiple lines at once, depending on the ticked check boxes that would be in the form. The form would basically consist of 2 parts;

In the first part i would be able to input data in text boxes (e.g. for columns A, B and C) that will stay the same for all created rows of the form.

In the second part of the form there would be many check boxes, which i would be able to tick. (The corresponding text for the ticked boxes would display in different row, e.g. in column D.). These check boxes would be the variable part of the finishing table.

So each ticked check box in the form creates a new row in the table that consist (!) of the "constant" data inputed for the columns A, B and C and (2) the text for corresponding ticked check box in column D.

So the question is if it is possible to do this with VBA and how? ( i mean of course it's possible, the problem is that I just don't have enough knowledge yet to do it by myself, because I'm a total beginner i would need help only with the "coding" part, not with a form "editor".

Thanks in advance and kind regards

 

[Norie]

Instead of the 3 textboxes and multiple checkboxes you could use 3 textboxes and a listbox.

The 3 textboxes would work as you describe and the listbox would contain a list of each item/text that would have been associated with the checkboxes and would be set up so each row would have it's own checkbox.

It would then be easy to loop through the listbox to check which items the user has selected and for each transfer the item and the values from the textboxes to the worksheet.

To see what I mean try this.

1 Open the VBE (ALT+F11).

2 Add a userform (Insert>Userform).

3 Add 3 textboxes in a row, or column, to the userform.

4 Next to the textboxes add a listbox.

5 Add a command button somewhere on the form, eg below the textboxes.

6 Now, assuming all the controls have their default names add this code to the userform module.

Option Explicit

Private Sub CommandButton1_Click()
Dim rng As Range
Dim I As Long

    Set rng = Range("A" & Rows.Count).End(xlUp).Offset(1)
    
    For I = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(I) = True Then
            rng.Resize(, 4).Value = Array(TextBox1.Value, TextBox2.Value, TextBox3.Value, ListBox1.List(I))
            Set rng = rng.Offset(1)
        End If
    Next I
    
End Sub

Private Sub UserForm_Initialize()
    
    With ListBox1
        .List = Array("Item1", "Item2", "Item3", "Item4", "Item5", "Item6")
        .ListStyle = fmListStyleOption
        .MultiSelect = fmMultiSelectMulti
    End With
    
End Sub

7 Open the userform (Run>Run UserForm or F5) and give it a try.

 

[xdciro]

Thanks for the fast reply. I agree, the listbox is a much better idea because it should be much easier to update it with new "items" when needed.

Thank you once again KR

Instead of the 3 textboxes and multiple checkboxes you could use 3 textboxes and a listbox.

The 3 textboxes would work as you describe and the listbox would contain a list of each item/text that would have been associated with the checkboxes and would be set up so each row would have it's own checkbox.

It would then be easy to loop through the listbox to check which items the user has selected and for each transfer the item and the values from the textboxes to the worksheet.

To see what I mean try this.

1 Open the VBE (ALT+F11).

2 Add a userform (Insert>Userform).

3 Add 3 textboxes in a row, or column, to the userform.

4 Next to the textboxes add a listbox.

5 Add a command button somewhere on the form, eg below the textboxes.

6 Now, assuming all the controls have their default names add this code to the userform module.

Option Explicit

Private Sub CommandButton1_Click()
Dim rng As Range
Dim I As Long

    Set rng = Range("A" & Rows.Count).End(xlUp).Offset(1)
    
    For I = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(I) = True Then
            rng.Resize(, 4).Value = Array(TextBox1.Value, TextBox2.Value, TextBox3.Value, ListBox1.List(I))
            Set rng = rng.Offset(1)
        End If
    Next I
    
End Sub

Private Sub UserForm_Initialize()
    
    With ListBox1
        .List = Array("Item1", "Item2", "Item3", "Item4", "Item5", "Item6")
        .ListStyle = fmListStyleOption
        .MultiSelect = fmMultiSelectMulti
    End With
    
End Sub

7 Open the userform (Run>Run UserForm or F5) and give it a try.