Macro that generates a prompt box(?) that once selected inserts da

[MIrving]

I would be sincerely grateful for any assistance with the following. I have
limited macro recording experience and no experience with Visual Basic, but
would like to know if (with my lack of experience) the following is something
I could achieve. I am extremely keen to try and learn.

I would like to set up a macro that runs when a toolbar button is clicked
that achieves the following:
1. A prompt box (?) comes up in the style of a form.
2. The form has a pull-down list of four cities.
3. Depending on which city is selected, a list of companies appears
underneath.
4. Next to each company is a button or box that can be selected.
5. Once the user selects the companies they want, the user clicks on a
button at the end of the prompt box that says 'Insert'.
6. The selected companies are then inserted into a table in the Word
document.

I know it is a big ask, but if anyone would be kind enough to step me
through how I could go about this, I would be extremely grateful.

 

[Doug Robbins - Word MVP]

You have a little bit of learning to do.

See the article "How to create a Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

and then the 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

 

[MIrving]

Thank you Doug. I have printed both out and will work through them.

 

[MIrving]

Hi Doug - I have worked my way through
http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm
and the steps all made a lot of sense (thanks).

When I tested it, however, I got the following prompt error: "Compile error:
Method or data member not found" and when I cick 'ok' Private Sub
CommandButton1_Click() is highlighted in yellow and the cursor is at the word
'Range' .Bookmarks("Text1").Range_

What have I done wrong?

 

[Jay Freedman]

You've fallen into a trap that gets about a dozen posters a year.
There are two lines in that macro that end with an underscore, which
is a "line continuation character" to tell VBA that the next line is
part of the same statement. But the underscore MUST be preceded by a
space, which you have left out.

See http://www.word.mvps.org/FAQs/MacrosVBA/_AtEndOfLine.htm.

If you had simply copied and pasted the macro, you wouldn't have had
that problem, but the space is small in that font and easy to miss
when you're retyping the code.

 

[MIrving]

Thank you Jay. I really appreciate your help. I've corrected it and it is
working perfectly.

 

[MIrving]

I am currently working my way through Greg Maxey's
http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm.

