UserForm of Autotext Entries

ptmuldoon · #1 02-11-2016, 12:00 PM

Its been a while since I've last looked at any VBA, both word and excel and now trying to re-familiarize myself with everything.

I'm working to create a listing of autotext entries for my staff/users to have available to insert into various places in a document. And I'm creating a separate template/addin file that will store all of those autotext entries and load on startup.

Now, I want to be create a userform that will list those entries and the user can then insert it into the document.

Could someone help me remember/learn how to generate a list of autotext entries, preferable to only list them from a specific file, gallery, and category?

Thanks
PT

EDIT. Slowly Making Progress. I was able to learn how to list out the items in my template/add in file. I hope to learn now how to expand more on this to work with a chained select on the BuildingBlock Type and have a 'preview' of the building block value.

Code:

Private Sub UserForm_Initialize()
 Dim objTemplate As Template
    Dim oBuildingBlock As BuildingBlock
    Dim i As Integer
    Dim sPath As String

' Set the template to store the building block
sPath = Environ("APPDATA") & "\Microsoft\Word\STARTUP\ReportMacros.dotm"
Set objTemplate = Templates(sPath)

    For i = 1 To objTemplate.BuildingBlockEntries.Count
            Set oBuildingBlock = objTemplate.BuildingBlockEntries.Item(i)
            Debug.Print oBuildingBlock.Name + vbTab _
                + oBuildingBlock.Type.Name + vbTab _
                + oBuildingBlock.Category.Name + vbTab _
                + objTemplate.FullName
        If oBuildingBlock.Type.Name = "AutoText" Then
             ComboBox1.AddItem oBuildingBlock.Value
        End If
        Next
  
End Sub

ptmuldoon · #2 02-11-2016, 04:03 PM

I think I've made some considerable progress, but am currently getting an error when I attempt to change between the selections:

This is the entire userform, which has an 2 comboboxes, 1 text box and command button. And the autotext is being store in the 'ReportMacros' file that is loaded at the Word Startup

Code:

Option Explicit

Private Sub CommandButton1_Click()
    Selection.TypeText ComboBox2.Value
    'Unload Me
End Sub

Private Sub ComboBox1_Change()
    Dim index As Integer
    Dim objTemplate As Template
    Dim oBuildingBlock As BuildingBlock
    Dim i As Integer
    Dim sPath As String

    ' Set the template to store the building block
    sPath = Environ("APPDATA") & "\Microsoft\Word\STARTUP\ReportMacros.dotm"
    Set objTemplate = Templates(sPath)

    index = ComboBox1.ListIndex

    ComboBox2.Clear

    Select Case index
        Case Is = 0     'AR Ineligibles
            For i = 1 To objTemplate.BuildingBlockEntries.Count
                Set oBuildingBlock = objTemplate.BuildingBlockEntries.Item(i)
                If oBuildingBlock.Type.Name = "Custom AutoText" And oBuildingBlock.Category.Name = "AR Ineligibles" Then
                    Me.ComboBox2.AddItem oBuildingBlock.Name
                    Me.ComboBox2.Column(1, i - 1) = oBuildingBlock.Value
                End If
            Next i
            Me.ComboBox2.Text = Me.ComboBox2.List(0)
        
        Case Is = 1     'Inventory Ineligibles
            For i = 1 To objTemplate.BuildingBlockEntries.Count
                Set oBuildingBlock = objTemplate.BuildingBlockEntries.Item(i)
                If oBuildingBlock.Type.Name = "Custom AutoText" And oBuildingBlock.Category.Name = "Inv Ineligibles" Then
                    Me.ComboBox2.AddItem oBuildingBlock.Name
                    Me.ComboBox2.Column(1, i - 1) = oBuildingBlock.Value
                End If
            Next i
            Me.ComboBox2.Text = Me.ComboBox2.List(0)
    End Select

End Sub

Private Sub ComboBox2_Change()
    TextBox1.Value = ComboBox2.Value
End Sub


Private Sub UserForm_Initialize()
    
    With ComboBox1
        .AddItem "AR Ineligibles"
        .AddItem "Inv Ineligibles"
    End With
    Me.ComboBox1.Text = Me.ComboBox1.List(0)

