Userform add multiple rows of records at once on any active sheet

[benntw]

I'm not sure if this can be done or not. I have dug through the threads and google looking for code. I have multiple rate sheets that are named by client. On the sheets are the equipment we are going to use along with the rates we are going to charge. I don't want to just copy and paste the entire equipment list and change the rates for the pieces we are going to use.

What I have built already.

My userform has 120 pieces of equipment on it.
All the textboxes populate when I open it from an equipment list from another sheet.
I have checkboxes next to every piece of equipment.
Empty textboxes for internal rates
Empty Textboxes with Client rates
Empty Textboxes with % Markups

What I want to do with code.

I need to be able to check every piece of equipment that I want to put on that active rate sheet.
Fill out the 3 empty textboxes.
Press the command button and have it add all of the pieces of equipment in the textboxes from the checkboxes that are true.
And if possible add how many pieces of each class needed. Leave it as 1 piece for default.


I have never built a userform to add multiple rows at once and cannot find anyone out there that has either. Is this even possible ?

Appreciate the help.

 

[Norie]

How have you named the controls on the userform?

How exactly is the equipment listed? Labels? Captions of checkboxes?

Have you considered a different approach?

For example you could have a combobox that lists the equipment, 3 textboxes (Internal Rate, Client Rate & % Markup), a command button and a listbox.

The user selects a piece of equipment from the combobox, fills out the 3 textboxes as required and presses the command button.

When the command button is pressed the selected piece of equipment and the values from the textboxes are added to the listbox and the combobox/textboxes are 'reset' for the next entry.

This method could be used to build up a list of equipment/rates etc in the listbox and that could easily be transferred in one go to a worksheet.

 

[benntw]

I have not added command buttons yet. I just have the sheet made.
I have considered a different approach , but I was looking for efficiency.
I want to have all of it listed on the form so the user can just look at what they want, select it, and add the rates.
I have a checkbox next to each textbox field(That cannot be edited). If you check the box then I need it to add that description to the rate sheet.

I guess you picture it like a checklist a mechanic would use. They go the paper and check each box that applies and writes their comments. This is the idea I want to use for the equipment listed. Go down the list check the equipment needed and add the rates. Press the button once and have all of those added to the sheet and then clear all the checkboxes.

 

[Norie]

Not sure having hundreds of controls on a userform is the most efficient approach and I know it's not going to be easy to work with that many controls in an efficient manner unless you use some sort of naming convention.

Why do you have a checkbox next to each textbox field? How can the user enter the rate(s)? Are they not supposed to do that?

By the way, if you wanted the user to be able to view a list of all the equipment the best (most efficient?) control for that would probably be a listbox.

 

[benntw]

The user enters the rate right next to the equipment textbox. Left to right Checkbox (User to select the equip to add) Equipment textbox (view only) Internal rate . User enters into textbox. Cleint Rate . User enters into textbox.

They might select 5 pieces of equipment or 50 pieces of equipment. I am not seeing how a listbox would work if they have to add rates to each piece they selected. You would have to select a piece from the list box, then go and then go fill out rates then back down to the list box and then back up to the textboxes. I don't see that being more efficient than have everything listed out and with checkboxes and textboxes next to each piece of equip. I need it to be just like a checkoff list. You scroll down check a box enter the rates and press a button. Bam! it's added to the sheet.

 

[My Aswer Is This]

I agree with Norie. If your looking for efficiency

You said:
I have considered a different approach , but I was looking for efficiency

I would not be using 120 TextBoxes and 120 check boxes

See writing all the code to associate all the checkboxes with the text boxes can be a chore.

And Norie asked how you had named all these check boxes and textboxes.

Normally I suggest keep their default names like TextBox1 and ChecKBox1

This way we can write a loop to run through all those controls.

Do not name the TextBoxes and checkboxes Car Apple Cake etc

Nories way of doing this sounds like a good approach and only requires a little bit of coding.

You said you had already wrote code to load all the textboxes with values. Please share with us that code.

So we can see how your doing that.

 

[Norie]

benntw

Why couldn't you scroll down a listbox, select the required piece of equipemnt, enter the rates in textboxes situated above the listbox, click a button and 'bam' have the selected equipment, entered rates etc transferred to a sheet then have the listbox/textboxes reset ready for the user to select the next piece of equipment.

Or how about this, 2 listboxes, a set of textboxes for rates etc and a command button.

User selects piece of equipment from first listbox, enters rates in textboxes, clicks command button, selected equipment and rates are then transferred to the second listbox.

User repeats this process until they have a full list of required equipment in second listbox that can then easily be transferred to a sheet.

You could also have a button to remove equipment from the second listbox if required.

 

[benntw]

I planned on leaving the names textbox1 and checkbox1 etc. Each number of the textbox lines up with the number of the checkbox. I didn't create one code for loading them with the names. I have 120 line items in my code to load the textboxes as follows.

TextBox1.Value = Sheets("Equip").Range("B1").Value

 

[Norie]

If you stick with the textboxes you could use this to load the them.

For I = 1 To 120
    Me.Controls("TextBox" & I).Value = Sheets("Equip").Range("B" & I).Value
Next I

PS If these textboxes are not going to be edited then you may as well use labels.

 

[My Aswer Is This]

If your equipment list is in column "A" of a sheet named "Equipment"

You can load all your equipment into a listbox like this:

Private Sub UserForm_Initialize()
ListBox1.List = Sheets("Equipment").Range("A1:A" & Sheets("Equipment").Cells(Rows.Count, "A").End(xlUp).Row).Value
End Sub