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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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.
 

xdciro

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

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

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
Top