Excel Form - need macro to add multiple rows with data validation & conditional formatting

Hi,

The excel form looks like in the attachment. Each Product has multiple rows with cells containing data validation, drop down lists, instructions and conditional formatting.

excel form.png

For example if a quantity is entered, the form will highlight in red the other required cells to be filled out. Once the information is entered the red background will disappear. The "Description" field is limited to 30 characters. The "Part Number" and "Manufacturer" are restricted to selections from drop down lists.

I need to include the option of adding other Products as necessary, with all the product's information as specified in each of the 4 adjacent rows, but keeping the same parameters: data validation,instructions, conditional formatting and formulas.


Thank you for your help!

John, I truly appreciate your help! You made it look so easy... The Code is very close to what I was looking for.

There are 2 things that need revision:
1 - the check box for the Capital is lost when a new item is entered - Would it be possible to add the check box to the code? If not, probably I will restructure the cell and instead of a check box I will use a Y/N drop down

2 - I noticed the code will replicate the previous product including the details and the values. Can it be adjusted so it will enter a blank product (with all it's lines) but keeping the validation and the conditional formatting?

THANK YOU!

This code works fine, but it still keeps the "manufacturer" value from the line above. I am not sure on how to adjust this.
Thank you Jaslake!

Thank you for the fast reply! I kind of guessed it and I have been close
I noticed that if the last item is deleted, the check box will still remain on the top of the button "Insert Product". Or if 2 new items are added and than deleted, 2 check boxes will remain on the top of the Insert button. Any thoughts on this?

Thanks again!

You are a true GURU This code works!

This forms will be used by others too. Is there anything else needed for others to use the form? Any special instructions?
What do you mean by disabling the right click delete option?

Thank you!

It looks like there is a problem: I will need to protect the form to restrict editing by other users, the code seems not to work properly if the form is restricted and password protected...

I can send you the form protected, but is the one you sent me. If you protect the latest form you sent me: Form_v3, you'll see the error code.
To protect the form I use this path: Review Tab -> Protect Sheet -> options checked on: Select locked cells; Select unlocked sells; eventually also: Insert and Delete rows.

Hi Jaslake,

When I open the attachment I am getting an Run time error message. Please see screenshots attached.
1.png2.png

Thank you.

Hi Jaslake,

When I try to open the Form I am getting a Run Time error.
Please see the screenshots attached.

Thank you.
2.png
1.png

Hi Jaslake,

It seems to be working! I am trying now to replicate the code from From v4 into my actual form. There are only 3 differences between the initial form and the actual form I will be using:
1. In the actual form the first line item starts in line 17 (instead of 4)
2. The Price is a merge of columns F and G. (instead of column F)
3. The value is extended to column H (instead of column G)

Attached is a screen shot of the code adjustments. I am sure I must have been entered something incorrectly because the code is not working in the new/actual form. Could you please take a look and help?

Thank you very much!

MODIFIED CODE.png

Sorry, I have not included the screenshot. Please see attached.

Thank you very much!actual form.png

Hi Jaslake,

so sorry for the delay! I wanted to add the excel form I'll be using but I am not sure on how to attach it...

The requirement is the same,the form should include 2 Buttons : Add Item & Delete Item.
The latest code you sent me on 02/04 with Form V4 needs to be adapted to work on the form version I sent you today.

Thank you.

Hi Jaslake,

I have tested this form and noticed that when an item is deleted, the cells A4, A8, A12 and A16 are changing the text content into numbers.

Also when an new item is added, it copies the above item values, it replicates the item above. I would need the Insert Item button to copy the corresponding item rows with empty cells but keeping all the validations on.

In this form/code don't we start from row 17 column 1, instead of (4,1)?


Sub New_Product()
Dim r As Range
Dim myCBX As CheckBox
Dim myCell As Range
Application.ScreenUpdating = False
Set r = ActiveSheet.Shapes(Application.Caller).TopLeftCell
Cells(r.Row, r.Column).Resize(4, 1).EntireRow.Insert



Thank you!

Thank you very much for your help!

I am getting an error message when I try to open form 5.1 L1 v2

It is about the form content: Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.

Hi Jaslake,

I opened the form with no problems, but noticed that the validation for the New Product added doesn't work properly. In fact any/all New Products added are linked to the validations on item #5...

Hi Jaslake,

I opened the form with no problems, but noticed that the validation for the New Product added doesn't work properly. In fact any/all New Products added are linked to the validations on item #5...

It is actually the formatting that is not working for the new products entered.
If a quantity is entered => the price, material, text and element cells will turn Red. (Red cell = cell required to be completed)

This formatting works on item#1,2,3, 4 and 5. It doesn't work on any New Product added.
The formatting for all new products added depends on the values that are entered for item #5.

test this: delete the quantity for item#5 and see how it affects the formatting on the new items added #6 and #7.

or try testing this: instead on selecting material#1 on item#5, from the drop down choose "select" and see how the material cells are affected on item#6 and #7

Thank you for looking into this.

yes, I remember! I'll change it and see if it works.
I will let you know.

Thank you.

I have changed the conditional formatting from Absolute to Relative. For new products added, the conditional formatting works for the unit price only, it doesn't work for material, text and element lines, unless I will choose one of the options from the drop-down, then switch back to "select..." or if I will open the conditional formatting for the new product and hit apply...

Please see attached the form with the conditional formatting changed to Relative.

but you have already worked a lot and now you are so close to solving it ...

Thank you so much for all your help!
How can I mark the thread SOLVED?