Combobox decides path of textbox value to particular cells in excel, Need VBA Code

Dear Forum Members,

I need your help regarding Excel VBA coding.

In sheet1

There are 9 coloumns A1, A2, A3, B1, B2, B3, C1, C2 & C3.

There are 3 Texboxes nanmed (Red, Blue & Green).

There is 1 Combobox containing Categories "A" "B" & "C".

When any numerical or Text value is filled in any or all of the 3 Textboxes (Red, Blue or Green), this happens on clicking the Submit button.

If "A" is selected in the Combobox, then these value from the 3 Textboxes goes into cells A1, A2 & A3 respectively.

If "B" is selected in the Combobox, then these value from the 3 Textboxes goes into cells B1, B2 & B3 respectively.

If "C" is selected in the Combobox, then these value from the 3 Textboxes goes into cells C1, C2 & C3 respectively.

After clearing the form and when new entry is made in the Boxes, The next new entry of new value goes into new Row.

Excel sheet1 contains complete columns and userform.
Sample Sheet shows what I need. (It is in Normal Excel with Normal Formulas)

I need Excel VBA code for this.( I use MS Excel 2007)

(Its like this, the combobox decides the path of Textbox values to the specific cells. The values goes into the cells from the Textboxes and not from cells to textboxes)

Hope some kind person will help me out with this.

Lot of thanks in advance.

Regards,
Mukhtar

This code for Submit Button

Please Login or Register  to view this content.

Kind regards
Leo

Dear Leo,
Lots, Lots & lots of thanks for the code. I am highly grateful to you, You solved my problem.
The code is alright and working properly, but I still ask you a favor,
When I select "A" in the Combox The values in the Textbox 1,2,& 3 move into cell columns A1, A2 & A3. respectively, but when I select "B" in the Combox The values in the Textbox 1,2,& 3 move into cell columns B1, B2 & B3. but in the same row I want that when I press the CommandButton I want the values to go into the next row, no matter the adjacent cells to A1,A2, A3 are empty. I mean that for every submission it should go into next Row.

Can you please add a code to the existing code that you have given me.
I am highly grateful to you. Thanks in advance

Regards,
Mukhtar56
(Country - India)

Good Morning Mukhtar,

with no other data below the table this can do

Please Login or Register  to view this content.

Kind regards
Leo

Dear Leo,

You hit right on the dot,
That was what I really wanted,
I am so happy,
God bless you a lot,

So much thankful to you.
Lots of blessings to you, You solved my problem, It was a real headache for me as I am weak in VBA.
Thanks once again,

Regards,
Mukhtar
(Country-India)

Dear Leo Taxi,

Please check the last letter once more, facing a small problem.

Regards,
Mukhtar56

Maybe

Please Login or Register  to view this content.

Kind regards
Leo

Dear Leo Taxi,
I am highly grateful to you, It worked fine and correct.
You are a kind soul.

Regards,
Mukhtar56

Dear LeoTaxi,

Sorry to trouble you,

Please Login or Register  to view this content.

In the code given by you on 26th Feb 2018 It works fine. In that file in the ComboBox I had sample source words List "A" To "K" from sheet4.
I tried to replace with real Yarn Names given in sheet4. When I replace the row source in sheet4 Cells("A3" to "A13") with Names from
Sheet4 Cells("C3" To "C13") I get this Error ......Run-time Error '91':.........Object Variable or with Block variable not set......

And when I debug it I get error in yellow color at this line in the code given above
mc = .Find(ComboBox1 & "1").Column

I am not understanding what has gone wrong, when everything is finely working. I just tried to replace Rowsource Alphabet words A to K with
with Text Names of the Yarns.

In Sheet4 the original Rowsource Named "Yarn" in Column A and the replaceable RowSource is given in Coloumn C.

How do I correct it.

I am sorry for troubling you and also lots of thanks for the help you have rendered to me.

Working sheet is (Sheet1)..Purchase Rolls with Userform1

Regards,
Mukhtar56

File attached Yarn (revised)

For ComboBox1, columns.count @ 3
columnWidths, 0;0;100
named range Yarn change to $A$3:$C$13

Kind regards
Leo

Dear LeoTaxi,

I appreciate your help,
Thank you very very much,

Regards,
Mukhtar56

Dear Leo Taxi,
I end up at error at this line particularly

mc = .Find(ComboBox1 & "1").Column

I copied the code as it is and even made changes as you said

but always end up with Runtime error 91 saying Object Variable or With Block Variable not set

attaching the file with Userform1 on Sheet1

Please let me know where I am going wrong.

Thanks in advance,

Regards,
Mukhtar56

Dear Leo Taxi,
Please check last letter,

Regards,
Mukhtar

your column headers have no longer A1, A2, A3 enz .....

so you have to change in sheet listsource the A-B-C enz....
to JB-W, HS-W, HP-W enz.....

after change this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Kind regards
Leo

Like this


Kind regards
Leo

Dear LeoTaxi,

Thanks a lot for the answer, It came to senses that the headers also have more importance in relation to the code.

Here i have added a column G in between the first six first identification column and the rest A1, A2, A3......... with title "Category" which contains the selected value of the Combobox1 (Example the name of the yarns "JackBrow" "Honeycom spun" etc all 11 Yarns) in the Category Column Before A1 Column.
So that I can uses Vlookup function or If Function to sort out the Data into another sheet. The Category column will act as Lookup value, without which I wil be unable to sort out the data into other sheets.

I am sorry to tell you this before.

I have a lot to prepare which you can see in the Listsource sheet. (My Preparations), I am not even at the start of it. Lot to do......

In UserForm1, the combox1 (selected value should go to Column G, row by row on each submission)

Once again Lots of thanks for the quick answers. God bless you.

Latest file attached "2018.SRVF" (title of file)

Thanks,

Regards,
Mukhtar56

Dear LeoTaxi,

Please check last letter on page 2,

Regards,
Mukhtar56

change this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Kind regards
Leo

Dear LeoTaxi,

You solved my Major problem, It might be very easy for you, but it was very tough for me because I do not know that subject VBA. I tried the whole day for the answers on the Internet but in vain. If you can see my last attachment uploaded yesterday especially the sheet "Listsource", There I have prepared a list of what I have to prepare to complete this Project. I am just at the start of it. I have lot to do yet.

There are few people on earth who are kind souls who have the patience to help out others and you are one among them. God bless you a lot. I started learning computer from 2003 onwards and I a bit slow, but never give up hope. My age is 60. So I am a slow learner.

There are lot of good books available on the Internet in PDF form especially on this site for free

www.pdfdrive.net

Can you please recommend me some good name of a book through which I can learn how to code in a better way VBA coding.

Thank a lot, It worked fine. God bless you a lot.
You are a kind person on this forum and I thank the forum owners too for such a nice forum.

Regards,
Mukhtar

No Mukhtar, can not help you with title of good book, i never readed 1 so difficul to say for me
i learned all by practice, practice and practice on forums like this one


Kind regards
Leo