Use VBA to create item code from user defined data (with screenshot)

**unclejemima** · 06-24-2013, 01:52 PM

If anyone can generate a VBA code for this, I will be very impressed. This is a tough one for sure, but perhaps a good challenge for someone.

Please see attached screenshot. itemcode3_zps0b041438.jpg

The first 5 columns are user generated, and then once all the fields are entered the VBA will spit out a code in Column F.

The code in column F is a collaboration of the information in Column A, B, C, D and E.

The first part of the code is a 5 digit number that comes from the Supplier and Product. This a random code.
IE, Joe Farms Apple could be 34512. If Farmer Ted has an Apple it would be a different random code like 93249

The second part of the code is the color. It is 2 digits. It is relevant to the suppler and product. Example, a Red Apple from Joe Farms would be 01. A Green Apple from Joe Farms would be 02 and so on. A Green Apple from Farmer Ted, if green is entered first, would be 01 as it is a different Supplier. The color can be re-used for the supplier and product if the size is different.

The third part of the code is from the sizes in Column D and E. This is alphanumeric.
IE, Size:6 and Size:12 would make 0612. Size:02 and Size:RB would be 02RB

Each code can only be generated once. If the information in columns A, B, C, D and E are changed after a the VBA generates a code, do not re-calculate the code. Rather retain the original generated code and highlight the text in Red to show that the columns what generated the code were changed.

The rows can be entered at any time. This means that Row 8 may be entered after Row 9 and 10. Row 9 and 10 would generate a code from VBA, but then a user would enter Joe Farms Banana Blue 12x12 into Row 8. This should still generate a code 68328-03-1212. With other VBA approaches I've seen, this would not work because it came before the calculated code in Row 9 and 10. I hope the VBA can allow to search up and down the spreadsheet for duplicates and such.

Very excited to see if someone can pull this one off!

**unclejemima** · 06-25-2013, 01:38 PM

Anyone? I'd assume this is a difficult one but was hoping someone might be able to pull it off.

**unclejemima** · 06-26-2013, 12:00 AM

Anyone? Almost had it figured out in this thread... http://www.excelforum.com/excel-prog...53#post3272753 but I've changed it slightly.

Very much hoping someone may have the know-how to do this one in VBA!

Thanks!!!

**jindon** · 06-26-2013, 01:19 AM

try

Please Login or Register  to view this content.

**unclejemima** · 06-26-2013, 03:10 PM

Thank you jindon,

I tried filling in variables in A2, B2, C2, D2, and E2, expecting cell F2 to spit out the code. It did nothing.

Am I doing something wrong?

See attached. test1_zps1e94328b.jpg Shows my worksheet with VBA screen open.

Let me know,

**jindon** · 06-26-2013, 05:24 PM

The code I posted assumes all the varialbes are filled.

So fill the all the columns A-E, then run the code.

**unclejemima** · 06-28-2013, 06:12 PM

I see. How to I "run the code"?

Is there any way to fill the variables and have column F calculate automatically?

Thanks,

**jindon** · 06-28-2013, 07:10 PM

1) To Worksheet module

Please Login or Register  to view this content.

2) Replace existsing code with the follwoing

Please Login or Register  to view this content.

**unclejemima** · 07-23-2013, 02:52 PM

Thanks jindon,

I'm just getting back at testing this...but still can't get it to work. I must be doing something wrong.

I've got a brand new worksheet open. I've created header row with titles in row A, and then filled in all but the Item Code in Row B. See screenshot...

\1

I'm confused where I'm pasting the code.

If I have the new spreadsheet with the text entered as the screenshot, I hit Alt+F11 to enter the VBA programming and choose "VBAProject>Microsoft Excel Objects>Sheet1 and double click. Is this where I paste the code? Private Sub Worksheet_Change(ByVal Target As Range)

Formula:

Please Login or Register  to view this content.

If I've got a new spreadsheet, where do I paste this code? \1

Formula:

Please Login or Register  to view this content.

I greatly appreciate your time.

**jindon** · 07-23-2013, 05:14 PM

Can you upload your workbook?

**unclejemima** · 07-23-2013, 05:41 PM

test sheet.xlsm

Good day. I've attached my test sheet. Just a header row (row A) and info for row B entered. I've left the Item code blank as this is what we want the VBA to calculate.

I REALLY appreciate your help. Thank you so much for checking this thread!!!

Let me know if you have problems with the file. I hope I've attached it proper :-)

**jindon** · 07-23-2013, 06:11 PM

Just change the cells

**unclejemima** · 07-25-2013, 03:36 PM

jindon, very nice. Thank you. Worked great.

can you make one more modification?

If I enter product in row 12 it will generate a code. Now if I go to enter the information in row 11, because there is a blank space separating the items it will actually change the code of product in row 12.

Is there any way to have it only calculate the code only once? Example, if row 12 code is generated it can-not be changed by items entered above it.

I've attached a youtube vid to with screen recording to display the problem. http://www.youtube.com/watch?v=PuQbhj-2r1s

Thank you again!!!

**unclejemima** · 07-25-2013, 03:37 PM

jindon, very nice. Thank you. Worked great.

can you make one more modification?

If I enter product in row 12 it will generate a code. Now if I go to enter the information in row 11, because there is a blank space separating the items it will actually change the code of product in row 12.

Is there any way to have it only calculate the code only once? Example, if row 12 code is generated it can-not be changed by items entered above it.

I've attached a youtube vid to with screen recording to display the problem. http://www.youtube.com/watch?v=PuQbhj-2r1s

Thank you again!!!

**jindon** · 07-25-2013, 08:36 PM

Test this hard...

**unclejemima** · 07-29-2013, 08:31 PM

wow. you are good!

Can't seem to break it

Jindon, you are the man!

Reputation added!

Use VBA to create item code from user defined data (with screenshot)

LinkBack

Thread Tools

Rate This Thread

Display

Use VBA to create item code from user defined data (with screenshot)

Re: Use VBA to create item code from user defined data (with screenshot)

Re: Use VBA to create item code from user defined data (with screenshot)

Re: Use VBA to create item code from user defined data (with screenshot)

Re: Use VBA to create item code from user defined data (with screenshot)

Re: Use VBA to create item code from user defined data (with screenshot)

Re: Use VBA to create item code from user defined data (with screenshot)

Re: Use VBA to create item code from user defined data (with screenshot)

Re: Use VBA to create item code from user defined data (with screenshot)

Re: Use VBA to create item code from user defined data (with screenshot)

Re: Use VBA to create item code from user defined data (with screenshot)

Re: Use VBA to create item code from user defined data (with screenshot)

Re: Use VBA to create item code from user defined data (with screenshot)

Re: Use VBA to create item code from user defined data (with screenshot)

Re: Use VBA to create item code from user defined data (with screenshot)

Re: Use VBA to create item code from user defined data (with screenshot)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions