Formula in macro, value displayed in TextBox

Hi all,

I need help creating macro for a formula which I have in the attached file. This formula (in cell C2) is generating a product number based on the selected product type in the cell B2, and number of already existing product from that type (range C6:C97).


The code is made of 12 characters.
- First characters are allays "Product_"
- then is followed by a letter which (according the given table in sheet "Pickup_list" in cells H1:O6) describing the product. For example, product 1 always start with letter A, B, C, D or E. Product 2 Always start with F, G, H or I.
- last 3 characters are alphanumerical which are given subsequently, starting with 0-9 and then continuing from A-Z. For the first product of a type, this characters will be 001, then 002, 003 and so one. After 009 will came 00Z, then 00B, 00C.
- after are used all combination for Product1 which starts with letter A, we'll move to codes which are starting with letter B (as shown below).

Product_A001
Product_A002
Product_A003
…
Product_A009
Product_ A00A
Product_ A00B
Product_ A00Z
Product_ A010
Product_ A011
…
Product_A01Z
Product_ A020
…
Product_AZZY
Product_ AZZZ
Product_ B000
Product_ B001
Product_ B003


The formula in cell C2 in sheet is doing exactly this. But I need instead formula in the sheet, to have formula in macro which will create this code and show it in a TexBox in the UserForm.

I've inserted in the file a UserForm (activated after is pressed green button named "Show input window"), in which, after selecting the "Product Type" from the drop down menu, I need in the "Product Number" box to be displayed the code based on above mentioned calculation.

Thank you in advance for the help.
Igor


VBA formula.xlsb

Hi Igor,

See the attached updated copy of your file, which uses a Macro to:
a. Update the value in cell 'C3' of Sheet 'Actual situation'.
b. Updates values in the UserForm.

Please NOTE that my code was done using Excel 2003, which may have destroyed some of your CONDITIONAL FORMATTING. I suggest you test using my file, then import the UserForm to your file, and cut and paste the following code into your file either from here or from myfile.

UserForm Code (cut and paste not needed, comes with Importing):

Please Login or Register  to view this content.

Sheet 'Actual Situation' code module code:

Please Login or Register  to view this content.

Ordinary Code Module Code Module ModCreateProductCode:

Please Login or Register  to view this content.

Lewis

Hi all,

I need additional help with the code. The prepared code from LJMetzger works perfectly. But now I need to insert also country prefix, which will identify to which country the product need to go, as shown in the below example.

Current code : Product_A003
Required code : Product_BEA003

Country Country prefix
Austria AT
Belgium BE
...
...
UK UK
Ukraine UA

The country prefix is predetermine. It is in the attached document, in sheet "Pickup_list"

Thank you in advance for your help.

Igor

formula in macro value displayed in textbox - 2015.10.01.xlsm

Hi Igor,

The code was changed to meet your new requirements. See the attached file which contains:

UserForm Code:

Please Login or Register  to view this content.

Sheet 'Actual Situation' code module code:

Please Login or Register  to view this content.

Ordinary Code Module Code Module ModCreateProductCode:

Please Login or Register  to view this content.

Please note that Option Explicit is very important. It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

Lewis

Hi Lewis,

The code works perfectly!!!!! Thank you a lot for your help!!!

Cheers.

Igor