Allow User to Input Title When Running Attachment Macro

rdross51 · #1 02-24-2015, 02:37 AM

I have developed a macro to add an Attachment to the end of a document and set up a header for attachment title and page numbering. I am trying to develop an Input Box using VBA but I have no idea how to make it work. When the macro is run, I want an Input Box to appear so they can type in the title for the attachment and then that input would be entered into the header (Attachment 1 - "Title"). I have attached a copy of my macro and what the Input Box should look like. Can somone please help?

gmayor · #2 02-25-2015, 11:49 PM

You can't have 'attachments' to documents. You can embed documents and link to them or you can link to external doucments. Maybe you want a hyperlink as seems to be the inference from your attachment? In that case the hyperlink must be to a location that whoever opens the document will have access to. This suggests a web location.

If you can enlarge upon what you are trying to do then we may be able to help further. The page numbering in the header/footer is simpler, and does not require the header view to be opened. Use instead the header range for the header you wish to insert into, and let us know if there is anything already in the header/footer in question

rdross51 · #3 02-26-2015, 04:18 AM

We seem to have a different understanding of what I mean by attachments. I've attached a template that shows our procedure outline with 3 blank attachments. You can look at the current macros and maybe offer some suggestions.

gmayor · #4 02-26-2015, 05:40 AM

You are right we do have a different understanding. What you appear to be doing is inserting appendices. Frankly the simplest way to do that is to create and save the appendices as Building Blocks and insert them as required at the end of the document. If the choice of document varies, then create a userform with a list box from which to select the items to be inserted. http://www.gmayor.com/Userform.htm

rdross51 · #5 02-26-2015, 06:23 AM

Sounds like I need to bone up on Building Blocks. However, on the surface it seems like Building Blocks won't salve my problem with the different page numbering schemes in the header(Page x of x for document and Page y of y for appendice). I'm not a programmer so when you say create a userform with a list box to insert information (easy for you) is almost an impossible task for me. I know I'm sounding like I'm asking you to provide me with all the written script but at least point me in the direction where I can find how to do what you are asking.

gmayor · #6 02-26-2015, 07:36 AM

From what I can see of your code, you have umpteen macros that add to the document. If I am correct in that you want to select these from a userform, then create add a list box and two buttons to your userform1. Call the buttons btnOK and btnCancel. Leave the ListBox as Listbox1

Add the following code to the module with the attachments code:

Code:

Sub Add_Attachments()
Dim oHeader As HeaderFooter
Dim oRng As Range
Dim oFld As Range
Dim i As Long
Dim oFrm As New UserForm1
    With oFrm
        For i = 1 To 9
            .ListBox1.AddItem "Attachment " & i
        Next i
        .ListBox1.ListIndex = -1
        .Show
        If .Tag = 0 Then GoTo lbl_Exit
        For i = 0 To .ListBox1.ListCount - 1
            If .ListBox1.Selected(i) Then
                Set oRng = ActiveDocument.Range
                oRng.Collapse 0
                oRng.Select
                Select Case i
                    Case 0: Call Add_Att1
                    Case 1: Call Add_Att2
                    Case 2: Call Add_Att3
                    Case 3: Call Add_Att4
                    Case 4: Call Add_Att5
                    Case 5: Call Add_Att6
                    Case 6: Call Add_Att7
                    Case 7: Call Add_Att8
                    Case 8: Call Add_Att9
                End Select
            End If
        Next i
    End With
lbl_Exit:
    Exit Sub
End Sub

and add the following code to the userform

Code:

Private Sub btnCancel_Click()
    Me.Hide
    Me.Tag = 0
lbl_Exit:
Exit Sub
End Sub

Private Sub btnOK_Click()
Dim bSelected As Boolean
Dim i As Long
    With Me
        For i = 0 To .ListBox1.ListCount - 1
            If .ListBox1.Selected(i) Then
                bSelected = True
                Exit For
            End If
        Next i
        If Not bSelected Then
            MsgBox "Select at least one item from the list"
            .ListBox1.SetFocus
            GoTo lbl_Exit
        End If
        .Hide
        .Tag = 1
    End With
lbl_Exit:
    Exit Sub
End Sub

Note I have not made any attempt to debug your macros. This process merely calls them as your original request.
Save the document as a macro enabled template and create new documents from it.

rdross51 · #7 02-26-2015, 06:49 PM

I really appreciate your efforts in trying to resolve my problem. Looking at your code, it appears this is allowing me to select what attachment macro to run but it doesn't seem to address my original question of adding the attachment title.

gmayor · #8 02-26-2015, 10:43 PM

OK, then add a text box to the aforementioned userform and modify the code associated with the OK button as follows:

Code:

Private Sub btnOK_Click()
Dim bSelected As Boolean
Dim i As Long
    With Me
        If .TextBox1.Text = "" Then
            MsgBox "Enter the title"
            .TextBox1.SetFocus
            GoTo lbl_Exit
        End If
        For i = 0 To .ListBox1.ListCount - 1
            If .ListBox1.Selected(i) Then
                bSelected = True
                Exit For
            End If
        Next i
        If Not bSelected Then
            MsgBox "Select at least one item from the list"
            .ListBox1.SetFocus
            GoTo lbl_Exit
        End If
        .Hide
        .Tag = 1
    End With
lbl_Exit:
    Exit Sub
End Sub

The modify the names of each of your macros to enable the title from the text box to be passed e.g.

Code:

Sub Add_Att1(strTitle As String)

and then replace Title in your code with strTitle e.g.

Code:

Selection.TypeText Text:="Attachment 1 – " & strTitle

That will then insert the selected title where indicated. If your macros worked correctly before, then they should do so with the addition of the titles entered.

rdross51 · #9 02-27-2015, 02:40 AM

Thanks again for all your help and understanding. I'm certain that all of what you said and wrote makes perfect sense to a programmer but for me it's rather confusing. You assume I created a userform because of the graphic but that's all it is, a graphic. I have no clue as to how to attache a program to it or where to specifically place the codes you provided. Is the userbox a separate macro or part of the attachment macro? If it's not too much trouble could you develop the userform with instructions on where to put it and then modify the att1 macro? I could then modify the remaining attachment macros based on that.

gmayor · #10 02-27-2015, 02:59 AM

I have added the code into your document, so you can see what I mean.

I have not tested your macros. I have just added in the functionality.

rdross51 · #11 02-27-2015, 03:17 AM

THANK YOU!!! It works perfectly. I will now study your codes and try to understand what you did. I can't tell you how much I appreciate what you've done. It's going to make my team of writers very happy and amazed!

gmayor · #12 02-27-2015, 05:52 AM

That's a relief

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Taking input from InputBox from user	SeattleITguy	Excel Programming	1	01-28-2015 09:05 AM
Insert input box into macro to allow user to define search term	Hoxton118	Word VBA	3	05-19-2014 02:03 AM
Insert input box into macro to allow user to choose multiple text entries	Hoxton118	Word VBA	6	04-03-2014 12:12 AM
User input to a variable on the document	dsm1995gst	Word VBA	1	09-03-2013 03:43 PM
Word attachment on an E-mail-macro to open attachment &	nablady	Outlook	0	11-28-2006 03:00 PM