Need Help to populate a combobox with specific autotext entries

[susiecc via OfficeKB.com]

I'm hoping someone can help.

I created a combobox that a user enters names and they are listed in the
combobox. A user can then select one of the names and it inserts it into text,
just like an autotext entry.

Of course, when the userform is closed the combobox entries disappear. so, I
decided to try and find a way to repopulate the box.

I created a macro that adds each of the items (when inserted into text) into
the AutoText Entries. I then created a macro to extract these and put them
back into the list.

Unfortunately, when it does this, it also puts all of the predefined Word
AutoText entries into the list as well. I don't want these; I just want the
ones that the user defined.

So, my question is...is there some way to search the AutoText to find those
entries, based on a grouping (created a group style) and tell my macro to add
those only? I don't want all those other listing repopulating my form.

I really would appreciate some help on this.

thanks in advance.

susiecc

 

[Doug Robbins - Word MVP]

How/where does the user enter the names into the combobox in the first
place.

If you are the Susie that I think you are, I earlier gave you some code that
will load a combo box from data that is stored in a separate Word document.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[susiecc via OfficeKB.com]

Hi Doug,

First... I created a user form with a textbox where the user enters names.
when they press the return key on the keyboard, the name is transferred to a
combox in the same user form. the combobox will contain all the entered items,
up to 25. the user can then select from this list and the text that is
selected will be inserted into the document, where their cursor is.

I did previously post something to ask how to retain that entered list, but I
don't believe the response was from you. the person told me to use the
registry to store items. i can't do this because of restrictions at my office.


if you did send me how to store in a text document and use to repopulate the
combobox, I don't remember. I do save all items from posts, but I had a
computer die and lost quite a bit of information.

To be honest, a text document would be preferable to the autotext solution.
If you have this still, I would be grateful if you could post it again.

I did go through all the posts and couldn't find anything.

thanks you Doug.

susie

How/where does the user enter the names into the combobox in the first
place.

If you are the Susie that I think you are, I earlier gave you some code that
will load a combo box from data that is stored in a separate Word document.

I'm hoping someone can help.

[quoted text clipped - 28 lines]

susiecc

 

[Doug Robbins - Word MVP]

It might have been a different Susie then.

This routine loads a listbox (or it could be a combobox) with client details
stored in a table in a separate
document (which makes it easy to maintain with additions, deletions etc.),
that document being saved as Clients.Doc for the following code.

On the UserForm, have a list box (ListBox1) and a Command Button
(CommandButton1) and use the following code in the UserForm_Initialize() and
the CommandButton1_Click() routines

Private Sub UserForm_Initialize()
Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range,
m As Long, n As Long
' Modify the path in the following line so that it matches where you
saved Clients.doc
Application.ScreenUpdating = False
' Open the file containing the client details
Set sourcedoc = Documents.Open(FileName:="c:\Company.doc")
' Get the number or clients = number of rows in the table of client
details less one
i = sourcedoc.Tables(1).Rows.Count - 1
' Get the number of columns in the table of client details
j = sourcedoc.Tables(1).Columns.Count
' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
ListBox1.ColumnCount = j
' Define an array to be loaded with the client data
Dim MyArray() As Variant
'Load client data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
myitem.End = myitem.End - 1
MyArray(m, n) = myitem.Text
Next m
Next n
' Load data into ListBox1
ListBox1.List() = MyArray
' Close the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Hi Doug,

First... I created a user form with a textbox where the user enters names.
when they press the return key on the keyboard, the name is transferred to
a
combox in the same user form. the combobox will contain all the entered
items,
up to 25. the user can then select from this list and the text that is
selected will be inserted into the document, where their cursor is.

I did previously post something to ask how to retain that entered list,
but I
don't believe the response was from you. the person told me to use the
registry to store items. i can't do this because of restrictions at my
office.


if you did send me how to store in a text document and use to repopulate
the
combobox, I don't remember. I do save all items from posts, but I had a
computer die and lost quite a bit of information.

To be honest, a text document would be preferable to the autotext
solution.
If you have this still, I would be grateful if you could post it again.

I did go through all the posts and couldn't find anything.

thanks you Doug.

susie

How/where does the user enter the names into the combobox in the first
place.

If you are the Susie that I think you are, I earlier gave you some code
that
will load a combo box from data that is stored in a separate Word
document.

I'm hoping someone can help.

[quoted text clipped - 28 lines]

