Copy Data from a Form to a work sheet

[larrysdime]

I am need of expert help on VBA Code

I have a Data Entry form that I want to populate specific cells on the worksheet when the enter button is pressed. My format on the worksheet where I need the data is as such:

Part Number - C5 and below
Description - H5 and below
Supplier - Y5 and below
Supplier Part Number - AD5 and Below
Package Cost - AL5 and Below
Package Quantity - AO5 and Below
Sell As Quantity - AR5

Here is the Kicker. When Inserting the New part into the worksheet I need it to Insert into a row alpha/numeric in the Part Number Column. (Column C) Example Part# EQASHP0025

There are many other Columns of data on the worksheet, but they are not part of the New Part Entry Form. However, I need the Field on the form to match the proper columns as I outlined above.

Does anyone have code that would do the trick?

Also, I would Like to use the same Form to search and choose Quantities of Parts as well. Should I add Column B5 and below to the list above since I will need to enter the Quantity of parts needed when selecting parts for an estimate? Or would adding the QTY Needed to the part entry form create a problem when entering the new item? Hope that part made sense.

Thanks,
Larry

[xladept]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[rcm]

could you send a sample in excel?

[larrysdime]

Hi and thanks for responding.

First I am decent with excel skills but horrible when it comes to code. So, I am at a point where I need help on code for the project.

Attached is a copy of the project I am working on. I will give a step by step and then where things currently are on the form:

1. A technician opens a new service ticket and fills out the required general information on the worksheet labeled "Service Ticket"

2. He then goes to the worksheet labeled "Parts" to select the part Used on the Service Call.

3. He may need to search the data for the part he used and when the Item is found add the quantity used next to the part. My thoughts are that they would hit the Find Part Button at the upper Right Hand Corner. Begin the search criteria by searching the part number field. This might be better accomplished with a multiple drop down list box that allows them to see multiple fields (Columns) in their search. Once the item is found they would again be able to enter the quantity that they used on the job. Here I am not certain whether a form tool is appropriate or a combo box that allows them to view multiple columns during the search is the right answer. Or may be search form that contains a combo box of several columns.

4. If during the Search the part is not found the Technician can then add the part to the data. They would hit the "Add Part" button and fill out the form that I already created call "Add Part". Once the New part is created I need it to go back into the worksheet in the worksheet in Alpha/Num order by the column "Part Number" which is Column C. The only question here is should the Quantity Used be included on this form to avoid searching the part again once added just to enter the quantity used.

5. Once the New Parts are entered and the parts already existing have quantities greater than zero in front of them they would then hit the "Select Parts" button in the upper right hand corner.This simply filters the page to display only the parts that have quantities greater than zero in front of them in Column B

6. Once Step 5 is complete they would highlight and copy the appropriate columns on the "parts" work Sheet and copy them over to the "service ticket" worksheet starting at cell B25 on the "Service Ticket" worksheet"

Here are other factors to note:

1. Neither the "Find Parts" or "Add parts" command buttons work. I do not know how to unassigned the macro assigned to them. So they will need to be fixed to execute the proper Form(s)

2. The "Select Parts" Command works and will filter the Items that Have quantities greater than Zero next to them. No Fix needed there

3. The Check Box on the right labeled "Yes" works and determines whether to use itemized pricing on the service ticket or to white out the item pricing and only provide a total.

4. The columns highlighted in yellow are calculated fields which may not need to be on the 'enter new part" form. As in one line example the Refrigerant on line 7 is purchased at a 30lb cylinder cost. We would Enter the cost for the entire Cylinder (Column AL) and the total number of pounds (Column AO). Then the "Cost Ea" (Column V) Divides the Cylinder Cost (Column AL) by the Package Quantity (Column AO) to produce the Cost Each. Then the cost each uses a V Lookup on the "tables Tab" to Determine which Multiplier to use and populates the Multiplier Column AI. Finally, The Cost Each multiplies itself by the line Multiplier to produce the "Sell Price" (Column S)

5. Everything in Line 4 may not be important since I'm not sure if these fields are need in the "Add New Part" form, but I wanted to explain the sequence. Maybe all the fields should be on the "Add New Part" form and calculate there before inserting into the database

6. I have already created a crude form for Adding a New Part. Please feel free to spruce it up if you like

7. Again both of the Command buttons on the left side are assigned the wrong Macro. They will need to execute the proper form or forms if you feel more than one is the right way to go.

