Is there a way to create a custom form that can pull, edit, and save data depending where?

[Cyberpawz]

I know it's possible to create a custom form, and I'm learning all I can but I am looking for three functions in particular.

Search, Update, and Save.

Here is my problem, I need the search to search in one field, on two pages and one entirely separate fields in third page.


The search needs to look at SO# in New Data and Old Data, and the NCMR# on the Compiled page. I figured that they can be two separate search fields, if nothing show up, it would say no match. If there is no match, then I need to have the option to add the data into the fields, and then save, and it will save to Compiled sheet on the last row with no data on it.

If it finds a match it pulls the data from the sheet and you can change it, and it will store the data in compiled, or if it was pulled from the compiled page it would then update the row the data was pulled from originally.

Here is a copy of the sheet, I have the forms built, just no code because that seems to be where I am weakest, at least for now.

Also, this has to be pop up forms... they can't reside on the sheet.

If anyone can help it be appreciated.

[rvasquez]

Hello cyberpawz,

That was a fun one to figure out!

Attached is a spreadsheet that I believe achieves the results you are looking for. I have inserted comments into the code to help you understand how it works.

Please let me know if you have any questions.

Thanks!

RVASQUEZ

[Cyberpawz]

Hello cyberpawz,

That was a fun one to figure out!

Attached is a spreadsheet that I believe achieves the results you are looking for. I have inserted comments into the code to help you understand how it works.

Please let me know if you have any questions.

Thanks!

RVASQUEZ

I just looked at the sheet I clicked on the Macro Editor and nothing shows up... the script shows the form I'm made on New Data but there is no code, where you hide it?

That being said, good start, what I was thinking of was a search button in where the search form should show up, then when found the editing form shows up, but nothing did after the data was found, as least I think it was found because no errors showed up.

[rvasquez]

Hello Cyberpawz,

Okay I added a new worksheet that just has a command button on it. When you open the workbook make sure to enable macros and content in order for the workbook to perform correctly.

When you click on the command button located on the Main Page worksheet the Search Userform should pop up.

Type the number of the NCMR or SO# you would like to look up in the appropriate text box provided. Then click search. To try it out type in the number 34488 in to the SO# text box leaving the NCMR# blank and click search.

Once you click the search button you should get the pop up for the NCMR form which sets the Sales Order equal to the cell that was found and the Customer Name next to the cell that was found. The Save button should be grayed out as it is meant to add a new entry if the search returns no value. To continue with the test type a random number in to the text box next to the NCMR field and check the internal box. Then select the update button.

You should now get a message box that states that your file has been updated and added to the compiled worksheet. Click okay and you can look at the New Date and compiled worksheet to see the changes.

To view the code for this workbook press the f11 key while holding down the alt key to view the visual basic application. On the left double click on sheet four. Here you will see how the command button opens the search form on the onclick event.

Now right click on the Search form in the Project Window and select the view code option. This is where the code lies and all of my comments are located.

You can do the same thing with the NCRMForm by right clicking on it in the project window and selecting view code.

Please let me know if this still doesn't work for you.