How do i get Combobox to insert Autotext at Bookmark?

Hi all, I have a document with bookmarks throughout and a few userforms. I need the combo box on the userform to have a simple list of options that inserts autotext entries or similar into bookmarks whilst maintaining the original formatting. This will just be used to insert addresses so the user can just select the office location and all address fields will be automatically populated.

I should point out I have no IT qualifications and have so far spent a few hours looking into VBA to program exactly what I need only, so I ideally want the absolute simplest answer possible that a complete beginner can understand and troubleshoot. I can always modify it at a later date.

I have looked on the internet already but the code I have come across varies on every answer and seems unnecessarily complex. I was really hoping for the VBA equivalent of if combo box option = 1, insert auto text 1 at bookmark 1 or something that simple just to start with. Any help at all would be much appreciated though.

Cheers

This is fairly straightforward e.g.

Code:

---

Option Explicit

Private Sub CommandButton1_Click()
    Select Case ComboBox1.ListIndex
        Case 0    'First item
            AutoTextToBM "BookmarkName", "Template", "Autotext Entry1"
        Case 1
            AutoTextToBM "BookmarkName", "Template", "Autotext Entry2"
        'etc
        Case Else
    End Select
End Sub

Sub AutoTextToBM(strbmName As String, oTemplate As Template, strAutotext As String)
'Graham Mayor - http://www.gmayor.com - Last updated - 17 Jul 2018
'strBMName is the name of the bookmark to fill
'oTemplate is the template with the autotext - probably ActiveDocument.AttachedTemplate
'strAutotext is the name of the autotext entry
Dim oRng As Range
    On Error GoTo lbl_Exit
    With ActiveDocument
        Set oRng = .Bookmarks(strbmName).Range
        Set oRng = oTemplate.AutoTextEntries(strAutotext).Insert _
                  (Where:=oRng, RichText:=True)
        .Bookmarks.Add Name:=strbmName, Range:=oRng
    End With
lbl_Exit:
    Exit Sub
End Sub

---

Okay so the first part makes sense to me, although this wont be a template people select as such, it will just be a document saved to their desktop so I assume I don't need the "Template" in the first section or would I just put the document name?

However it seems the first section up to End Sub does what I want, so what is the second section for? And would I need to repeat the second section for each entry in the combobox?

Thanks for the answer too. I'm just struggling to find a good complete beginners resource that I can learn from as most places give code but seem to be more oriented towards people with some programming experience which, unfortunately, I do not have.

You cannot save autotexts in a document - only in a template, so if you are using autotexts the user will need access to the template with those texts and that is the template referred to.
The second part of the code is what inserts the autotext entries at the bookmarks and is required. It is called for each entry in the combobox from the first macro. Entries in a combo box start from item 0 (as shown). I have only shown two entries, but you need a similar entry for each combo box item with the bookmark name, the path of the template and the autotext name from that template.

Oh okay i get you. It's a template i will be using, essentially it will be opened and used throughout the day but will never be saved over. It just needs to be cleared or I suppose it would be closed and reopened after each use to clear it so i guess i could save the autotext entries in it if it's a template and then use "ActiveDocument" instead of "Template" to call the entries?

You create new documents from templates. It is not meant to be used as you describe, nor will the change you describe work.

I was hoping to do it that way as using it as a template would stop people saving over it. I know i can protect a document but it causes other issues such as stopping hyperlinks from working. Is there an alternative to using autotext then such as just storing addresses in a textbox and having it select and copy the textbox into the section the address should be placed?

If users create new documents from a template, the template is not going to be written over. The new document will be a new unnamed document with the content from the template. You don't 'open' templates in order to use them. If you are using autotexts then they need to be stored in the document template, or you could simply hard code the addresses into the code associated with the userform - and instead of bookmarks, which are easily overwritten, you could write to named content controls. If the address is to be repeated in several places then use mapped content controls. I suppose it depends on how many addresses are involved and who is completing the document.

It has been a while since i looked at it and as it turns out i had actually put the spaces for the addresses as content controls not bookmarks. Would it be easier to have a textbox on the userform below the combo box and, somehow, have the dropdown input the text into that textbox then copy that to the content control?

