+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 21

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

  1. #1
    Registered User
    Join Date
    03-23-2015
    Location
    India
    MS-Off Ver
    MS Office 2007
    Posts
    16

    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
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,065

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

    This code for Submit Button

    Please Login or Register  to view this content.
    Kind regards
    Leo

  3. #3
    Registered User
    Join Date
    03-23-2015
    Location
    India
    MS-Off Ver
    MS Office 2007
    Posts
    16

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

    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)

  4. #4
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,065

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

    Good Morning Mukhtar,

    with no other data below the table this can do

    Please Login or Register  to view this content.
    Kind regards
    Leo

  5. #5
    Registered User
    Join Date
    03-23-2015
    Location
    India
    MS-Off Ver
    MS Office 2007
    Posts
    16

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

    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)

  6. #6
    Registered User
    Join Date
    03-23-2015
    Location
    India
    MS-Off Ver
    MS Office 2007
    Posts
    16

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

    Dear Leo Taxi,

    Still need a little help on this,
    You have already solved my problem, but I need a little extension with this.
    Attached Excel file named "Yarn"
    It is the same as previous one with little difference.
    There is "Purchase.Roll" Sheet which I need you to look at. It has a Userform1 also.
    Before that please look at the "Read me first" Sheet where I have explained everything in detail.
    Hope you will help me.

    Thanking You,

    Regards,
    Mukhtar56
    Attached Files Attached Files
    Last edited by mukhtar56; 02-25-2018 at 10:59 PM. Reason: attachment

  7. #7
    Registered User
    Join Date
    03-23-2015
    Location
    India
    MS-Off Ver
    MS Office 2007
    Posts
    16

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

    Dear Leo Taxi,

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

    Regards,
    Mukhtar56

  8. #8
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,065

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

    Maybe

    Please Login or Register  to view this content.
    Kind regards
    Leo

  9. #9
    Registered User
    Join Date
    03-23-2015
    Location
    India
    MS-Off Ver
    MS Office 2007
    Posts
    16

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

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

    Regards,
    Mukhtar56

  10. #10
    Registered User
    Join Date
    03-23-2015
    Location
    India
    MS-Off Ver
    MS Office 2007
    Posts
    16

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

    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)
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,065

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

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

    Kind regards
    Leo
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    03-23-2015
    Location
    India
    MS-Off Ver
    MS Office 2007
    Posts
    16

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

    Dear LeoTaxi,

    I appreciate your help,
    Thank you very very much,

    Regards,
    Mukhtar56

  13. #13
    Registered User
    Join Date
    03-23-2015
    Location
    India
    MS-Off Ver
    MS Office 2007
    Posts
    16

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

    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
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    03-23-2015
    Location
    India
    MS-Off Ver
    MS Office 2007
    Posts
    16

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

    Dear Leo Taxi,
    Please check last letter,

    Regards,
    Mukhtar

  15. #15
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,065

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

    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

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1