Forum Discussion
VBA Programming Assistance
I have been working on the attached spreadsheet creating formulas and now I have wading into the VBA programming...which I have not done since college and didn't do very well then.
As I get deeper and deeper into this I am convinced I can make it perfect. So now I added our purchase order form to the workbook. What I would like to do is be able to hit the "SAVE & SEND" command button and it will:
1. Save only the "Purchase Order Form" worksheet as a pdf on a designated spot on my computer...in this instance it is "P:\Purchase Orders\2019\"
2. Prompt my email to open with the filename as the subject line with "Purchase Order: (Filename1)"
3. And this might be REALLY ambitious...make the purchase order number for cell I11 on "Purchase Order Form" automatically populate to the next available cells in the "Invoice Register" worksheet such as the filename is "7201_081918_SF" so it will populate cell B6 on the "invoice register" with 7201, cell D6 on the "Invoice Register" with "08/19/2018" and cell E6 on the invoice register with "Springfield Electric, Inc." as it relates to the SF from the table on the "setup" tab.
I realize this is a tall order and maybe not something anyone wants to take the time to help out with but I am convinced it can be done, just pretty sure my expertise isn't at that level! If anyone ever needs with with electrical power distribution I would be happy to reciprocate ;-)
8 Replies
Hello Deborah,
I took some liberties, and made some small modifications. First, I saved it as .xlsm, as it is the current file format for Excel, and the older format was trying to revert some things. Second, I deleted all except one line of your Invoice Register table. The VBA code checks to see if the first cell of the last row is blank. If it is not, then it adds a new row to the bottom, and then populates the Job Number, Order Date, and Vendor Name of the last row from your PO number. It exports the active worksheet (it will probably always be the Purchase Order Form, since that is where the button is located, but if you were to initiate the macro from a different tab, it will export that tab). It saves it without prompting, and if the same filename already exists, it will overwrite it without warning. It then opens an email with the subject "Purchase Order " and the PO number from the form, and attaches the pdf file to the email. That last bit was an assumption on my part, and if you don't want it to attach the file, you can delete the .Attachments.Add attpath line near the bottom of the code. I hope it works for you.
That is so amazing and I promise I will read through the code and try to understand it all so I don't have to ask so many questions in the future! The only thing is when I click the "Save File" button I get the following error:
As it relates to the .Attachments.Add attpath line in the code.
I do want the file to attach to the email so you assumed correctly and it really is very much appreciated!
Another thing that I noticed that you may or may not want to change. The Submit image (button), and SAVE FILE button both are visible on the exported PDF. If you want them to not display, you can set them so they don't print. They are different types, so the steps for each one is different. Let me know if you would like some assistance with that.
