Dispatching Materials details output as plain Text

bobby786

Board Regular
Joined
Apr 24, 2014
Messages
85
Office Version
  1. 365
Platform
  1. Windows
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



 

Attachments

  • Addreslist.screenshort.png
    Addreslist.screenshort.png
    30.9 KB · Views: 3
  • Dispatch.Screenshot.png
    Dispatch.Screenshot.png
    62.3 KB · Views: 4
  • material_names.png
    material_names.png
    97.2 KB · Views: 4

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
442
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.

1574908277278.png
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

bobby786

Board Regular
Joined
Apr 24, 2014
Messages
85
Office Version
  1. 365
Platform
  1. Windows
@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 .

1574918729195.png
 

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
442
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

Board Regular
Joined
Apr 24, 2014
Messages
85
Office Version
  1. 365
Platform
  1. Windows
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

1574919794965.png
 

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
442

ADVERTISEMENT

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

1574920340239.png
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.
 

Attachments

  • 1574920389380.png
    1574920389380.png
    12.6 KB · Views: 3

bobby786

Board Regular
Joined
Apr 24, 2014
Messages
85
Office Version
  1. 365
Platform
  1. Windows
1. Bingo it is working now , i have the button

1574921028693.png


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 ?

1574920891195.png
 

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
442
So do you like the idea of the user form? Or would you prefer it done from the sheet?
 

bobby786

Board Regular
Joined
Apr 24, 2014
Messages
85
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,177
Messages
5,640,620
Members
417,158
Latest member
jimmy1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top