Selecting from a popup list

[christophercbrewster via OfficeKB.com]

I have a macro that requires entering a value from a list in another document.
I'd like the user to be able to simply select an item from a list, instead of
having to enter it by hand. The macro could easily grab the list from the
other document, but I can't think of how to make this into a popup selection
list. How would this be done? Thanks.

--
Christopher Brewster
Lockheed Martin, Eagan MN

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

 

[Jay Freedman]

I have a macro that requires entering a value from a list in another document.
I'd like the user to be able to simply select an item from a list, instead of
having to enter it by hand. The macro could easily grab the list from the
other document, but I can't think of how to make this into a popup selection
list. How would this be done? Thanks.

Make a userform (http://www.word.mvps.org/FAQs/Userforms/CreateAUserForm.htm)
and put a list box on it. In the Userform_Initialize procedure, place the code
needed to get the list from the other document and add its items to the list
box.

If you need more direction than that, explain how to find the list data, and
where the selected item from the list box should be put.

 

[christophercbrewster via OfficeKB.com]

Thanks for the link. I think what's described there doesn't apply to my need,
but I'll give more detail.

My macro creates a DocVariable using a string that is input. Then it creates
a DOCVARIABLE field in the text, specifying the DocVariable just created. The
string that was input matches a tag used in a Powerpoint presentation. When
the Ppt tags are revised, a list is created on a Notes page. The number of
slides will vary but is often >100.

The macro needs to grab the list from Powerpoint and show it as a simple
popup. The user would click an item. If I'm reading the description correctly
at your link, it seems to serve a different purpose. I'm thinking that the
expression.List property might be relevant, but am not sure how to make a
popup on which an item can be selected. Thanks for any further suggestions.

Make a userform (http://www.word.mvps.org/FAQs/Userforms/CreateAUserForm.htm)
and put a list box on it. In the Userform_Initialize procedure, place the code
needed to get the list from the other document and add its items to the list
box.

If you need more direction than that, explain how to find the list data, and
where the selected item from the list box should be put.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.

--
Christopher Brewster
Lockheed Martin, Eagan MN

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

 

[christophercbrewster via OfficeKB.com]

I discovered the ListBox object. Now I need to learn how it works. I'm kind
of new to the "Visual" part of Visual Basic-- that is, the input forms.

--
Christopher Brewster
Lockheed Martin, Eagan MN

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

 

[Doug Robbins - Word MVP]

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
Dim i As Long, j Long, m As Long, n As Long
Dim myitem As Range

' 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

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.


--
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

 

[Jay Freedman]

Although the particular userform shown in that article won't do what you need,
the userform is much more flexible than that.

I haven't done any PowerPoint automation, let alone running PPT from a Word
macro, but it's perfectly feasible if you know how to deal with the object
model.

My first question to you is, exactly what is the source of the list items? Is it
a NotesPage for a specific slide, such as
ActivePresentation.Slides(1).NotesPage? And what property of the NotesPage
object do you address to get the strings of the tags?

In the userform, you need a list box plus an OK button. The default names of
these controls are ListBox1 and CommandButton1 (although you're encouraged to
change them to names that reflect their usages).

In the Userform_Initialize procedure of the userform (which executes just before
the userform is displayed), place code that opens the PPT presentation and then
iterates through the strings in the appropriate NotesPage object. For each
string, call the ListBox1.AddItem method to insert the string into the list
box's list. It's also a good idea to set ListBox1.ListIndex = 0 to ensure that
there is always some value selected (the default is .ListIndex = -1, which
doesn't select any value).

In the CommandButton1_Click procedure (which executes when the user clicks the
OK button), place code that hides the userform (Me.Hide). This causes control to
return to the macro that called the userform.

To call the userform onto the screen, your macro should do something like this:

Sub Demo()
Dim myVar As Variable
Dim testVar As Variable
Dim UF As UserForm1
Const varName = "foo"

Set UF = New UserForm1
UF.Show

With ActiveDocument
' check for doc variable
For Each testVar In .Variables
If LCase(testVar.Name) = LCase(varName) Then
Set myVar = testVar
Exit For
End If
Next

If myVar Is Nothing Then ' need to add it
.Variables.Add Name:=varName, _
Value:=UF.ListBox1.Value
Else ' already exists
.Variables(varName).Value = _
UF.ListBox1.Value
End If

' add DocVariable field
.Fields.Add _
Range:=ActiveDocument.Bookmarks("XXYY").Range, _
Type:=wdFieldEmpty, _
Text:="DOCVARIABLE " & varName

' update
.Fields.Update
End With

' release memory
Set UF = Nothing
End Sub

 

[christophercbrewster via OfficeKB.com]

Thanks to Doug and Jay. I should have explained better what I already have
for this:

- A Powerpoint macro adds a tag to one slide.

- A Ppt macro creates a list of tags and slide numbers in the notes of slide
1. The object is:
ActivePresentation.Slides(1).NotesPage.Shapes.Placeholders(2).TextFrame.
TextRange
I'm going to have AddTag run this automatically, because it's fast.

- A Word macro creates a docvariable and inserts a DOCVARIABLE field using
the new docvariable. (The missing link in the automation is that the name of
the docvariable must be input by hand.)

- A Word macro looks at each docvariable, searches the Ppt pres for a
SlideTag of the same name, and returns the current slide number.

So I know how to get and use the current list. The part I'm missing the input
for inserting a docvariable: Although I can easily grab the list of SlideTags,
I don't know how to make that list into a ListBox from which the user makes a
selection.

For the field-update operation, I'll probably put the list in a scratch
document and turn it into a table for use in a faster search operation (it's
comes over as a tabbed list).

I haven't done any PowerPoint automation, let alone running PPT from a Word
macro, but it's perfectly feasible if you know how to deal with the object
model.

My first question to you is, exactly what is the source of the list items? Is it
a NotesPage for a specific slide, such as
ActivePresentation.Slides(1).NotesPage? And what property of the NotesPage
object do you address to get the strings of the tags?


In the userform, you need a list box plus an OK button. The default names of
these controls are ListBox1 and CommandButton1 (although you're encouraged to
change them to names that reflect their usages).

In the Userform_Initialize procedure of the userform (which executes just before
the userform is displayed), place code that opens the PPT presentation and then
iterates through the strings in the appropriate NotesPage object. For each
string, call the ListBox1.AddItem method to insert the string into the list
box's list. It's also a good idea to set ListBox1.ListIndex = 0 to ensure that
there is always some value selected (the default is .ListIndex = -1, which
doesn't select any value).

In the CommandButton1_Click procedure (which executes when the user clicks the
OK button), place code that hides the userform (Me.Hide). This causes control to
return to the macro that called the userform.

To call the userform onto the screen, your macro should do something like this:

Sub Demo()
Dim myVar As Variable
Dim testVar As Variable
Dim UF As UserForm1
Const varName = "foo"

Set UF = New UserForm1
UF.Show

With ActiveDocument
' check for doc variable
For Each testVar In .Variables
If LCase(testVar.Name) = LCase(varName) Then
Set myVar = testVar
Exit For
End If
Next

If myVar Is Nothing Then ' need to add it
.Variables.Add Name:=varName, _
Value:=UF.ListBox1.Value
Else ' already exists
.Variables(varName).Value = _
UF.ListBox1.Value
End If

' add DocVariable field
.Fields.Add _
Range:=ActiveDocument.Bookmarks("XXYY").Range, _
Type:=wdFieldEmpty, _
Text:="DOCVARIABLE " & varName

' update
.Fields.Update
End With

' release memory
Set UF = Nothing
End Sub

Thanks for the link. I think what's described there doesn't apply to my need,
but I'll give more detail.

[quoted text clipped - 24 lines]
--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.

 

[Jay Freedman]

The simplest way to put items into the list of a list box is to create an
array in a variable of type Variant, and then just assign the variable to
the list box's .List member. That's what Doug's example does, but he assumed
you were taking the data from a table in a Word document to build up the
array.

For your purposes, the quickest method might be to grab the TextRange object
you mentioned, use the Split function (passing the appropriate separator
character, probably vbCr) to assign its contents to an array in a Variant,
and strip out the slide numbers (I'm not sure what separates the slide
numbers from the tags...).

Dim idx As Long
Dim SlideList As Variant
SlideList = Split(myPPT.Slides(1).NotesPage.Shapes _
.Placeholders(2).TextFrame.TextRange, vbCr)

For idx = 0 To UBound(SlideList)
SlideList(idx) = Left(SlideList(idx), InStr(something)) ' extract tag
only
Next

ListBox1.List = SlideList

Thanks to Doug and Jay. I should have explained better what I already
have for this:

- A Powerpoint macro adds a tag to one slide.

- A Ppt macro creates a list of tags and slide numbers in the notes
of slide
1. The object is:

ActivePresentation.Slides(1).NotesPage.Shapes.Placeholders(2).TextFrame.
TextRange I'm going to have AddTag run this automatically, because
it's fast.

- A Word macro creates a docvariable and inserts a DOCVARIABLE field
using the new docvariable. (The missing link in the automation is
that the name of the docvariable must be input by hand.)

- A Word macro looks at each docvariable, searches the Ppt pres for a
SlideTag of the same name, and returns the current slide number.

So I know how to get and use the current list. The part I'm missing
the input for inserting a docvariable: Although I can easily grab the
list of SlideTags, I don't know how to make that list into a ListBox
from which the user makes a selection.

For the field-update operation, I'll probably put the list in a
scratch document and turn it into a table for use in a faster search
operation (it's comes over as a tabbed list).

I haven't done any PowerPoint automation, let alone running PPT from
a Word
macro, but it's perfectly feasible if you know how to deal with the
object
model.

My first question to you is, exactly what is the source of the list
items? Is it
a NotesPage for a specific slide, such as
ActivePresentation.Slides(1).NotesPage? And what property of the
NotesPage
object do you address to get the strings of the tags?


In the userform, you need a list box plus an OK button. The default
names of
these controls are ListBox1 and CommandButton1 (although you're
encouraged to
change them to names that reflect their usages).

In the Userform_Initialize procedure of the userform (which executes
just before
the userform is displayed), place code that opens the PPT
presentation and then
iterates through the strings in the appropriate NotesPage object.
For each
string, call the ListBox1.AddItem method to insert the string into
the list
box's list. It's also a good idea to set ListBox1.ListIndex = 0 to
ensure that
there is always some value selected (the default is .ListIndex = -1,
which
doesn't select any value).

In the CommandButton1_Click procedure (which executes when the user
clicks the
OK button), place code that hides the userform (Me.Hide). This
causes control to
return to the macro that called the userform.

To call the userform onto the screen, your macro should do something
like this:

Sub Demo()
Dim myVar As Variable
Dim testVar As Variable
Dim UF As UserForm1
Const varName = "foo"

Set UF = New UserForm1
UF.Show

With ActiveDocument
' check for doc variable
For Each testVar In .Variables
If LCase(testVar.Name) = LCase(varName) Then
Set myVar = testVar
Exit For
End If
Next

If myVar Is Nothing Then ' need to add it
.Variables.Add Name:=varName, _
Value:=UF.ListBox1.Value
Else ' already exists
.Variables(varName).Value = _
UF.ListBox1.Value
End If

' add DocVariable field
.Fields.Add _
Range:=ActiveDocument.Bookmarks("XXYY").Range, _
Type:=wdFieldEmpty, _
Text:="DOCVARIABLE " & varName

' update
.Fields.Update
End With

' release memory
Set UF = Nothing
End Sub

Thanks for the link. I think what's described there doesn't apply
to my need, but I'll give more detail.

[quoted text clipped - 24 lines]

If you need more direction than that, explain how to find the list
data, and where the selected item from the list box should be put.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.