susiecc

--
Sue Caulfield

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/word-programming/200907/1

 

[Doug Robbins - Word MVP]

Alsoseecthe following page of fellow MVP Greg Maxey's website :

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Hi Doug,

First... I created a user form with a textbox where the user enters names.
when they press the return key on the keyboard, the name is transferred to
a
combox in the same user form. the combobox will contain all the entered
items,
up to 25. the user can then select from this list and the text that is
selected will be inserted into the document, where their cursor is.

I did previously post something to ask how to retain that entered list,
but I
don't believe the response was from you. the person told me to use the
registry to store items. i can't do this because of restrictions at my
office.


if you did send me how to store in a text document and use to repopulate
the
combobox, I don't remember. I do save all items from posts, but I had a
computer die and lost quite a bit of information.

To be honest, a text document would be preferable to the autotext
solution.
If you have this still, I would be grateful if you could post it again.

I did go through all the posts and couldn't find anything.

thanks you Doug.

susie

How/where does the user enter the names into the combobox in the first
place.

If you are the Susie that I think you are, I earlier gave you some code
that
will load a combo box from data that is stored in a separate Word
document.

I'm hoping someone can help.

[quoted text clipped - 28 lines]

susiecc

--
Sue Caulfield

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/word-programming/200907/1

 

[susiecc via OfficeKB.com]

Hi Doug,

Thanks. I'm having a bit of a problem making this work correctly.

First, I can't get the sourcedoc to populate with the combobox list items at
all.

Then, i simply tried manually entering an item in the sourcedoc. the code
opened and closed the sourcedoc, but after it was done executing, there was
still no entries in the combobox.
The table is in the document after execution, the entries I added by hand are
there...but nothing goes into my list.

I'm sure this is enduser error.

Alsoseecthe following page of fellow MVP Greg Maxey's website :

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

Hi Doug,

[quoted text clipped - 40 lines]

 

[Doug Robbins - Word MVP]

The code is not intended to populate the sourcedoc. You must set up the
entries in that document manually and save it. If this line of code

Set sourcedoc = Documents.Open(FileName:="c:\Company.doc")

then contains the correct path and filename for the sourcedoc, that document
will be opened and the combobox will be populated with the data from it.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Hi Doug,

Thanks. I'm having a bit of a problem making this work correctly.

First, I can't get the sourcedoc to populate with the combobox list items
at
all.

Then, i simply tried manually entering an item in the sourcedoc. the code
opened and closed the sourcedoc, but after it was done executing, there
was
still no entries in the combobox.
The table is in the document after execution, the entries I added by hand
are
there...but nothing goes into my list.

I'm sure this is enduser error.

Alsoseecthe following page of fellow MVP Greg Maxey's website :

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

Hi Doug,

[quoted text clipped - 40 lines]

susiecc

 

[susiecc via OfficeKB.com]

Hi Doug,

Thank you. I figured out how to populate the sourcedoc from an array of
combobox data.

So, I incorporated your code into the userform initialization, but, it still
errored.

It is erroring on the load data line. is this because my box type is a
combobox?

i.e. ComboBox1.list() = myarray

--------

The code is not intended to populate the sourcedoc. You must set up the
entries in that document manually and save it. If this line of code

Set sourcedoc = Documents.Open(FileName:="c:\Company.doc")

then contains the correct path and filename for the sourcedoc, that document
will be opened and the combobox will be populated with the data from it.

Hi Doug,

[quoted text clipped - 23 lines]

 

[Doug Robbins - Word MVP]

I do not understand why you are populating the sourcedoc form an array of
combobox data.

It sounds like it is back to front to me. I would be expecting you to be
loading the combobox with the items from the sourcedoc. At least that is
what the code that I had given you is doing.

Show us ALL of the code that you are using so that I can try and understand
what you are doing.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Hi Doug,

Thank you. I figured out how to populate the sourcedoc from an array of
combobox data.

So, I incorporated your code into the userform initialization, but, it
still
errored.

It is erroring on the load data line. is this because my box type is a
combobox?

i.e. ComboBox1.list() = myarray

--------

The code is not intended to populate the sourcedoc. You must set up the
entries in that document manually and save it. If this line of code

Set sourcedoc = Documents.Open(FileName:="c:\Company.doc")

then contains the correct path and filename for the sourcedoc, that
document
will be opened and the combobox will be populated with the data from it.

Hi Doug,

[quoted text clipped - 23 lines]

susiecc

--
Sue Caulfield

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/word-programming/200907/1

 

[Russ]

Susie,
See reply in middle of your text.

I'm hoping someone can help.

I created a combobox that a user enters names and they are listed in the
combobox. A user can then select one of the names and it inserts it into text,
just like an autotext entry.

Of course, when the userform is closed the combobox entries disappear. so, I
decided to try and find a way to repopulate the box.

I created a macro that adds each of the items (when inserted into text) into
the AutoText Entries. I then created a macro to extract these and put them
back into the list.

Unfortunately, when it does this, it also puts all of the predefined Word
AutoText entries into the list as well. I don't want these; I just want the
ones that the user defined.

So, my question is...is there some way to search the AutoText to find those
entries, based on a grouping (created a group style) and tell my macro to add
those only? I don't want all those other listing repopulating my form.

As you see in step 6 on this webpage below you can group items in an
AutoTextList drop down list by making those items a custom 'made-up style',
if you give them an unique style name. (even if it shares all attributes
with another existing style)

http://word.mvps.org/FAQs/TblsFldsFms/AutoTextList.htm

 

[susiecc via OfficeKB.com]

Hi Doug,

Each user is creating their own custom list of terms. These terms don't
remain static. For each project they work on, the terms change. Within the
project, they may add additional terms to the list they are using. Those
terms need to stay for the project they are working on. After they are done
with that particular project, the terms are removed. The next project may
have some of the same terms or completely different ones. Some of the users
wanted the terms that they entered (on the fly) to stay in the list for a
long as they worked on the project. Other users didn't care, because they
didn't use the form to enter their own.

I changed from a combobox to a listbox to see if that would make a difference.
it didn't.

here's the code, slightly modified. I assume I made an error and that is why
it's not working.
'--------------------

Sub LoadCustomTagBar()
Load UserForm

'-----------------------------------
Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range, m As
Long, n As Long
Application.ScreenUpdating = False
Set sourcedoc = Documents.Open(FileName:="c:\users\myprofile\Documents\
LISTSOURCE.DOC")
i = 25 'sourcedoc.Tables(1).Rows.Count - 1

j = 1 ' sourcedoc.Tables(1).Columns.Count

ListBox1.ColumnCount = 1
Dim myarray1() As Variant
ReDim myarray1(i, j)

For m = 1 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m, 1).Range
myarray1(m, 1) = myitem.Text

Next m
ListBox1.List() = myarray1() [[THIS IS WHERE IT ERRORS]]

sourcedoc.Close savechanges:=wdDoNotSaveChanges
UserForm.Show
End Sub


It's reading some odd stuff from the table...something like a dot. I don't
know where that's coming from. i put a message box in to check whether it was
reading the text and it seems to be, but this dot or period is also coming up.


the code i used to populate the list doesn't seem to make a difference. I
manually input a list and it did the same thing.

here is the code, just in case, that i used to populate the document table:

--------------------------------------------------
Private Sub CommandButton2_Click()

Dim myarray As Variant
Dim i As Long
Set sourcedoc = Documents.Open(FileName:="c:\Users\myprofile\Documents\
LISTSOURCE.DOC")

myarray = ComboBox1.List()
For i = 0 To UBound(myarray)
MsgBox myarray(i, 0)

'-------------------

If ActiveDocument.Tables.Count >= 1 Then
With ActiveDocument.Tables(1).Cell(Row:=i, Column:=1).Range
.Delete
.InsertAfter myarray(i, 0)

End With
End If

Next i

sourcedoc.Close savechanges:=wdSaveChanges

End Sub

---------------------------------------------------------------
this seems to be working ok.

here is the code where the user enters the text...
-----------------------------------------------------------------------
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode = vbKeyReturn Then
KeyCode = 0
ComboBox1.AddItem TextBox1.Text, 0
TextBox1.Text = ""
TextBox1.SetFocus

End If
TextBox1.Cut
'-----------------------------------------------------------------------------
---------------------------------------
Thanks for your help.

sue

'---------------------------------------------------

I do not understand why you are populating the sourcedoc form an array of
combobox data.

It sounds like it is back to front to me. I would be expecting you to be
loading the combobox with the items from the sourcedoc. At least that is
what the code that I had given you is doing.

Show us ALL of the code that you are using so that I can try and understand
what you are doing.

Hi Doug,

[quoted text clipped - 26 lines]

 

[Doug Robbins - Word MVP]

You need to insert

myitem.End = myitem.End - 1

into

For m = 1 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m, 1).Range
myarray1(m, 1) = myitem.Text

so that you have

For m = 1 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m, 1).Range
myitem.End = myitem.End - 1
myarray1(m, 1) = myitem.Text

as in the code that I gave you originally to strip the end of cell marker
from the information that is loaded into the array and then the combobox.

You are getting the odd stuff from the table because you have omitted that
line of code.

The idea of having the items to be loaded into the combobox in a table in a
separate document was to make it easy to modify the list of items without
having to modify the code. Therefore, I would suggest that you change

i = 25 'sourcedoc.Tables(1).Rows.Count - 1

j = 1 ' sourcedoc.Tables(1).Columns.Count

back to

i = sourcedoc.Tables(1).Rows.Count - 1

j = sourcedoc.Tables(1).Columns.Count

so that if there is any variation in the size of the table in the sourcedoc,
nothing will be omitted.

This line of code

ListBox1.List() = myarray1() [[THIS IS WHERE IT ERRORS]]

should be

ListBox1.List() = myarray1

or it can be

ListBox1.List = myarray1

Instead of

Private Sub CommandButton2_Click()

Dim myarray As Variant
Dim i As Long
Set sourcedoc = Documents.Open(FileName:="c:\Users\myprofile\Documents\
LISTSOURCE.DOC")

myarray = ComboBox1.List()
For i = 0 To UBound(myarray)
MsgBox myarray(i, 0)

'-------------------

If ActiveDocument.Tables.Count >= 1 Then
With ActiveDocument.Tables(1).Cell(Row:=i, Column:=1).Range
.Delete
.InsertAfter myarray(i, 0)

End With
End If

Next i

sourcedoc.Close savechanges:=wdSaveChanges

End Sub


which I doubt really does what you want it to, (it doesn't for me) I would
use:

Private Sub CommandButton2_Click()

Dim myarray As Variant
Dim i As Long
Dim trange As Range
Set sourcedoc =
Documents.Open(FileName:="c:\Users\myprofile\Documents\LISTSOURCE.DOC")

myarray = ComboBox1.List()
With sourcedoc
If .Tables.Count = 1 Then
.Tables(1).Delete
End If
For i = 0 To UBound(myarray)
.Range.InsertAfter myarray(i) & vbCr
Next i
Set trange = .Range
trange.End = trange.End - 1
trange.ConvertToTable
.Close savechanges:=wdSaveChanges
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Hi Doug,

Each user is creating their own custom list of terms. These terms don't
remain static. For each project they work on, the terms change. Within the
project, they may add additional terms to the list they are using. Those
terms need to stay for the project they are working on. After they are
done
with that particular project, the terms are removed. The next project may
have some of the same terms or completely different ones. Some of the
users
wanted the terms that they entered (on the fly) to stay in the list for a
long as they worked on the project. Other users didn't care, because they
didn't use the form to enter their own.

I changed from a combobox to a listbox to see if that would make a
difference.
it didn't.

here's the code, slightly modified. I assume I made an error and that is
why
it's not working.
'--------------------

Sub LoadCustomTagBar()
Load UserForm

'-----------------------------------
Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range, m
As
Long, n As Long
Application.ScreenUpdating = False
Set sourcedoc = Documents.Open(FileName:="c:\users\myprofile\Documents\
LISTSOURCE.DOC")
i = 25 'sourcedoc.Tables(1).Rows.Count - 1

j = 1 ' sourcedoc.Tables(1).Columns.Count

ListBox1.ColumnCount = 1
Dim myarray1() As Variant
ReDim myarray1(i, j)

For m = 1 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m, 1).Range
myarray1(m, 1) = myitem.Text

Next m
ListBox1.List() = myarray1() [[THIS IS WHERE IT ERRORS]]

sourcedoc.Close savechanges:=wdDoNotSaveChanges
UserForm.Show
End Sub


It's reading some odd stuff from the table...something like a dot. I don't
know where that's coming from. i put a message box in to check whether it
was
reading the text and it seems to be, but this dot or period is also coming
up.


the code i used to populate the list doesn't seem to make a difference. I
manually input a list and it did the same thing.

here is the code, just in case, that i used to populate the document
table:

--------------------------------------------------
Private Sub CommandButton2_Click()

