Advice wanted - choosing values from one array based on another

[Paul Moloney]

Currently, I have a template which stores an array of
product codes, and allows you to select these in a dialog box
and populate a document custom property with the selected
code:

productCodes = Array("SD", "PP", "TU", "RC", "AG", "AV")

[...]

UserForm1.ProductCode.List() = productCodes

[...]

.CustomDocumentProperties("ProductCode").Value = ProductCode.Value

I've also set up an array of the related product names:

productNames = Array("Service Delivery Platform", "PaymentsPlus",
"MobileTop-Up", "RC", "AG", "AV")

What I _want_ to do is to populate a custom document property
ProductName with the relevant product name, based on the product code
selected. How do I do this? Do I set up a two-dimensional array of
product names and product codes?
Or can I return the position in the productCodes array of the selected
product code (for example, SD is at position 0) and return the
variable in productNames at the same position? I'm afraid my VBA
knowledge doesn't come up to scratch here. Any help is appreciated,

P.

 

[Peter Hewett]

Hi Paul

Arguably hard coding your values in your code is not the best way to do
this. The problem with this is approach is that if the data changes
(productcode,productname) then so does your code. Doug Robbins has posted
code to load a ListBox/ComboBox Control or an array using data stored in a
table in another Word document. You might consider this as an alternative
strategy.

I'm presuming you're using either a ListBox or ComboBox control. Both of
these Controls have a ListIndex property which tells you which item has
been selected. You can then use the ProductCodes ListIndex property to
access the ProductName array something like this.

..CustomDocumentProperties("ProductName").Value = _
astrProductName(lstProductCode.ListIndex)

You need to check the value of the .ListIndex property, if it's -1 then the
user has not selected a value.

The variable astrProductName is a string array holding your product names.

HTH + Cheers - Peter

Currently, I have a template which stores an array of
product codes, and allows you to select these in a dialog box
and populate a document custom property with the selected
code:

productCodes = Array("SD", "PP", "TU", "RC", "AG", "AV")

[...]

UserForm1.ProductCode.List() = productCodes

[...]

.CustomDocumentProperties("ProductCode").Value = ProductCode.Value

I've also set up an array of the related product names:

productNames = Array("Service Delivery Platform", "PaymentsPlus",
"MobileTop-Up", "RC", "AG", "AV")

What I _want_ to do is to populate a custom document property
ProductName with the relevant product name, based on the product code
selected. How do I do this? Do I set up a two-dimensional array of
product names and product codes?
Or can I return the position in the productCodes array of the selected
product code (for example, SD is at position 0) and return the
variable in productNames at the same position? I'm afraid my VBA
knowledge doesn't come up to scratch here. Any help is appreciated,

P.

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Paul,

Here is the code to which Peter referred. In your case, you would have the
codes and the names in the table and they would both be loaded into the
listbox.:

This routine loads a listbox 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:="e:\worddocs\Clients.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

Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm2.Hide
End Sub

The Initialize statement will populate the listbox with the data from the
table and then when a client is selected in from the list and the command
button is clicked, the information for that client will be inserted into a
bookmark in the document. You may want to vary the manner in which it is
inserted to suit our exact requirements, but hopefully this will get you
started.

To make it easy for you, the code has been written so that it will deal with
any number of clients and any number of details about each client. It
assumes that the first row of the table containing the client details is a
header row.


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

Currently, I have a template which stores an array of
product codes, and allows you to select these in a dialog box
and populate a document custom property with the selected
code:

productCodes = Array("SD", "PP", "TU", "RC", "AG", "AV")

[...]

UserForm1.ProductCode.List() = productCodes

[...]

.CustomDocumentProperties("ProductCode").Value = ProductCode.Value

I've also set up an array of the related product names:

productNames = Array("Service Delivery Platform", "PaymentsPlus",
"MobileTop-Up", "RC", "AG", "AV")

What I _want_ to do is to populate a custom document property
ProductName with the relevant product name, based on the product code
selected. How do I do this? Do I set up a two-dimensional array of
product names and product codes?
Or can I return the position in the productCodes array of the selected
product code (for example, SD is at position 0) and return the
variable in productNames at the same position? I'm afraid my VBA
knowledge doesn't come up to scratch here. Any help is appreciated,

P.

 

[Paul Moloney]

Thanks both to you and Peter; that code works very well
for my purposes. However, I notice that both columns in
the table row are displayed in the listbox when I click on the
arrow button.

For example, the listbox initially contains:

Code

since this is the first item in the table. However, when I click
on the arrow button to the right of the listbox, the following drop-down
list is displayed:

Code1 Product1
Code2 Product2

etc.

If there are extra columns in the table (as mentioned below), those
are also displayed. (As I mention later, if an image is included
the row, a special character is displayed).

Is it possible to only display the _first_ column in the array in the
listbox,
but be able to subsequently use the values from all columns? Can
I choose which columns are displayed, and keep the other somehow
hidden?

I also got more ambitious and thought of storing more
information in extra columns. For example, to store
product logos into column 3 of the table, load those
into the array, and then insert the correct product logo
into the document (at a bookmarked point) according to the
product logo chosen.

A minor problem is that because the product logo is loaded
into the listbox (as mentioned above) a square character ( []),
presumably representing the image file, appears to the right of each
option in the listbox. This looks strange, but doesn't break anything.

For example:


Code1 Product1 []
Code2 Product1 []

etc.

A major problem is that I simply don't know how to insert an image
stored in an array. This was my dismal attempt:

' Column 3 in the original table stored the product logos, ProductCode is
the listbox

ProductCode.BoundColumn = 3
Dim rnge As Range
Set rnge = ActiveDocument.Bookmarks("ProductLogo").Range
rnge.InlineShapes.AddPicture ProductCode.Value, LinkToFile:=False,
SaveWithDocument:=True
End With

However, AddPicture appears only to work with a filename; how you add a
picture
to a document from another source, I don't know.

Any help appreciated, and thanks for the help already,

P.

 

[Jean-Guy Marcil]

Thanks both to you and Peter; that code works very well
for my purposes. However, I notice that both columns in
the table row are displayed in the listbox when I click on the
arrow button.

For example, the listbox initially contains:

Code

since this is the first item in the table. However, when I click
on the arrow button to the right of the listbox, the following drop-down
list is displayed:

Code1 Product1
Code2 Product2

etc.

If there are extra columns in the table (as mentioned below), those
are also displayed. (As I mention later, if an image is included
the row, a special character is displayed).

Is it possible to only display the _first_ column in the array in the
listbox,

After loading the listbox with your array, set the column widths to make
those you want visible/invisible, like:
Listbox1.ColumnWidths 75;0;0
will assign a width of 75 pixels to the first column and 0 to the second and
third, making them invisible.
You can also use:
Listbox1.ColumnWidths 1 in;0;0
to specify inches.
You may have to play with the width to get what you want, or set it to the
listbox width to use the control width.
You can use something like:
ListBox1.ColumnWidths = ListBox1.Width - 10 & ";" & 0
(I use "- 10" because I foind that if iI set the column widtht o exactly the
control width, I get an horizontal scroll bar)

but be able to subsequently use the values from all columns? Can
I choose which columns are displayed, and keep the other somehow
hidden?

I also got more ambitious and thought of storing more
information in extra columns. For example, to store
product logos into column 3 of the table, load those
into the array, and then insert the correct product logo
into the document (at a bookmarked point) according to the
product logo chosen.

You cannot store pictures in an array variables, at least I have never seen
it. What you can do though is store the logo as Autotext and then use the
autotext names in the third column (Or, use standard name for the autotext,
use a method that will guarrantee uniqueness, like "ProductNameCode_Logo"
like Product1Code1_Logo, this way you do not need the third column and just
call the autotext based on that naming scheme). Then all you have to do is
nsert an Autotext instead of inserting a picture from a file.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Paul Moloney]

You cannot store pictures in an array variables, at least I have never
seen

it. What you can do though is store the logo as Autotext and then use the
autotext names in the third column (Or, use standard name for the autotext,
use a method that will guarrantee uniqueness, like "ProductNameCode_Logo"
like Product1Code1_Logo, this way you do not need the third column and just
call the autotext based on that naming scheme). Then all you have to do is
nsert an Autotext instead of inserting a picture from a file.

Thanks for the info; do you know where
I can find more info on InsertAutotext?
The "Microsoft Word Visual Basic Help"
online help is sketchy, to put it mildly;
I can't find InsertAutotext in the index,
and the help doesnt' offer any full text search.
What good sources for MS Word VB commands
are there?

P.

 

[Jean-Guy Marcil]

Hi Paul,

Do ALT-F11 to get to the VBE,
There, do F2 to get to the object browser,
Type "autotext" in the search field;
Click on the binoculars to start the search;
Choose an entry form the search results and click on the "?" button.
You will find plenty of examples, try it out and come back if you need help
on specifics.

Meanwhile, here's Peter Hewett's Sub for inserting autotext at a bookmarked
spot in a document:

'_______________________________________
Public Sub UpdateBMText(ByVal strBMName As String, _
ByVal strBMValue As String)
Dim rngBM As Word.Range

Set rngBM = ActiveDocument.Bookmarks(strBMName).Range
rngBM.Text = strBMValue
ActiveDocument.Bookmarks.Add strBMName, rngBM
End Sub
'_______________________________________

use:

UpdateBMText "MyBookmark_Name", "Autotext_Name"

to call the sub.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Peter Hewett]

Hi All

Ooops! That's the code for inserting text in BM's. Use this for inserting
AutoText in bookmarks:

Public Sub InsertAutoText(ByVal strAutoTextName As String, _
ByVal strBookmarkName As String)
Dim tplAttached As Word.Template
Dim rngLocation As Word.Range

' Insert specified autotext at the bookmarked location
' and then recreate the bookmark
Set tplAttached = ActiveDocument.AttachedTemplate
Set rngLocation = ActiveDocument.Bookmarks(strBookmarkName).Range
Set rngLocation = tplAttached.AutoTextEntries(strAutoTextName) _
.Insert(rngLocation, True)
ActiveDocument.Bookmarks.Add strBookmarkName, rngLocation
End Sub

use:

InsertAutoText "Autotext entry name", "Bookmark name"


HTH + Cheers - Peter

 

[Jean-Guy Marcil]

Hi Peter,

<blush>

I had just seen that code in one of your replies to a post... without
thinking (actually, I thought; "Hey, this is just what this guys needs!") I
just grabbed it and pasted it in there! And then proceeded to totally forget
that you could not just use the Autotext name like that...

Sorry Paul... was too careless on that one!
And sorry Peter to have misused your name like that!

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Peter Hewett]

Hi JG

No problem! Had the code hiding in my Normal.dot (well one of my Normal.dot's
anyway). Just cleaned the bugger and it's still 450+Kb. Must get one of those
macro libray addin thingumy jigs!

Cheers - Peter