Userform Textboxes and Comboboxes to Populate certain cells across 100 columnsx1colatatime

Hi All, Im a vba code stealing/altering noob that is propbably on the wrong track...?

I'm trying to figure out how to use command button click on a userform to transfer textbox and combobox data into specific cells across 100 columns - 1 column at a time. not every cell will contain data on every command click hence needing to be in specific cells and not find next cell or next column item.

The userform needs to re-set and next column selection begin up until a quantity is reached (quantity is in Cell B22)

Can anybody please point me in the right direction?

Cheers in advance


Private Sub CommandButton3_Click()
Dim i As Integer
For i = 3 To 103
Dim ws As Worksheets
Set ws = Worksheets("Frame Input")
With ws
.Cells(C23, i).Value = TextBox5.Text
.Cells(C25, i).Value = TextBox3.Text
.Cells(C28, i).Value = ComboBox1.Text
.Cells(C29, i).Value = TextBox1.Text
.Cells(C30, i).Value = TextBox2.Text
.Cells(C24, i).Value = ComboBox19.Text
.Cells(C31, i).Value = ComboBox4.Text
.Cells(C39, i).Value = ComboBox2.Text
.Cells(C51, i).Value = ComboBox3.Text
.Cells(C35, i).Value = ComboBox24.Text
.Cells(C32, i).Value = ComboBox25.Text
.Cells(C63, i).Value = ComboBox5.Text
.Cells(C115, i).Value = ComboBox7.Text
.Cells(C167, i).Value = ComboBox8.Text
.Cells(C371, i).Value = ComboBox15.Text + " " + ComboBox16.Text
.Cells(C323, i).Value = ComboBox12.Text
.Cells(C347, i).Value = ComboBox13.Value
.Cells(C378, i).Value = ComboBox14.Text
.Cells(C385, i).Value = ComboBox26.Text
.Cells(C392, i).Value = ComboBox17.Text + " " + ComboBox18.Text
.Cells(C219, i).Value = ComboBox9.Text
.Cells(C271, i).Value = ComboBox10.Text
.Cells(C471, i).Value = ComboBox11.Text
.Cells(C472, i).Value = ComboBox20.Text
.Cells(C473, i).Value = ComboBox21.Text
.Cells(C474, i).Value = ComboBox22.Text
.Cells(C475, i).Value = ComboBox23.Text
End With
Next i

End Sub

It may also be worth revising your structure, if you have 100 columns that don't always need populating, then there may be a better way of laying out your workbook

Originally Posted by Kyle123

It may also be worth revising your structure, if you have 100 columns that don't always need populating, then there may be a better way of laying out your workbook

Yeah since dipping my toes into vba I’m finding I have to revise layouts to suit easily written codes however with this on each item is a column and the components in each item are the cells it would be 6 in one or half dozen in the other by switching them - unless I go into making 100 worksheets and reference but that seems really messy and labor intensive?

Originally Posted by mikerickson

Rather than looping, you could use code like

Please Login or Register  to view this content.

Thanks for the tip mike I really appreciate it. Forgive my lack of knowledge but do you mean for not looping do I keep integer etc at the start of the code etc?

It would be much easier if you post a sample workbook:

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Also, Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 6)

Done, Sorry told you i was a noob.

Now i know, unlike my vba writing...

Thanks for the heads up

Hi Mike

Yes I would like the userform to fill in one column at a time in the referenced cells. As the amount of cells required scrolling over is alot im sure a correctly coded vba userform would reduce data input 10 fold.

So the userform would need to reload and run again (I think using a sequential item number textbox?) up to 100 times but finish after a pre-determined quantity as noted in the attached excel file notes.

I hope its easily followed??

Either way (if its achievable or not) i appreciate you looking at it.

Apologies for the inconvenience if any.

Hi Dotchiejack, thank you very much as it worked a treat! albeit on your excel file you sent me

My next hurdle is assigning the userform to be controlled by a command button on a different worksheet to drive the input of items you so kindly put together for me (as the Frame Input worksheet will be hidden, so no access to the button). Again my skills in coding are very lacking (I am the old dog you cannot teach tricks too...) lol

I really appreciate everyones help on this - its going to make my job easier

Thanks dotchiejack, appreciate it mate. I found when I copied your userform text/combo-boxes into a userform I already had, it kept the Rowsource input data in my orioginal userform text&combo boxes and applied them to the copied text&combo-boxes (maybe an FYI but you probably already know that one?).

Anywho I am now trying to intergrate the checkboxes to give a value of yes or no (again per column in a particular cell) and I cant find any examples in the forum or online.. can you help once more? please..

Thanks Dotchiejack, I tried importing the code in but unfortunately it isnt working, am i missing something?

The extract is the last part of the add button code..

Please Login or Register  to view this content.