As background what i want is a list of 10-15 countries in the combo box and when the country name is selected a full address that is assigned to that country appears in the content control in the document but obviously the address will have several lines to it rather than just being all on 1 line. I really don't mind if i need to write this into autotext, other content controls in the document, textboxes or the vba code. I am really just looking for the absolute simplest way to acheive this since my knowledge of vba is so poor.

It doesn't matter how you do it, but somewhere you are going to have to store the list of addresses which the user of the template will have access to. Associating those addresses with the items in the combobox and the insertion of the address so associate are the easy parts. The more difficult part is establishing which is the best method to use for your circumstances. The attached uses hard coded addresses, each line of which is separated by a pipe character '|'. Modify it to suit your requirements.

Okay i am making some progress. I had been working on it before you posted back and have the following.

Sub UserForm_Intitialize ()

With ComboBox1

.AddItem "England"
.AddItem "France" 'etc.

End With

End Sub

Private Sub ComboBox1_Change ()

If ComboBox1.Value = "England" Then
Textbox1.Value = "I put the full address here"

ElseIF ComboBox1.Value = "France" Then
TextBox1.Value = "Again I put the full address here" 'etc.

End If

End Sub


I have then put textbox 1 on the userform and tested it and it does copy the exact address so I could use this to copy to my content control but it does not break into separate lines for each line of the address.

I looked through the code you gave but I am not 100% clear on which parts are needed solely for inserting the line break since you had written the entire sub for everything (thanks by the way).

How could I now modify this just to add that one feature. I should add that I have defined no variables at all so they just have standard names such as combobox1, userform1 and textbox1.

You need to set the text box multi line option to sisplay multiple lines e.g.

Code:

---

Option Explicit

Private Sub UserForm_Initialize()
    With ComboBox1
        .AddItem "England"
        .AddItem "France"    'etc.
    End With
    TextBox1.MultiLine = True
End Sub

Private Sub ComboBox1_Change()

    If ComboBox1.value = "England" Then
        TextBox1.value = "Address Line 1" & Chr(11) & _
                        "Address Line 2" & Chr(11) & _
                        "Address Line 3"

    ElseIf ComboBox1.value = "France" Then
        TextBox1.value = "Again I put the full address here"    'etc.

    End If

End Sub

---

That was much, much simpler than i was expecting but it works perfectly. Thank you very much for that!

Slightly unrelated but the same useform has a multipage tab. Page 1 has a checkbox which enables page 2, otherwise page 2 is not visible. I can use the command buttons to go to the next page whether or not this checkbox is selected. I can also use the command button to go to the previous pages if the checkbox IS enabled, but, I cannot go from page 3 to page 1 using the command button when the checkbox is disabled. This sounds logical but why will it skip forward from 1 to 3 but not back from 3 to 1?

The following is the simple code I used for the command buttons and to show page 2 as visible or not.

MultiPage1.Value = MultiPage1.Value + 1

MultiPage1.Value = MultiPage1.Value - 1

-----------------------------------------
With MultiPage1
.Page2.Visible = False

End With (In Userform_Initialize)
-----------------------------------------


Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
MultiPage1.Page2.Visible = True
Else
MultiPage1.Page2.Visible = False

End If

End Sub

Not really sure why that is not working as expected?

Hi Graham,

I have a similar problem to this forum (the original problem kibl1 was trying to solve). I can populate my combo box with 3 names that the user can choose. I have also made a bookmark in the document to where the autotext needs to go. My 3 auto texts are saved in the actual template as building blocks (under service description category). I have used ActiveDocument.AttachedTemplate like you suggested in your function but I get a type mismatch error.
I made sure the name of my bookmark is correct and the names of the three service descriptions (the building block names) are also correct.

Could you have a look and give me some guidance please? My apologies if my approach is a bit silly, I am new to to VBA. Attachment 25907

Cheers
Ramin

Your attachment is invalid, however the attached contains code to insert autotexts stored in the attachment to both a content control (preferred) and a bookmark (already present) based on a selection in the userform. Create a new document from the template.

Thanks a lot Graham,

The code you shared in 2018 actually does the trick; I just had to " " around the word "Template" in your code because obviously it is not a string. So that code works perfectly well now with my attachment. The joys of not knowing VB and wanting to automate things. Thanks so much for your help.