Command Button Macros

[kibl1]

Hi all, new here and new to VBA.

I am just starting to learn this and have no experience at all of programming or VBA.

I have a macro enabled template which contains a list of hyperlinks to forms and pages within said template. I would like to include several macros to simplify processes and reduce user error.

I would like one macro to print just the page that form is on, another to print several pages or a section between breaks as the document is several pages long, another to save and one to save and send but again only current page or a range of pages and ideally would like to be able to input detals such as order number on one form which will automatically fill all order number fields throughout the form. It would also be very useful to have a clear template button to completely reset the template without having to close and reopen it.

The buttons should be invisible once printed, saved or sent too and the user should have a choice of which printer to select, where to save too and the macro should only open outlook and attatch the document giving the user freedom to select who to send it too.

I would be happy to do this in excel if it is easier. The trouble i am having is getting to grips with when to use each section of code such as apostrophes, brackets, application, wd, ms etc. I don't know how your supposed to know this.

If anyone can point me in the right direction as i am sure these macros already exist or even suggest a good learning resource for complete begginers or just give me some general tips i would be grateful.

Thanks

[gmayor]

There are lots of code examples on my web site and elsewhere on the internet. Don't put buttons on your document - add a ribbon to your template http://gregmaxey.mvps.org/word_tip_pages/customize_ribbon_main.html with the buttons. Don't clear the existing form. Create a new document from the template.

You could use a mapped content control to reproduce the number throughout the document. https://www.gmayor.com/insert_content_control_addin.htm should make the easy.

Here are a couple of macros to get you started. One simple. The other less so.

To print the current page:

Sub PrintPage()
Application.PrintOut Range:=wdPrintCurrentPage
lbl_Exit:
Exit Sub
End Sub
To e-mail the document

Sub SendDoc()
'Graham Mayor - http://www.gmayor.com - Last updated - 10/12/2016
Const strMsg As String = "Please find the enclosed document" 'the message body text
Const strRecipient As String = "someoneATsomewhere.com" 'the e-mail address of the intended recipient
Const strSubject As String = "Attached Form" 'the message subject
Dim sPath As String
sPath = Environ("TEMP") & "\Form Document.docx" 'the temporary filename
ActiveDocument.SaveAs2 FileName:=sPath, AddToRecentFiles:=False

Send_As_Mail strTo:=strRecipient, _
strSubject:=strSubject, _
strMessage:=strMsg, _
bSendAsAttachment:=True

ActiveDocument.Close 0
'create another document
'If MsgBox("Create another form?", vbYesNo) = vbYes Then
' Documents.Add Template:=ThisDocument.FullName
'End If
lbl_Exit:
Exit Sub
End Sub

Public Sub Send_As_Mail(strTo As String, _
strSubject As String, _
strMessage As String, _
Optional bSendAsAttachment As Boolean, _
Optional bPDFFormat As Boolean, _
Optional strAttachment As String)
'Graham Mayor - http://www.gmayor.com - Last updated - 10/12/2016

'bSendAsAttachment - Enter True/False - indicate whether to send the active document as an attachment
'Requires the code from - http://www.rondebruin.nl/win/s1/outlook/openclose.htm
'to either retrieve an open instance of Outlook or open Outlook if it is closed.
Dim olApp As Object
Dim olInsp As Object
Dim oItem As Object
Dim wdDoc As Object
Dim oRng As Object
Dim bStarted As Boolean
Dim oDoc As Document
Dim strDocName As String
Dim strPath As String
Dim intPos As Integer
Dim iFormat As Long

Set oDoc = ActiveDocument
If Not bSendAsAttachment Then oDoc.Range.Copy
If bSendAsAttachment Then
'On Error GoTo Err_Handler
'Prompt the user to save the document
If bPDFFormat Then
strDocName = oDoc.Name
strPath = oDoc.Path & "\"
intPos = InStrRev(strDocName, ".")
strDocName = Left(strDocName, intPos - 1)
strDocName = strPath & strDocName & ".pdf"
oDoc.ExportAsFixedFormat OutputFilename:=strDocName, _
ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=False, _
OptimizeFor:=wdExportOptimizeForPrint, _
Range:=wdExportAllDocument, From:=1, to:=1, _
Item:=wdExportDocumentContent, _
IncludeDocProps:=True, _
KeepIRM:=True, _
CreateBookmarks:=wdExportCreateHeadingBookmarks, _
DocStructureTags:=True, _
BitmapMissingFonts:=True, _
UseISO19005_1:=False
Else
strDocName = oDoc.FullName
End If
End If
Set olApp = OutlookApp()

