VBA Newbie

[C Nelson]

Hi everyone, I am new to this side of excel. I have no real knowledge of the coding required to use this side of excel but I know its powerful and can do what I'm after, I just need some help if at all possible. I am working on a spreadsheet for work as an electronic shift log and I am looking to add a command button to basically save and publish the active sheet as a pdf to SharePoint and email to a group defined in a group of cells in the workbook on a second sheet without saving any changes to the original workbook. I would like to be able to pull from multiple cells for the file name. If there is anyone that could help or at least point me in the right direction of where to look for the information, it would be much appreciated. I have played with bits and pieces of what I am trying to do but could never get anything to work right so I got frustrated and scrapped it all.

 

[Killragtshirts]

Hi,

The best advice I can give is to break these steps down into manageable pieces. Personally I would get your template spreadsheet sorted first so you know all the locations in the spreadsheet your code is going to read from. I would leave the command button coding to last however, in the meantime work out if you want a button on the tool bar OR a user based form button that you can place on the cells of spreadsheet. You can save a spreadsheet to PDF and just point it to a location on SharePoint if you have OneDrive sync etc (Google the code). The email option should be doable as well and there is heaps of code on online for that also look into Mailmerge as I think that might be your answer. Google is your best friend. Do your best to cobble up some code, post it and im sure there will be plenty of people to assist you bring it together and fine tune it.

Another option is to look at alternate ways to accomplish your task without VBA exclusively. Microsoft powerapp or power automate if you have it available to you might also be another option.

Good luck.

 

[Michael M]

Here is a code that creates the PDF.
It is also a very good resource page for heaps of other coding requirements.
The code is about 1/3 the way down the page

Save Excel to PDF-VBA Macro Step-by-Step Tips Video

How to save Excel sheets or workbook to PDF. Step-by-step or use time-saving Excel macro. Fix PDF error. PDF setup tips, video, download Excel file

www.contextures.com

 

[C Nelson]

Hi,

The best advice I can give is to break these steps down into manageable pieces. Personally I would get your template spreadsheet sorted first so you know all the locations in the spreadsheet your code is going to read from. I would leave the command button coding to last however, in the meantime work out if you want a button on the tool bar OR a user based form button that you can place on the cells of spreadsheet. You can save a spreadsheet to PDF and just point it to a location on SharePoint if you have OneDrive sync etc (Google the code). The email option should be doable as well and there is heaps of code on online for that also look into Mailmerge as I think that might be your answer. Google is your best friend. Do your best to cobble up some code, post it and im sure there will be plenty of people to assist you bring it together and fine tune it.

Another option is to look at alternate ways to accomplish your task without VBA exclusively. Microsoft powerapp or power automate if you have it available to you might also be another option.

Good luck.

I think I want to use a form button on the sheet for what I’m after. I have the completed form already. I have tried to play with some parts I have found online and never could get it to work for me, maybe I need to take a step back and try again and see what I come up with.

 

[Michael M]

Why not upload your workbook to a hosting site, Dropbox for instance, and someone may take a look at it for you.
A brief description of wehat you want to do would also help.

 

[Killragtshirts]

I think I want to use a form button on the sheet for what I’m after. I have the completed form already. I have tried to play with some parts I have found online and never could get it to work for me, maybe I need to take a step back and try again and see what I come up with.

Send a screen capture please of the form.

 

[C Nelson]

Why not upload your workbook to a hosting site, Dropbox for instance, and someone may take a look at it for you.
A brief description of wehat you want to do would also help.

I would love to but it’s business confidential I can’t. But I think in the next couple days I may throw together a dummy one that’s similar for that purpose.

 

[Michael M]

Ok, well keep an eye out.

 

[C Nelson]

Here is a code that creates the PDF.
It is also a very good resource page for heaps of other coding requirements.
The code is about 1/3 the way down the page

Save Excel to PDF-VBA Macro Step-by-Step Tips Video

How to save Excel sheets or workbook to PDF. Step-by-step or use time-saving Excel macro. Fix PDF error. PDF setup tips, video, download Excel file

www.contextures.com

Thanks I’ll take a look into this.