I have a question about the example he gives for 'External Data Sources'
(which is macro StartD in
http://gregmaxey.mvps.org/Load UserForm ListBox.zip).

How do I vary the code so that the data displayed in the document (client
address) appears on one line, with a tab of 3cm between each?

Thank you for your help.

 

[MIrving]

Sorry about all the questions, but am slowly working through the links and
getting familiar with each step (Thank you everyone for your assistance )

I have a question about creating a multi-select ListBox.
What do I need to do the following, to make it a multi-select List Box?

Option Explicit
Private Sub CommandButton1_Click()
Dim i As Integer
Dim Client As String
Dim oRng As Word.Range
Client = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
If i < ListBox1.ColumnCount Then
Client = Client & ListBox1.Value & vbTab
Else
Client = Client & ListBox1.Value & vbCr
End If
Next i
Set oRng = ActiveDocument.Bookmarks("Client").Range
oRng.Text = Client
ActiveDocument.Bookmarks.Add "Client", oRng
Me.Hide
End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub UserForm_Initialize()
Dim myArray() As Variant
Dim sourcedoc As Document
Dim i As Integer
Dim j As Integer
Dim myitem As Range
Dim m As Long
Dim n As Long
Application.ScreenUpdating = False
'Modify the following line to point to your list member file and open the
document
Set sourcedoc = Documents.Open(FileName:="C:\sourceWord.doc")
'Get the number of list members (i.e., table rows - 1 if header row is used)
i = sourcedoc.Tables(1).Rows.Count - 1
'Get the number of list member attritbutes (i.e., table columns)
j = sourcedoc.Tables(1).Columns.Count
'Set the number of columns in the Listbox
ListBox1.ColumnCount = j
'Load list members into an array
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
'Populate the ListBox using the array
ListBox1.List() = myArray
'Close the source file
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub

 

[MIrving]

When I try to run the following macro I get an error message "Compile error:
Sub or function not defined". When I click ok, Private Sub ListBox1_Change()
is shaded in yellow and the word 'Split' in the following is highlighted
myArray = Split(ListBox1.List(ListBox1.ListIndex, 1), Chr(13)).

What am I doing wrong? Thanks very much for your assistance.


Option Explicit
Private Sub UserForm_Initialize()
Dim myArray() As Variant
Dim sourcedoc As Document
Dim i As Integer
Dim j As Integer
Dim myitem As Range
Dim m As Long
Dim n As Long
Application.ScreenUpdating = False
Set sourcedoc = Documents.Open(FileName:="C:sourceWord.doc")
i = sourcedoc.Tables(1).Rows.Count - 1
j = sourcedoc.Tables(1).Columns.Count
ListBox1.ColumnCount = j
'Hide columns 2 and 3
ListBox1.ColumnWidths = "75;0;0"
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
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub
Private Sub CommandButton1_Click()
If ListBox1.ListIndex > -1 And ListBox2.ListIndex > -1 And
ListBox3.ListIndex > -1 Then
If MsgBox("You selected the " & ListBox1.Text & " " _
& ListBox2.Text & " model " & ListBox3 _
& " . Is this correct?", vbQuestion + vbYesNo, "Selection") = vbYes
Then
MsgBox "Proceed to checkout"
Me.Hide
End If
Else
MsgBox "Please select a manufacturer, style and model."
End If
End Sub
Private Sub ListBox1_Change()
Dim myArray As Variant
myArray = Split(ListBox1.List(ListBox1.ListIndex, 1), Chr(13))
ListBox2.List = myArray
ListBox3.Clear
End Sub
Private Sub ListBox2_Change()
Dim myArray1 As Variant
Dim myArray2 As Variant
myArray1 = Split(ListBox1.List(ListBox1.ListIndex, 2), Chr(13))
myArray2 = Split(myArray1(ListBox2.ListIndex), "|")
ListBox3.List = myArray2
End Sub

 

[Doug Robbins - Word MVP]

What version of Word are you using?

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

 

[Doug Robbins - Word MVP]

Set the MultiSelect property of the ListBox to 1 - fmMultiSelectMulti or via
code, use the command

ListBox1.MultiSelect=fmMultiSelectMulti

Probably in the UserForm Initialize event.

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

 

[MIrving]

I'm using Word 97.

What version of Word are you using?

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

 

[MIrving]

Thanks. I changed that to 1 - fmMultiSelectMulti .

When I run the macro, it allows me to select more than one which is perfect,
but the problem I'm now having is that when I click 'ok' the selected item(s)
don't show up in the document (the bookmarked area is blank)? Do I need to
change something else? Thanks.

 

[Doug Robbins - Word MVP]

The Split function was not introduced until Word XP.

You will find and alternate method that does not rely on the use of the
Split function at:

http://groups.google.com/group/micr...6/ac1f830e7c3ed2f1?lnk=st&q=#ac1f830e7c3ed2f1

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

 

[Doug Robbins - Word MVP]

See the article "How to find out which Items are selected in a Multi-Select
ListBox" at:

http://www.word.mvps.org/FAQs/Userforms/GetMultiSelectValues.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

 

[MIrving]

Thank you Doug. Sorry, but if I could just ask to be sure - does this get
added to the command button and if so where do I insert it? Thank you very
much for assistance.

Private Sub CommandButton1_Click()
Dim i As Integer
Dim Client As String
Dim oRng As Word.Range
Client = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
If i < ListBox1.ColumnCount Then
Client = Client & ListBox1.Value & vbCr & vbTab
Else
Client = Client & ListBox1.Value & vbCr
End If
Next i
Set oRng = ActiveDocument.Bookmarks("Rates").Range
oRng.Text = Client
ActiveDocument.Bookmarks.Add "Rates", oRng
Me.Hide
End Sub

 

[Doug Robbins - Word MVP]

Yes, it does need to be incorporated into the command button click event,
but just how depends on what you are wanting to do with the information.

Probably, the follow part of your code needs to be incorporated into the
code from that site in place of the MsgBox ListBox1.List(i)
(You will need to change the i in one bit of the code to say j to
differentiate between them)

For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
If i < ListBox1.ColumnCount Then
Client = Client & ListBox1.Value & vbCr & vbTab
Else
Client = Client & ListBox1.Value & vbCr
End If
Next i

I am not sure though just what you want to end up with in the document and
this part of your code will need to be modified so that the data from the
second and any subsequent selected item in the list box does not overwrite
the data that is already in the bookmark [Rates]:

Set oRng = ActiveDocument.Bookmarks("Rates").Range
oRng.Text = Client
ActiveDocument.Bookmarks.Add "Rates", oRng

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

 

[MIrving]

Thank you for providing the link. I'm still not 100% sure - do I need to
modify the following commandbutton script to include the additional lines
somewhere that find out which Items are selected in a Multi-Select ListBox?
What I'd like to achieve in a Word97 template is that a userform is displayed
with a listbox of choices (that are sourced from a table) and a user can
select one or more choices and on clicking 'ok' on the commandbutton, the
choices are inserted into the Word document? Thank you for your kind
assistance.

Private Sub CommandButton1_Click()
Dim i As Integer
Dim Client As String
Dim oRng As Word.Range
Client = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
If i < ListBox1.ColumnCount Then
Client = Client & ListBox1.Value & vbCr & vbTab
Else
Client = Client & ListBox1.Value & vbCr
End If
Next i
Set oRng = ActiveDocument.Bookmarks("Client").Range
oRng.Text = Client
ActiveDocument.Bookmarks.Add "Client", oRng
Me.Hide
End Sub

 

[Doug Robbins - Word MVP]

Take a look at my previous post. You need to work out exactly what it is
that you want to do.

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

 

[MIrving]

Thanks. I had another try at it but am stil getting an error when I run it.
The code is at the end of this post.

What I want to end up with is:
1. Userform with a listbox.
2. Listbox has client names with addresses, that is sourced from another
Word doc.
3. User can select one or more clients and then click ok.
4. Selected clients (one or more) are inserted into the bookmark 'client'
(one client per line).

I have worked out steps 1, 2 and 3 (thanks to a previous reply, with step 3,
I changed the listbox to multi-select). My remaining problem is with step 4
and how to get all of the selected clients to appear in separate lines at the
'client' bookmark.

My sincere thanks for your help.

Private Sub CommandButton1_Click()
Dim i As Long
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
If i < ListBox1.ColumnCount Then
Client = Client & ListBox1.Value & vbTab
Else
Client = Client & ListBox1.Value & vbCr
End If
Next i
Set oRng = ActiveDocument.Bookmarks("Client").Range
oRng.Text = Client
ActiveDocument.Bookmarks.Add "Client", oRng
Me.Hide
End Sub