Solved: Populate list box in a document using macro?

[clhare]

I have to add a list box to a table cell, then get the information that will appear in the list by via a user form. My question is.....once I get the info from the user form, how do I get them into that list box in the document? I need to set it up so that the user can select multiple items from the list.

Cheryl

[MOS MASTER]

Hi Cheryl, :yes

Little time left and I'm to drunk right now! :rofl:

Okay the code for the userform:
Option Explicit
Private Sub CommandButton1_Click()
Dim i As Integer
ThisDocument.ListBox1.Clear

For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) = True Then
ThisDocument.ListBox1.AddItem Me.ListBox1.List(i)
End If
Next i
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim vArray As Variant
vArray = Array("VBA", "VB", "ASP", ".NET", "COBOL", "LISP", "C#")
Me.ListBox1.List = vArray

End Sub


This will fill the selected items in the listbox in the document.

See attachment for a example....:whistle:

[clhare]

Okay, but what if the user has to tell me what the variables are? You've set it up to assign the variables yourself.

Cheryl

[xCav8r]

How do you want the user to give you row data?

[clhare]

I created a user form that has 10 text boxes in it. The user would then type in however many names (up to 10) they want.

Then I have to insert all the names they give me into a specific spot in the document, and also into 10 list boxes that are in the document (listbox1, listbox2, etc.). The user needs to be able to select any or all of the names in any of the list boxes.

Cheryl

[MOS MASTER]

Okay, but what if the user has to tell me what the variables are? You've set it up to assign the variables yourself.

Cheryl
Ahums..I've given you a quick working example and therefore had to provide the Array of data myself????

You didn't provide me with that information! :devil:

[MOS MASTER]

I created a user form that has 10 text boxes in it. The user would then type in however many names (up to 10) they want.

Then I have to insert all the names they give me into a specific spot in the document, and also into 10 list boxes that are in the document (listbox1, listbox2, etc.). The user needs to be able to select any or all of the names in any of the list boxes.

Cheryl
To Enable multiselect in an ActiveX listbox control you set the property: MultiSelect to 1- FmMultiSelectMulti

Enjoy! :whistle:

[clhare]

Joost,

I'm using your example file as a starting point, but I can't figure out how to assign the variables received in the user form to the list box in the document. Here's my code...maybe you can tell me what I'm doing wrong with it?

Option Explicit
Private strChildList(1 To 10) As String

Private Sub cmdOK_Click()

Dim strChildName(1 To 10) As String
Dim intCount As Integer
Dim strTemp As String
Dim strBuildNames As String
Dim intListItem As Integer
Dim varArray As Variant

' Assign values to variables
strBuildNames = ""
strTemp = ""

' Add the first name to this variable
strTemp = frmChildrensNames.txtChildName1.Text

' Assign child's name to a string for list box
intCount = 1
strChildList(intCount) = strTemp
strBuildNames = strTemp & vbCr
For intCount = 2 To 10 ' Already did first one, nine left to check
' Clear variable and assign new name to it
strTemp = ""
' Check next name to see if it contains text. If so, continue
If frmChildrensNames.Controls(CStr("txtChildName" & intCount)).Text = "" Then
' End loop
Else
strTemp = frmChildrensNames.Controls(CStr("txtChildName" & intCount)).Text
' Assign child's name to a string for list box
strChildList(intCount) = strTemp
' StrBuildNames = previous value plus strTemp & Hrt
strBuildNames = strBuildNames & strTemp & vbCr
End If
intCount = intCount + 1
Next

' Add all names entered at bookmarked location
With Application.ActiveDocument
Call FillBookmark(strBuildNames, "ChildrensNames")
End With

' For list box
ThisDocument.ListBox1.Clear
For intListItem = 0 To ThisDocument.ListBox1.ListCount - 1
If ThisDocument.ListBox1.Selected(intListItem) = True Then
ThisDocument.ListBox1.AddItem ThisDocument.ListBox1.List(intListItem)
End If
Next intListItem

' ***** I GET AN ERROR WHEN IT GETS TO THIS NEXT LINE OF CODE *****
varArray = Array(strChildlist1, strChildlist2, strChildlist3, strChildlist4, _
strChildlist5, strChildlist6, strChildlist7, strChildlist8, _
strChildlist9, strChildlist10)
ThisDocument.ListBox1.List = varArray
End Sub