End Sub

Currently, when I attempt to change between AR Ineligible and Inv Ineligible, I get an error message with the Case 1 of the on change event here?

Code:

 Me.ComboBox2.Column(1, i - 1) = oBuildingBlock.Value

Again, it is that line in Case 1, and not Case 0

The error Message is
Run Time Error '381'
Could not set the column property Invalid Property Array Index

Can anyone help with that error code?

gmaxey · #3 02-11-2016, 04:51 PM

Why are you trying to use column?

Code:

Private Sub ComboBox1_Change()
Dim lngIndex As Long
Dim objTemplate As Template
Dim oBuildingBlock As BuildingBlock
  Set objTemplate = Templates(Environ("APPDATA") & "\Microsoft\Word\STARTUP\ReportMacros.dotm")
  ComboBox2.Clear
  For lngIndex = 1 To objTemplate.BuildingBlockEntries.Count
    Set oBuildingBlock = objTemplate.BuildingBlockEntries.Item(lngIndex)
    If oBuildingBlock.Type.Name = "AutoText" And oBuildingBlock.Category.Name = ComboBox1 Then
      With ComboBox2
        .AddItem
        .List(.ListCount - 1, 0) = oBuildingBlock.Name
        .List(.ListCount - 1, 1) = oBuildingBlock.Value
      End With
    End If
  Next lngIndex
  ComboBox2.Text = ComboBox2.List(0)
lbl_Exit:
  Exit Sub
End Sub

ptmuldoon · #4 02-11-2016, 05:00 PM

Quote:

Originally Posted by gmaxey

Why are you trying to use column?

I guess that a good question, and the only real answer I have is that is what I was finding in searching out sample code on the web when having multiple columns.

But your code is so much cleaner and works perfectly. I knew their had to be a way to only loop the data once, and you made it so much easier to read.

I'll be incorporating this all into some RibbonX code as well so the user has a nice little way to open the userform.

ptmuldoon · #5 02-11-2016, 06:03 PM

Hi Greg (or others)

As a followup and as I begin to add more and more autotext entries. Is there a way to sort your autotext entries in a combo or drop down box? Either Alphabetic or a user defined order?

gmaxey · #6 02-11-2016, 06:04 PM

You might find this interesting:
http://gregmaxey.com/word_tip_pages/..._autotext.html

ptmuldoon · #7 02-12-2016, 08:25 AM

Thanks Greg

That info and your other one on modifying the Ribbon has me rethinking my overall approach. But with the current userform approach, I just noticed a strange behaviour in that the Combobox2.value is being truncated and not getting the full amount of the autotext entry? The Combobox2 Maxlength is currently set for 0 which I think means unlimited? I tried to change that to 50000 as well but it had no effect.

Any thoughts on why the value would be trunacated?

gmaxey · #8 02-12-2016, 09:23 AM

It appears that a list/combobox column limit is 2047 characters. Why are you trying to but the Value of an autotext in a combobox anyway?

ptmuldoon · #9 02-12-2016, 01:03 PM

Quote:

Originally Posted by gmaxey

It appears that a list/combobox column limit is 2047 characters. Why are you trying to but the Value of an autotext in a combobox anyway?

Well, my initial thought/logic was in creating a userform that that listed specific Autotext entry's, and the user could then chose the one they want from the form and insert the autotext into body of a document.

I guess I could rework the code to only include a Name of the Autotext item as the combobox value. And use the command button code to query the result and post that full autotext?

I'll be boarding a plane soon, so hope to do some experimenting while traveling later today. I forgot how much fun this stuff could be

EDIT.

I just tried this in a command button, but seems the text being added to the document was still truncated?

Code:

For lngIndex = 1 To objTemplate.BuildingBlockEntries.Count
    Set oBuildingBlock = objTemplate.BuildingBlockEntries.Item(lngIndex)
    If oBuildingBlock.Name = ComboBox2.Text Then
      Selection.TypeText oBuildingBlock.Value
    End If
  Next lngIndex

ptmuldoon · #10 02-12-2016, 08:08 PM

