Consulting

Results 1 to 7 of 7

Thread: Command Button Macros

  1. #1
    VBAX Newbie
    Joined
    Jun 2018
    Posts
    4
    Location

    Command Button Macros

    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

  2. #2
    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_p...bbon_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
    Graham Mayor - MS MVP (Word)
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Newbie
    Joined
    Jun 2018
    Posts
    4
    Location
    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.

  4. #4
    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.
    Graham Mayor - MS MVP (Word)
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    VBAX Newbie
    Joined
    Jun 2018
    Posts
    4
    Location
    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.

  6. #6
    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.
    Graham Mayor - MS MVP (Word)
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  7. #7
    VBAX Newbie
    Joined
    Jun 2018
    Posts
    4
    Location
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •