Userform Code to Find entered value and replace with specific text

**NWGuy** · 07-14-2019, 04:02 PM

Hello everyone,

To start, I've looked far and wide for a specific solution to this, so I apologize if this ends up being redundant to another thread.

I currently have a code written for a userform with dynamic dependent combo boxes the user picks from in order to identify a specific part number they would like to remove from a bill of materials.

When the user clicks submit, once all fields are filled out, the code below logs the part number (cmb_RemovePN) and quantity (tb_RemoveQTY) to a removal log on (sheet 2).

Userform.JPG

Please Login or Register  to view this content.

What I am trying to do is add to my userform VBA code so that when I click the submit button, excel finds the value chosen in the part number combo box (cmb_RemovePN) in range (sheet 8, column "F") and changes the matching part number in that location to the word "Replaced".

I've been able to find examples of how to do this via setting up a find value & replace value table, or by setting specific find and replace values in the code itself, but in my entry each part number entered will be different, while the replacement word "replaced" will remain the same.

The data that my dependent drop-down boxes uses is a match to the part numbers shown on the sheet I want to update, so there won't be an instance of "no part number found.

This is my first post, so please just let me know what else you need from me. Your help is greatly appreciated.

**Leith Ross** · 07-14-2019, 04:48 PM

Hello NWguy,

Welcome to the forum!

It would be best if you attach a copy of your workbook.

Click on GO ADVANCED at the bottom of the message window.
Once the new window opens, scroll down to the section Additional Options.
You will see the heading Manage Attachments. Click on the link Manage Attachments
Under the heading Upload Files from your Computer, click the button on the top left Choose File
Once the you have selected your file, click the Upload button on the far right side.
After the file has finished uploading, click the button on the top left side Close this window.

**NWGuy** · 07-14-2019, 05:47 PM

Unfortunately my workbook contains sensitive data that I cannot share. I can create a dummy workbook to show my intent if necessary, but I was hoping to gain a little bit of direction first without it.

**Leith Ross** · 07-14-2019, 06:12 PM

Hello NWguy,

A dummy workbook is fine just as long as it faithfully reproduces the original layout, formatting, formulas, and any macros it uses. The reason I am asking for all of this is when comparing and searching for information, there a myriad number of issues that interfere with the processes. Rather than play Q and A ping pong and hope we get it right, having accurate data from the outset will save time. Thanks.

**NWGuy** · 07-14-2019, 07:28 PM

Dummy workbook uploaded. The button that activates the userform in question is titled "remove part" at the top of Sheet 1.

Sheet 1 is the log for all remove entries. Columns A & B are updated when the submit button in the userform is selected.

Sheet 4 is the data to populate and restrict the dependent drop down boxes of the userform

Sheet 2 is the bill of materials I am searching in column "F" for a match to cmb_removePN combo box value, and to change the part number in column F on sheet 2 to the word "removed"

Note* the userform won't open upon clicking the "remove part" button on Sheet 1, however a screenshot of that userform is shown in my original post.

Hopefully this is enough to get started. Thanks All.