In continuing to learn, I believe I have learned the underlying cause is that VBA will truncate a string down to 255 characters.

Greg, I found an old post of yours here helping with a similar issue and the solution appears to insert the buildingblock into a temp file and then set a string variable to that selection. That old post is here:

http://www.vbaexpress.com/forum/arch...p/t-44367.html

I think I got it working with this, and just need to change the hardcoded building block name to a variable.

Code:

Sub BuildingBlockMsgBox()
Dim oTmp As Template
Dim sPath As String
Dim oScratchPad As Word.Document
Dim oRng As Word.Range

sPath = Environ("APPDATA") & "\Microsoft\Word\STARTUP\ReportMacros.dotm"
Set oTmp = Templates(sPath)

'oTmp.BuildingBlockEntries("Foreign AR").Insert Selection.Range
Set oScratchPad = Documents.Add(, , , False)
Set oRng = oTmp.BuildingBlockEntries("Foreign AR").Insert(Where:=oScratchPad.Selection.Range, RichText:=True)
MsgBox oRng
oScratchPad.Close wdDoNotSaveChanges
Set oScratchPad = Nothing

End Sub

gmaxey · #11 02-13-2016, 07:02 AM

pt,

That won't change the fact that the combobox list column limite is 2047 characters (or at least appears so) or help explain why you would want to put the value of a building block in a combobox column. What is your ultimate goal? If it is to insert building block content into a document then I think you are going about it in the wrong way.

Consider:

Note: Obviously I am not using your template, using AutoText (9) vice CustomAutoText, and my categories are "General" and "Signatures"

Code:

Option Explicit
Dim objTemplate As Template
Private Sub UserForm_Initialize()
  With ComboBox1
    .AddItem "General"
    .AddItem "Signatures"
  End With
lbl_Exit:
  Exit Sub
End Sub
Private Sub ComboBox1_Change()
Dim lngIndex As Long
Dim oBB As BuildingBlock
Dim oCat As Category
  'Set objTemplate = Templates(Environ("APPDATA") & "\Microsoft\Word\STARTUP\ReportMacros.dotm")
  Set objTemplate = Templates(ThisDocument.AttachedTemplate.FullName)
  ComboBox2.Clear
  Set oCat = objTemplate.BuildingBlockTypes(9).Categories(ComboBox1)
  For lngIndex = 1 To oCat.BuildingBlocks.Count
    Set oBB = oCat.BuildingBlocks(lngIndex)
    With ComboBox2
      .AddItem
      .List(.ListCount - 1, 0) = oBB.Name
      .List(.ListCount - 1, 1) = oBB.Type.Index
      .List(.ListCount - 1, 2) = oBB.Category.Name
    End With
  Next lngIndex
  ComboBox2.Text = ComboBox2.List(0)
lbl_Exit:
  Exit Sub
End Sub
Private Sub CommandButton1_Click()
  objTemplate.BuildingBlockTypes(ComboBox2.Column(1)).Categories(ComboBox2.Column(2)).BuildingBlocks(ComboBox2.Column(0)).Insert Where:=Selection.Range, RichText:=True
  Hide
lbl_Exit:
  Exit Sub
End Sub

ptmuldoon · #12 02-13-2016, 09:36 AM

Greg

I didn't really need to place the value of the building block into the combobox value. But what I was trying to do was to a list in a userform a selection of building/block auto entries. And within the userform, the building block item would be 'previewed' in a textbox. The user could then read the entry and edit if they wanted to could edit the entry/textbox before inserting it into the document.

And I think I've got that working now as well, although after learning I can just modify the ribbon to include just something like CustomGallery1, etc that almost seems a much simplier approach

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Userform calls other userform, then populate worksheet	Lehoi	Excel Programming	0	02-03-2016 02:58 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
Is it possible to take an input from a UserForm in one document to a UserForm in a do	BoringDavid	Word VBA	5	05-09-2014 09:08 AM
Using Word autocorrect and autotext entries in outlook	dswapp	Outlook	2	11-24-2010 10:18 AM
Maintaining AUtotext Entries from an external table.	kelzud	Word Tables	0	07-28-2009 01:31 PM