[MOS MASTER]

Hi Cheryl, :yes

Your code to the listboxes is totally of because you are only using one listbox to fill itself or something and then again your using the Array technique to do it all over again???

I have no idea from the code I've sean what you are doing. (The code above the listbox stuff I've given you in another topic and is clear to me)

Could you post a sample document with some more information what should be done so I can fill in the blanks? :whistle:

[clhare]

Arrgh!!!!

I can't figure out how to zip a file. I have pkzip and even with the Wizard, it's not zipping anything!!!

:banghead:
Cheryl

[MOS MASTER]

Hi Cheryl,

Do you have XP?

Rightmouse choose | Send to | Compressed zip folder?

[clhare]

Yes, I do have XP. Thank you!! I think that worked!

Cheryl

[MOS MASTER]

Okay..look at it later..:whistle:

[MOS MASTER]

Hi Cheryl, :yes

You are using a template and therefore we can't use Me or Thisdocument to get a handle on the Listbox ActiveX controls. (Fields)

So now I've made you a separate sub to fill those listboxes feeding them a array:
Public Sub FillListBoxes(vArray As Variant)
Dim oInlineShape As Word.InlineShape
Dim oActiveX As ListBox
For Each oInlineShape In ActiveDocument.InlineShapes
If oInlineShape.Type = wdInlineShapeOLEControlObject Then
If InStr(1, oInlineShape.Field.Code, "ListBox", vbTextCompare) <> 0 Then

Set oActiveX = oInlineShape.OLEFormat.Object
With oActiveX
.List = vArray
End With
Set oActiveX = Nothing

End If
End If
Next
End Sub


And changed the code in the Form:
Private Sub cmdOK_Click()
Dim strChildName() As String
Dim intCount As Integer
Dim iArray As Integer
Dim strTemp As String
Dim strBuildNames As String

' Sets up the error trap
On Error GoTo ErrHandle
' Hide the form
frmChildrensNames.Hide
' Assign values to variables
strBuildNames = ""
intCount = 1
iArray = 0
For intCount = 1 To 10 ' Already did first one, nine left to check
' Clear variable and assign new name to it
strTemp = ""
' Check next name to see if it contains text. If so, continue
If frmChildrensNames.Controls(CStr("txtChildName" & intCount)).Text = "" Then
' End If
Else
strTemp = frmChildrensNames.Controls(CStr("txtChildName" & intCount)).Text
' Assign child's name to a string for list box
ReDim Preserve strChildName(iArray)
strChildName(iArray) = strTemp
iArray = iArray + 1

' StrBuildNames = previous value plus strTemp & Hrt
strBuildNames = strBuildNames & strTemp & vbCr
End If
Next
' Go to the top of the document
Selection.HomeKey Unit:=wdStory
Call FillBookmark(strBuildNames, "ChildrensNames")
Call FillListBoxes(strChildName)
Exit Sub
' This section handles any error if one has occurred
' by closing the file without saving it
ErrHandle:
MsgBox prompt:="An error occurred while setting up the document." & _
vbNewLine & "Please try again.", _
Title:="Document Canceled Due to Error"
' Deletes the document without saving and ends the procedure due to error
' ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
End
End Sub


I'm sure you have to make more alterations because you have other things you want it to do but for now it works as is.

I've made the changes in the template as well. (Attachment)

Enjoy! :whistle:

[clhare]

Great...now it works! But I have one question. I thought the list box would only show the names selected once you tab out of it (kind of like the dropdown formfield does), but apparently that's not the case. It shows whichever names I selected as highlighted among all of them.

Is there a different type of box that can do that? Or can a dropdown box be set to allow more than one selection?

Cheryl

[clhare]

Uh oh.....

I just saved and closed the file I created using the template and opened it up again to see if the names I selected in the list boxes were still highlighted. I found that the names inserted by macro at the bookmark location were still there, but all the list boxes were now empty!

If there is not a way to only show what the user selects in the list boxes within the document once the user tabs out of the list box, I have to think of another way to do it. Another alternative would be to:

1) Remove the list boxes from the document and put bookmarks in the cells instead
2) Create an additional user form for the table variables and put the list boxes in the user form instead

Then when the macro is run:

1) User enters all possible names into the first user form
2) Macro passes the names entered in the first user form to each list box in the second user form when that form initializes
3) User selects which names would apply for each row in the second user form's "table"
4) Macro insert the names selected in the second user form's list boxes into the bookmarked locations in the document

I tried doing this new variation myself and totally screwed it up!

Cheryl
:banghead:

[MOS MASTER]

Great...now it works! But I have one question. I thought the list box would only show the names selected once you tab out of it (kind of like the dropdown formfield does), but apparently that's not the case. It shows whichever names I selected as highlighted among all of them.

Is there a different type of box that can do that? Or can a dropdown box be set to allow more than one selection?

Cheryl
Hi Cheryl, :yes

Strange that you thought that? Controls don't have the habbit of doing anything by themselfs.....(And the Formfield only allows one selected item so that's not the same)

What you need is a macro that will strip all values from the list except those are selected.

You could use the Lostfocus event of the control to do that. Code should go in ThisDocument and for every listbox like:
Private Sub ListBox1_LostFocus()
Dim i As Integer
For i = Me.ListBox1.ListCount - 1 To 0 Step -1
If Me.ListBox1.Selected(i) = False Then
Me.ListBox1.RemoveItem (i)
End If
Next
End Sub


This will clean the box up and leave only the selected ones in there...:whistle:

[MOS MASTER]

Uh oh.....

I just saved and closed the file I created using the template and opened it up again to see if the names I selected in the list boxes were still highlighted. I found that the names inserted by macro at the bookmark location were still there, but all the list boxes were now empty!

Yeah aint that grant! :yes

Like Gerry and I told you before we really hate those ActiveX controls! :rofl:

The re-initialize on open so yepz you can't save anything in them....

So the only workarround is do have some kind of storage place for the values (properties or variables or something) and have the listboxes filled each time the document is opened in the Open event)

A good point of advice:

Before you start building on a project examine very carefully what you want.
Write a workflow of the proces you want to happen.
Make a list of tools you wish to use (Like ActiveX)
Run some test if those tools are the right one for the Job.
Thinking out a plan takes time...but later saves time. You're now rethinking your design so you could say you've lost all the previous invested time...(And that's no fun)

ActiveX are lousy things in my opion and I would never use them in a real sollution.

Good luck on the project. :whistle:

[clhare]

I apologize for all the extra work I put you through. I've never had to use a setup like this and had no idea that a list box within a document could not keep it's selected values after saving and closing and opening again.

I ended up using a user form to get all the info I need and them populating it into the text (with your help). The only problem I'm having now is getting the information in the user form's list box into the text at a bookmarked location.

Can you tell me how to do that? I tried the following, but it didn't work. Nothing was transferred to the text at the bookmarked location:


Dim strNames1 As String

' Get names from user form's list box
strNames1 = frmSupplementA.lstNames1.Text

' Add names in list box to document using bookmark
With Application.ActiveDocument
Call FillBookmark(strNames1, "Names1")
End With


I really appreciate all your help--you guys are terrific!!

Cheryl
:bow:

[MOS MASTER]

Hi Cheryl, :yes

No Problem!

This should do it:
Dim i As Integer
Dim strNames1 As String

For i = 0 To frmSupplementA.lstNames1.ListCount - 1
If frmSupplementA.lstNames1.Selected(i) = True Then
strNames1 = strNames1 & frmSupplementA.lstNames1.List(i) & vbCr
End If
Next i
Call FillBookmark(strNames1, "Names1")


Enjoy! :whistle:

[fumei]

Actually, I really LIKE activeX controls. I hate those Insert > Textbox thingies. Staright ActiveX controls from Controls toolbars work great as far as I am concerned.

BTW: I suspect that this design is far more complicated that required. However, shrug, that is the way most things go.

Cheryl, just out of curiousity, what exactly do you want to be the process?

WHY are the users building a list, to be REselected later? This seems to invlove a complication that is not needed.

You say you have propblems getting the information into a specific location.

There are a number of solutions. It all depends on what your REAL requirements are.

1. You can insert whatever you want into a bookmark. The trick is to resize the range dimensions of the bookmark dynalically, so it always retails the range iof what text is inserted into it.

2. you could have the information inserted into a formfield.

Still trying to figure out why you are doing it this way.

[MOS MASTER]

Actually, I really LIKE activeX controls. I hate those Insert > Textbox thingies. Staright ActiveX controls from Controls toolbars work great as far as I am concerned.

Hi Gerry, :yes

Ah Ok...must have misunderstood you in one of your previous posts...sorry.

For the record I do really HATE them! They don't even have a Visible property! :devil:

[clhare]

Perfect!!! Thank you so much! It works great!

[MOS MASTER]

Perfect!!! Thank you so much! It works great!
Hi Cheryl, :yes
As always it was my pleasure! :beerchug:

[fumei]

Joost, ah True, they do not have a Visible property. But they DO have a dimensional properties. It is a little tricky, but I can, and do, use them and dynamically resize the .Height and .Width to a pixel dimension...not .Visible= False I know, but, depending on your resolution....pretty darn close to .Visible = False.

They take images quite well, and can be, again, dynamically resized to fit incoming images.

For example, I have a Word document that loads images directly (using WinHTTP) from the 'Net into ActiveX Image Checkbox controls - yes, Checkboxes have .Image properties. Therefore, a user can check an image (downloaded live) and that image can be sent to a storage document, and the checkbox loaded with a another image, with the checkbox cleared.

Make them "Flat" and they essentially disappear on the screen, and resized to VERY small, they can be essentially non-existent - yet ready to do work at the sniff of some code. PLUS they have a lot of Events that can be fired.

Better specs....better code. If the specs call for the special qualities of ActiveX control..I say use 'em. Remember they CAN be converted back and forth between InlineShapes, and Shapes. Even temporarily.

It is those hybrid thingies (Insert > Textboxes) I hate - they are sort of ActiveX, but not REAL ActiveX.

[MOS MASTER]

It is those hybrid thingies (Insert > Textboxes) I hate - they are sort of ActiveX, but not REAL ActiveX.
Hi Gerry,

To be honest there not to bad if you talk about what they can do. (They are event driven and thereby good working tools)

What I truly HATE about them is the fact that they engage the Macro security question when you open the document, even if NO code is present.

If you don't press yes then your in design mode and gone is the use of ActiveX...its a shame..they could be brillian!

I know this is a security feature..Now days what isn't. (Those Hacker/Spyware-builders/Virus writers...really screwed Office up for us...:( )

I long for the days you could do almost everything without security issues. (Now days even a simple mailmerge in 2003 causes an SQL warning..Easy fix in the registry buth still...it's deamed harmfull by MS)

Those Textbox thingies I don't like either. (Upgraded Frames) so lets chake on that one...:friends:

O well....we can't have it all can we?

[fumei]

Agreed, there is a lot more we could do if there were no annoying people with too much time on their hands.

[MOS MASTER]

Agreed, there is a lot more we could do if there were no annoying people with too much time on their hands.
:beerchug: (And I hope we will be able to do that again in the near future again with some kind of new technology....but all seams to point the other way..if does annoying people keep on doing there terible stuff)

[fumei]

Just to get back to the thread....Cheryl are you fine with how this worked out?

I certainly agree with having User interaction completed on the UserForm. If you are using the UserForm to get some information and then perform some logic on it, you may as well keep going and perform as much logic as possible there.

If you have a need to store information within the document, I mean information that has persistence, there are methods for doing so.

[MOS MASTER]

I think Cheryl was happy with the sollution provided but forgot to mark the thread as such. :yes

I'm sure she's a bit busy right now developping here latest project..:rofl:

[clhare]

Sorry for the delay! This works great! Thanks for all your help!

[MOS MASTER]

Sorry for the delay! This works great! Thanks for all your help!
Hi Cheryl, :yes

No problem..you're always welcome and thanks for marking the thread solved! :whistle:

[clhare]

I have a document with a couple formfield dropdown boxes in them. How would I automatically select an option in the second dropdown if a particular option in the first one is selected?

[clhare]

Oops! I ended up in something else by mistake. I'll repost as a new topic.