Opening Userform from button or menu

PhilTilson · #1 11-19-2021, 09:17 AM

I have a simple requirement.

The user types up a document and, when complete, clicks a menu item or toolbar button which brings up a VBA userform. The user answers a few questions on the form, clicks submit and the VBA then prints the document to a PDF file with a name constructed from the userform answers.

I have created the userform and tested it, and everything works fine. I am pretty sure I can achieve the print-to-PDF function without problem. What I cannot do is find out how to open the userform from within the document!

I have gone round and round in circles playing with macros, templates etc, but I just want a simple explanation of how I can 1) put a button on a toolbar (or create a menu item) and 2) have that button or menu open the userform for the author.

Please help!

gmayor · #2 11-19-2021, 09:56 PM

Either see Customize the Office Ribbon (It doesn't take rocket science)

or even simpler, create a macro to show your userform in the document template and run that macro when a new document is created from the template. The macro enabled template would have the userform, the macro(s) the document text and (preferably) content controls placed to accept the data from the userform. Create a new document from the template.

The following macros should be saved in the template. Change the name of the form as appropriate. You can call the macro to fill the content control(s) as often as required from your userform code and the PDF macro to finish.

Code:

Option Explicit

Sub AutoNew()
    ShowMyForm
End Sub

Sub ShowMyForm()
    frmFormName.Show
End Sub

Private Sub FillCC(strCCTitle As String, strValue As String, Optional bLock As Boolean)
'Graham Mayor - https://www.gmayor.com - Last updated - 03 Sep 2021
Dim oCC As ContentControl
    On Error GoTo lbl_Exit
    For Each oCC In ActiveDocument.ContentControls
        If oCC.Title = strCCTitle Then
            oCC.LockContents = False
            oCC.Range.Text = strValue
            oCC.LockContentControl = True
            If bLock = True Then oCC.LockContents = True
            Exit For
        End If
    Next oCC
lbl_Exit:
    Set oCC = Nothing
    Exit Sub
End Sub

Private Sub SaveAsPDF()
Dim strDocName As String
Dim strPath As String
Dim intPos As Integer
Start:
    On Error Resume Next
    ActiveDocument.Save
    If ActiveDocument.path = "" Then
        MsgBox "Document must be saved first!", vbCritical, "Save Document"
        GoTo Start
    End If
    On Error GoTo 0
    strDocName = ActiveDocument.Name
    strPath = ActiveDocument.path & "\"
    intPos = InStrRev(strDocName, ".")
    If intPos = 0 Then
        ActiveDocument.Save
        GoTo Start
    End If
    strDocName = Left(strDocName, intPos - 1)
    strDocName = strPath & strDocName & ".pdf"

    ActiveDocument.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
lbl_Exit:
    Exit Sub
End Sub

PhilTilson · #3 11-20-2021, 03:45 AM

Thanks for your helpful reply, Graham.

I will study this in more detail this morning, but the main thing that strikes me is that you (and many other references I have looked at!) are suggesting running the macro on opening the document. This is the problem...

The user will first create a new document, such as the agenda for a meeting and, when it is complete, then run the macro to bring up the user form. This form has some simple questions about the type of document, the body for which it has been written and the date of the document. From these replies it constructs the rather abstruse document name that the filing system requires for archiving - eg:

VHT Mtg 04 Agenda 17 Apr 2021.pdf

The filing system was not my idea! But, as you can see, there is plenty of opportunity to mess up the name structure, especially by a not-very-computer-literate operator, which is why I devised the form.

So how can I prevent the macro from running immediately, but delay it until the user has written the document and wants to file it?

gmayor · #4 11-20-2021, 10:01 PM

In that case you would have to create the ribbon button and ensure that your not very computer literate operator uses it.
Personally I would use the userform to create the document with all the variables available from the userform and changeable texts inserted using building blocks if they are too large for convenient insertion from code.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Userform button: the object invoked has disconnected from its clients	letcall	Word VBA	2	08-25-2021 07:58 AM
.DOTM files not opening as .DOCX with userform	mktate	Word VBA	3	02-26-2016 02:17 PM
Create a command button that will insert another drop down menu	Tinamation	Word VBA	1	11-06-2015 03:11 PM
Executing a userform with a command button	spc94	Word VBA	2	06-24-2015 10:08 PM
VBA Code in a UserForm module to delete a Command Button which opens the userform	Simoninparis	Word VBA	2	09-21-2014 03:50 AM