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

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

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 ??

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

Originally Posted by bsalv

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.

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?

Originally Posted by bsalv

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.

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

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

Originally Posted by bsalv

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.

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 !

Originally Posted by bsalv

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.