'Create a new mailitem
Set oItem = olApp.CreateItem(0)

With oItem
.to = strTo
.Subject = strSubject
If bSendAsAttachment Then .Attachments.Add strDocName
If Not strAttachment = "" Then .Attachments.Add strAttachment
.BodyFormat = 2 'olFormatHTML
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor
Set oRng = wdDoc.Range
oRng.Collapse 1
.Display
If bSendAsAttachment Then
oRng.Text = strMessage & vbCr
Else
oRng.Paste
End If
'.Send 'restore after testing
End With
If bStarted Then olApp.Quit
lbl_Exit:
Set oItem = Nothing
Set olApp = Nothing
Set olInsp = Nothing
Set wdDoc = Nothing
Set oDoc = Nothing
Set oRng = Nothing
Exit Sub
err_Handler:
Err.Clear
GoTo lbl_Exit
End Sub

[kibl1]

That was more than I was expecting to get in response :) . Thanks. Some of that looks rather confusing but I'm sure it will be clearer to me once I've had time to go through it properly.

I was experimenting too and have come across an issue that is driving me mad. I put a "Legacy Frame" in the template and put form fields in that to link to other form fields with "Ref" bookmarks (in the first form fields). This worked and allowed me too duplicate what I put in the fields in page 1 throughout the template everywhere the Ref field was. However I have to have restricted editing on for this and using restricted editing blocks my hyperlinks from working. I see both of these things as integral to the template. I read that you can "group" fields to stop this but that didn't work. I read about using macro buttons for hyperlinks in sort of double form fields to call the macro but this seems very over engineered for a simple hyperlink. Surely there is a way to have hyperlinks on a protected document, I mean I only want people to be able to click them not edit them. I have also read about section breaks which may work but instead of having a shopping list style of hyperlinks it creates several lines between each one (Legacy frame/ fields is at the side of all the hyperlinks on the same page) so they end up taking up several pages rather than being an interactive index.

Any suggestions on this?

Oh and the reason I was going to put buttons on the page was that I know people who use it will just go file-print if I don't and print a 200 page template instead of a one page letter. I was going to use these buttons (relying on peoples inherent laziness) to negate this problem.

Thanks again for your reply so far, I will study it closely and see what I learn.

[gmayor]

Use content controls instead of legacy form fields as they don't require the document to be protected
- see https://www.gmayor.com/insert_content_control_addin.htm
and
https://www.gmayor.com/ExtractDataFromForms.htm includes a utility to convert form fields to content controls.
As for the buttons - it doesn't matter what you do, users will always conspire to wreck it.

[kibl1]

I did actually look at that article. I would be using the document at work and they have extremely tight restrictions on IT. Would installing an add on trigger a red flag for them or is it not something that would be detected? I can build the document at home but just want to make sure it will work at my office and not be picked up ad unauthorised.

[gmayor]

You don't need the add-in order to insert content controls. It just makes the task far more simple and logical. If you built the document at home the content controls inserted would be no different from those inserted manually. If you want the same data repeated throughout the document, you would need to map the controls then copy and paste them to the separate locations. The mapping is much more complex to handle manually - though of course it can be done.

You should not install third party products - even mine :) - on a company PC without seeking permission to do so.

[kibl1]

Update on this. I have put content controls in and had it working but every solution seems to cause a dozen more problems. I cannot get fields within tables to update with all the other fields. I have tried lots of code but none of it seems to work (think one was from your site). I had one section of code which did work once but then gave an "ambigous" name error. I have had all sorts of other errors too.
The funny thing is simply going to print preview and back updates it but i cannot find a macro that enters and exits print preview and it will not record a macro that does that either.
This is starting to peeve me a little.
I suppose i could retype all the tables if necessary but to get the formatting correct would be extremely time consuming and there really should be a way if the print preview method works so quickly and easily but i have a button i want to put to click to update all the fields.

Any ideas?