DataEntry using VBA - multiple lines at once

xdciro

New Member
Joined
Dec 7, 2016
Messages
7
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.

Code:
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.
 
Upvote 0
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.

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,217,673
Messages
6,137,916
Members
450,098
Latest member
Ikmal Sabri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top