• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need To Generate Unique material ID at the time of every material receipt in VBA

Sachin Bhor

New Member
Hi Guys,
I am New in VBA - FILE ATTACHED
Login Id - admin
Password- admin@123

I need your help to create VBA code for below case/example
I have created userform for Raw material entries with below details
For Example -
SR.No - 1
Date Of entry - 29-Mar-2020
Supplier Name - ABC India Ltd
Material Name - Deisel Engine
Material Number - ............
Now Condition is I want to create Material Code automatically in userform for each material on the basis of material receipt as below
* Material Code For below Case should be - A100120B
1) Material name - Deisel Engine - A
2) Supplier Name - ABC india ltd - 1
3) Batch Code - for first batch - 001 (so on for remaining batches) -
Also i want create batch code for material automatically Like - Batch Code - for first batch - 001, 002, 003, 004, 005,006 (so on for remaining batches)
4) Year - 2020 - 20
5) Material Type - B
please guide me
Regards,
Sachin Bhor
Mail - sachin22588@rediffmail.com , sachinb22588@gmail.com
 

Attachments

  • VBA TEST.xlsm
    32.2 KB · Views: 12
I don't understand the first part of this. Apparently you already have the values, which you will collect from a user form, and you need to concatenate them into a material code. But you already know how to concatenate values (don't you?). I don't understand what you're asking.

As for the batch code, you want to start with 1 and increment it with each new order, right? And each execution of the program happens just once, when the user hits the New-Entry button. So you need your program to store the latest batch number after each execution, then pull it up again then next time a user hits that button.

If it's the same user each time, using the same workbook, you can just save the number in one of the worksheets in that workbook. If it's different users all using the same workbook, you may have conflicts—you need some way to make sure only one person runs the program at a time—but you can still store the batch number in a worksheet between executions. But if multiple users will be using copies of this workbook, then each copy needs to save the latest batch number in some common location.

But I'm guessing I haven't answered your question. In that case you'll have to ask it again, using different words.
 
This should give you a start.
see example.
A few tips
1. Use tables.
2. Avoid AddItem, it is a slow way to fill comboboxes.
3. Avoid using things like MsgBox "UPDATED SUCCESSFULLY",this gets on users' nerves when they see it 50 times a day,and have to press ok every time.

Set up your material list the same way as you can see in the list page.
When you exit the material name box afther selecting a material, Supplier Name and Material Number are filled in automatically
To update, select a line in the listbox.
Search a Sr.No. the numbers that do not meet the search criteria are removed from the list.
 

Attachments

  • VBA TEST.xlsm
    43.8 KB · Views: 4
Thanks for Your Reply, I got some Hints from your explanation
I have Created VBA code to Concatenate the Codes generate material Code

Actually , I need your help on another topic, please refer the attached file for the same
Login Id - admin
Password- admin@123


Now I am Explaining you my requirement

1) Its related to Sheet named "ISSUE RM" in File
2) I have created user form in this sheet to issue material (please click on "ISSUE ENTRY" Button)
3) When you search for any MATERIAL NAME in form it is showing you report in LISTBOX
4) In list Box Data fetched from Sheet "INWARD_RM"
5) In list box last column having named "AVAILABLE" showing material available stock quantity

In this column, I want values to be searched which are greater than 0 only

can you help me the write VBA Logic for this.
 

Attachments

  • VBA TEST.xlsm
    101.1 KB · Views: 7
This should give you a start.
see example.
A few tips
1. Use tables.
2. Avoid AddItem, it is a slow way to fill comboboxes.
3. Avoid using things like MsgBox "UPDATED SUCCESSFULLY",this gets on users' nerves when they see it 50 times a day,and have to press ok every time.

Set up your material list the same way as you can see in the list page.
When you exit the material name box afther selecting a material, Supplier Name and Material Number are filled in automatically
To update, select a line in the listbox.
Search a Sr.No. the numbers that do not meet the search criteria are removed from the list.

Thank You so much
It Is really Helpful, I have Changed some Logics as per my requirement :)
Can you please help me on my another post
 
Back
Top