Dim myarray As Variant
Dim i As Long
Set sourcedoc = Documents.Open(FileName:="c:\Users\myprofile\Documents\
LISTSOURCE.DOC")

myarray = ComboBox1.List()
For i = 0 To UBound(myarray)
MsgBox myarray(i, 0)

'-------------------

If ActiveDocument.Tables.Count >= 1 Then
With ActiveDocument.Tables(1).Cell(Row:=i, Column:=1).Range
.Delete
.InsertAfter myarray(i, 0)

End With
End If

Next i

sourcedoc.Close savechanges:=wdSaveChanges

End Sub

---------------------------------------------------------------
this seems to be working ok.

here is the code where the user enters the text...
-----------------------------------------------------------------------
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode = vbKeyReturn Then
KeyCode = 0
ComboBox1.AddItem TextBox1.Text, 0
TextBox1.Text = ""
TextBox1.SetFocus

End If
TextBox1.Cut
'-----------------------------------------------------------------------------
---------------------------------------
Thanks for your help.

sue

'---------------------------------------------------

I do not understand why you are populating the sourcedoc form an array of
combobox data.

It sounds like it is back to front to me. I would be expecting you to be
loading the combobox with the items from the sourcedoc. At least that is
what the code that I had given you is doing.

Show us ALL of the code that you are using so that I can try and
understand
what you are doing.

Hi Doug,

[quoted text clipped - 26 lines]

susiecc

 

[susiecc via OfficeKB.com]

Hi Doug,

Thank you. Yes, of course, you are correct. I put in the end statement and
the unusual dot went away.

Although after doing this the program still errored, I did figure out what it
was that I had done wrong...something so insanely obvious that I missed it.
Once corrected, it worked like a charm.

I'll now go on to the other code you sent and check that out.

I really appreciate this Doug. Thank you.

sue

You need to insert

myitem.End = myitem.End - 1

into

For m = 1 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m, 1).Range
myarray1(m, 1) = myitem.Text

so that you have

For m = 1 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m, 1).Range
myitem.End = myitem.End - 1
myarray1(m, 1) = myitem.Text

as in the code that I gave you originally to strip the end of cell marker
from the information that is loaded into the array and then the combobox.

You are getting the odd stuff from the table because you have omitted that
line of code.

The idea of having the items to be loaded into the combobox in a table in a
separate document was to make it easy to modify the list of items without
having to modify the code. Therefore, I would suggest that you change

i = 25 'sourcedoc.Tables(1).Rows.Count - 1

j = 1 ' sourcedoc.Tables(1).Columns.Count

back to

i = sourcedoc.Tables(1).Rows.Count - 1

j = sourcedoc.Tables(1).Columns.Count

so that if there is any variation in the size of the table in the sourcedoc,
nothing will be omitted.

This line of code

ListBox1.List() = myarray1() [[THIS IS WHERE IT ERRORS]]

should be

ListBox1.List() = myarray1

or it can be

ListBox1.List = myarray1

Instead of

Private Sub CommandButton2_Click()

Dim myarray As Variant
Dim i As Long
Set sourcedoc = Documents.Open(FileName:="c:\Users\myprofile\Documents\
LISTSOURCE.DOC")

myarray = ComboBox1.List()
For i = 0 To UBound(myarray)
MsgBox myarray(i, 0)

'-------------------

If ActiveDocument.Tables.Count >= 1 Then
With ActiveDocument.Tables(1).Cell(Row:=i, Column:=1).Range
.Delete
.InsertAfter myarray(i, 0)

End With
End If

Next i

sourcedoc.Close savechanges:=wdSaveChanges

End Sub

which I doubt really does what you want it to, (it doesn't for me) I would
use:

Private Sub CommandButton2_Click()

Dim myarray As Variant
Dim i As Long
Dim trange As Range
Set sourcedoc =
Documents.Open(FileName:="c:\Users\myprofile\Documents\LISTSOURCE.DOC")

myarray = ComboBox1.List()
With sourcedoc
If .Tables.Count = 1 Then
.Tables(1).Delete
End If
For i = 0 To UBound(myarray)
.Range.InsertAfter myarray(i) & vbCr
Next i
Set trange = .Range
trange.End = trange.End - 1
trange.ConvertToTable
.Close savechanges:=wdSaveChanges
End With

Hi Doug,

[quoted text clipped - 127 lines]