Dispatching Materials details output as plain Text

[bobby786]

Dear All Excel Experts ,
My job requires me to send Text message to our vendors to dispatch the materials which contains the following information

Supplier Name : Longzhen
Model NO : S25
Material Name : Colorbox
Qty : 500 pcs
Address: Room 505 , Commercial Plaza , 104 Road , Unit 2 , Baoan District
Mr.John
Tel: 8765423
Notes: Please use lifter when the goods to this address

I have excel sheets :
Dispatch = Which will be used to enter the data
Address_list= which contains Supplier name and its address details with contact person name and tel no.
Material_Names = which contains the names of the material which will be needed to dispatch it.

Right now i have to manually copy paste everything into "Dispatch" and then i will copy all cells and paste them in notepad , then copy again and paste in our Text-message App which is similar to Whats-app.

I want to make this process automatic.

Here i will try to explain it , please forgive me for language and long post , as English is not my native language.

1. I want when i enter the Supplier name , Model NO , Qty after that "Material Name" column in "dispatch" sheet should give me option to select materials from "Material_Name" sheet , so i don't have to type it , in post as example i just used the simple name but in real life my material names contains words and numbers which is hard to remember always.So i like to have selection or drop-down or whatever is possible to easily select the available material names.

2.Address Column in "Dispatch" should get the corresponding address of that supplier automatically , so i dont' have to copy paste it , As soon as i enter the supplier name it get the address automatically.

3."Notes" Column in "Dispatch" sheet should get the notes from corresponding sheet , i have few different notes for different supplier , here i have no clue how to make it automatic please share your suggestion for it.

4. Last but not least , when i select all my data copy and paste it should give me Plain Text output like the Below:
Supplier Name : Longzhen
Model NO : S25
Material Name : Colorbox
Qty : 500 pcs
Address: Room 505 , Commercial Plaza , 104 Road , Unit 2 , Baoan District
Mr.John
Tel: 8765423
Notes: Please use lifter when the goods to this address

To download the spreadsheet click on this link Dispatch.Materials

Remarks: Somehow XL2BB capture range is not working when i paste the range it shows like this " ￿￿" , therefore i have upload my spreadsheet to my cloud web page in case if any helper want to take a look , i have attached the screenshot with the post as well.

I hope to have help from you guys Thanks in advance

Regards

 

[Paul Ked]

Hi, that's enthusiasm!

As it's Macro enabled it won't run online. Download the file and you should see a button under Dispatch List. Hit that to load the form.

 

[bobby786]

@Paul Ked
Yes i did download it , but strange , i cannot see the button as it appears on your screenshot. I attached my screenshot here for you .

 

[Paul Ked]

Is the code there? Check if there are two userforms in the VBE.
The code to show the 1st form (frmDispatch) is in modClicks

 

[bobby786]

yes the code is there , I try to run it manually the USERFORM appeared , the basic thing is working fine . Good JOB man.
now i have few questions:
1. how to get that big button to show up.
2. Text output Where it says "Supplier Name" and other entries which is constant , if i change the A2 in Dispatch list which is " Supplier Name" to something like english chinese " Supplier Name / 工厂名“ the text output will change also ? Or is it all dymanic or static ? if it is static is there any way to make it dynamic so i can change that as per requirement , for example if i want to add chinese words there , i dont know if you hard coded those words , because i know the VBA editor wont accept the chinese characters i think to display as TEXT output.

Regards

 

[Paul Ked]

1. Developer - (Controls) - Insert - Form Controls - Button (Form Control)

Assign the macro DispMats and change the button name.
2. It is static, but it can be changed.

I don't know about Chinese chars, but I'll make some changes and you can try.

 

[bobby786]

1. Bingo it is working now , i have the button

2. I have tried to change my system locales , and added the chinese character and it seems working , but i edit this in code not from the sheet . You have better idea ?

 

[Paul Ked]

Latest version with dynamic headings for the text output below. I copied the supplier from your post and it does work:

Dispatch.Materials.MultiLing.xlsm

 

[Paul Ked]

So do you like the idea of the user form? Or would you prefer it done from the sheet?

 

[bobby786]

USER

So do you like the idea of the user form? Or would you prefer it done from the sheet?

USERFORM is perfect thing for me , is it possible to add some more options there , and make some little changes later on ?

regards

 

[bobby786]

Latest version with dynamic headings for the text output below. I copied the supplier from your post and it does work:
View attachment 883
Dispatch.Materials.MultiLing.xlsm

@Paul Ked

i should salute you if we get chance to meet each other. ITS BINGO. Working fine as required. I have already used it whole day today your masterpiece.

Regards