+ Reply to Thread
Results 1 to 16 of 16

Add a line based on values VBA

  1. #1
    Registered User
    Join Date
    04-19-2010
    Location
    belgium
    MS-Off Ver
    Office 365
    Posts
    27

    Add a line based on values VBA

    Good day everyone,
    I need help.. :-)
    I have an excel form where requestor needs to select some values in a drop down list. If the value choosen is "Extend" or "Modify" a new line is created just below so requestor can add the product number that needs to be modified/extended.

    Two problems i cannot solve :
    1) when the line is added, the drop down list from above (the one requestor used to make his choice) is also created where i shuold have an empty cell where user will mention his product number.
    2) further down in the same column, i have another cell where requestor needs to choose in the drop down list (F or E or X), based on the answer, a new line should be created below (without the drop down list from above of course)

    this file is created as a table, but can be removed this is not an issue for me.

    Attached you will find the file with explanations

    Thank you for the help
    Guismo30
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11)
    Posts
    52,093

    Re: Add a line based on values VBA

    Administrative Note

    Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Valued Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    376

    Re: Add a line based on values VBA

    Please Login or Register  to view this content.
    Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

  4. #4
    Valued Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    376

    Re: Add a line based on values VBA


  5. #5
    Registered User
    Join Date
    04-19-2010
    Location
    belgium
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Add a line based on values VBA

    Yes sorry, Artik provided me with the original answer, I will cancel it and keep this one

  6. #6
    Registered User
    Join Date
    04-19-2010
    Location
    belgium
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Add a line based on values VBA

    Hello thanks a lot for the answer, i copied past it in my form but there is a run time error 9 "Set lo = Me.ListObjects(1)" how can i solve this ?
    on the other hand, how can i duplicate this condition for other cells in the same column ??

  7. #7
    Valued Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    376

    Re: Add a line based on values VBA

    you're from Wallonie (French speaking ?)

    I added the workbook, but I don't get a runtime error 9, That means that there is no table present in that worksheet (Did you convert the table into a normal range ?)

    how can i duplicate this condition for other cells in the same column ??
    This change-event only handles 1 cell at a time and only if that cell in within the 5th column.
    So if you modify a little bit later another E-cell, that is done by the same macro.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-19-2010
    Location
    belgium
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Add a line based on values VBA

    I just realised that my answer was not sent to you! sorry, yes i am from Liege
    what you sent me works perfectly thanks a lot another additional question, I need to get this sheet protected and i know that vba add a line does not work if protected. would you have a trick to "remove password" add the line then reset password when requestor select "extend" or "Modify" .?
    thanks again for your answer
    Guismo30

    Quote Originally Posted by bsalv View Post
    you're from Wallonie (French speaking ?)

    I added the workbook, but I don't get a runtime error 9, That means that there is no table present in that worksheet (Did you convert the table into a normal range ?)

    This change-event only handles 1 cell at a time and only if that cell in within the 5th column.
    So if you modify a little bit later another E-cell, that is done by the same macro.

  9. #9
    Valued Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    376

    Re: Add a line based on values VBA

    Add this code to "Thisworkbook", save & close the workbook, afterwards reopen.
    Please Login or Register  to view this content.
    My english is better then my french ! (I can speak it, but I can't write it)

    If you use once the "userinterfaceonly:=true" in your VBA-session, you can change everything without limits in VBA.
    So, in thisWorkbook, add an event with such a command. (with or without password)
    I suppose that some cells are not blocked you that the user can modify those cells.
    Last edited by bsalv; 10-14-2021 at 06:21 AM.

  10. #10
    Registered User
    Join Date
    04-19-2010
    Location
    belgium
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Add a line based on values VBA

    Your french is certainly much better then my Flemish! (I do not speak Flemish unfortunately)
    I have added in "ThisWorkBook" what you gave me, but it seems that it does not work (certainly because i did something wrong lol)
    Would you mind if i send you the file on an eomail address?

    Quote Originally Posted by bsalv View Post
    Add this code to "Thisworkbook", save & close the workbook, afterwards reopen.
    Please Login or Register  to view this content.
    My english is better then my french ! (I can speak it, but I can't write it)

    If you use once the "userinterfaceonly:=true" in your VBA-session, you can change everything without limits in VBA.
    So, in thisWorkbook, add an event with such a command. (with or without password)
    I suppose that some cells are not blocked you that the user can modify those cells.

  11. #11
    Valued Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    376

    Re: Add a line based on values VBA

    If you used another password than "Guismo30", you have to remove the protection first or change the password to the one you use.
    If you protect without password, then you can omit that part.

    I add the workbook in a moment.

  12. #12
    Valued Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    376

    Re: Add a line based on values VBA

    table functions in a protected sheet, that's not working.
    So a 2-way solution, check if the sheet is protected :
    - if so, add an entirerow
    - of not, add a listrow
    To protect the sheet, allow adding rows (and columns ?)

    Also check if the necessary cells (those you want to change afterwards) aren't locked.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    04-19-2010
    Location
    belgium
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Add a line based on values VBA

    I must be to old ! copied past.. not working in my file i become crazy with this line !
    may I ask you to help ? I have attached the file.. again i appologise and thanjk you for all the help
    thanks again
    guismo30
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    04-19-2010
    Location
    belgium
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Add a line based on values VBA

    I must be to old ! copied past.. not working in my file i become crazy with this line !
    may I ask you to help ? I have attached the file.. again i appologise and thanjk you for all the help
    thanks again
    guismo30

    Quote Originally Posted by bsalv View Post
    table functions in a protected sheet, that's not working.
    So a 2-way solution, check if the sheet is protected :
    - if so, add an entirerow
    - of not, add a listrow
    To protect the sheet, allow adding rows (and columns ?)

    Also check if the necessary cells (those you want to change afterwards) aren't locked.
    Attached Files Attached Files

  15. #15
    Valued Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    376

    Re: Add a line based on values VBA

    your problem was the language !!! not your age
    Now you can enter your "modify" or "Extend" in french, dutch and english.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    04-19-2010
    Location
    belgium
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Add a line based on values VBA

    Honestly.... I am amazed i thank you very much for your kindness and patience with me lol... thanks somuch itworks perfectly. Not sure i understand the reason why language is an issuebut it works
    I wish you a VERY nice day and already a nice week end !

    Quote Originally Posted by bsalv View Post
    your problem was the language !!! not your age
    Now you can enter your "modify" or "Extend" in french, dutch and english.
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 05-29-2021, 03:04 AM
  2. Color series on a line chart based on cell values.
    By zealot in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 11-12-2020, 11:35 AM
  3. Blank/NA values to be shown as dotted line rather than straight line in Charts
    By palaniappan0212 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-29-2016, 01:01 AM
  4. Line Graph question - vetical line values at one fixed date
    By bohn in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-12-2015, 02:22 PM
  5. Create line item rows based on values between two dates
    By eeidmna in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2014, 01:55 PM
  6. Combining multiple line items into one line item based on column.
    By mguz018 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-20-2012, 07:22 PM
  7. Report a list line by line based on name criteria
    By cptnmorgan9999 in forum Excel General
    Replies: 9
    Last Post: 06-24-2008, 11:35 PM

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