I know I am probably forgetting something. Oh....You are probably wondering why I used such small widths on the columns and merged cells together. I do this because it is so much flexibility in designing a form with small column widths and merging cells together as needed. Without doing this it becomes impossible to design a good form to contain the information as it does in the "Service Ticket" Tab. In addition, this allows easy copy and past of Data from the "Parts" worksheet over to the "Service Ticket" worksheet since all cells on both worksheets are identically sized and have the exact number of merged cells. I wish I knew a better way around this, but unfortunately I don't.

I hope I have covered everything, but if not please give me a shout. I really appreciate you reaching out and helping with this project.

Here is the file: Perfect Degree Service Ticket.xlsm

Thanks Again,
Larry

[larrysdime]

Hi and thanks for responding.

First I am decent with excel skills but horrible when it comes to code. So, I am at a point where I need help on code for the project.

Attached is a copy of the project I am working on. I will give a step by step and then where things currently are on the form:

1. A technician opens a new service ticket and fills out the required general information on the worksheet labeled "Service Ticket"

2. He then goes to the worksheet labeled "Parts" to select the part Used on the Service Call.

3. He may need to search the data for the part he used and when the Item is found add the quantity used next to the part. My thoughts are that they would hit the Find Part Button at the upper Right Hand Corner. Begin the search criteria by searching the part number field. This might be better accomplished with a multiple drop down list box that allows them to see multiple fields (Columns) in their search. Once the item is found they would again be able to enter the quantity that they used on the job. Here I am not certain whether a form tool is appropriate or a combo box that allows them to view multiple columns during the search is the right answer. Or may be search form that contains a combo box of several columns.

4. If during the Search the part is not found the Technician can then add the part to the data. They would hit the "Add Part" button and fill out the form that I already created call "Add Part". Once the New part is created I need it to go back into the worksheet in the worksheet in Alpha/Num order by the column "Part Number" which is Column C. The only question here is should the Quantity Used be included on this form to avoid searching the part again once added just to enter the quantity used.

5. Once the New Parts are entered and the parts already existing have quantities greater than zero in front of them they would then hit the "Select Parts" button in the upper right hand corner.This simply filters the page to display only the parts that have quantities greater than zero in front of them in Column B

6. Once Step 5 is complete they would highlight and copy the appropriate columns on the "parts" work Sheet and copy them over to the "service ticket" worksheet starting at cell B25 on the "Service Ticket" worksheet"

Here are other factors to note:

1. Neither the "Find Parts" or "Add parts" command buttons work. I do not know how to unassigned the macro assigned to them. So they will need to be fixed to execute the proper Form(s)

2. The "Select Parts" Command works and will filter the Items that Have quantities greater than Zero next to them. No Fix needed there

3. The Check Box on the right labeled "Yes" works and determines whether to use itemized pricing on the service ticket or to white out the item pricing and only provide a total.

4. The columns highlighted in yellow are calculated fields which may not need to be on the 'enter new part" form. As in one line example the Refrigerant on line 7 is purchased at a 30lb cylinder cost. We would Enter the cost for the entire Cylinder (Column AL) and the total number of pounds (Column AO). Then the "Cost Ea" (Column V) Divides the Cylinder Cost (Column AL) by the Package Quantity (Column AO) to produce the Cost Each. Then the cost each uses a V Lookup on the "tables Tab" to Determine which Multiplier to use and populates the Multiplier Column AI. Finally, The Cost Each multiplies itself by the line Multiplier to produce the "Sell Price" (Column S)

5. Everything in Line 4 may not be important since I'm not sure if these fields are need in the "Add New Part" form, but I wanted to explain the sequence. Maybe all the fields should be on the "Add New Part" form and calculate there before inserting into the database

6. I have already created a crude form for Adding a New Part. Please feel free to spruce it up if you like

7. Again both of the Command buttons on the left side are assigned the wrong Macro. They will need to execute the proper form or forms if you feel more than one is the right way to go.

I know I am probably forgetting something. Oh....You are probably wondering why I used such small widths on the columns and merged cells together. I do this because it is so much flexibility in designing a form with small column widths and merging cells together as needed. Without doing this it becomes impossible to design a good form to contain the information as it does in the "Service Ticket" Tab. In addition,this allows easy copy and past of Data from the "Parts" worksheet over to the "Service Ticket" worksheet since all cells on both worksheets are identically sized and have the exact number of merged cells. I wish I knew a better way around this, but unfortunately I don't.

I hope I have covered everything, but if not please give me a shout. I really appreciate you reaching out and helping with this project.

Here is the file: Perfect Degree Service Ticket.xlsm

Thanks Again,

Larry