Transferring listbox values in userform to document

[NIA2]

Hi everyone,

I have a text box in a userform in Word where the person types in some information and then clicks an "add" button to insert the entry into a listbox. They keep doing this for as many entries they need.

Each one of these listbox entries will end up being a bullet point in the Word document once the OK button is pressed in the form. I have a point in the file that's marked with a DOCVARIABLE field where I want them to go but I'm not sure how I'd go about transferring the listbox values to the marked point and then have them all appear as bullets one underneath each other and taking on the style from the initial point.

I know how to do it if I'm transferring something from a textbox, ie.

.Variables("Name").Value = txtName.Text

...but something like this for the list box isn't working:

.Variables("Personal_Interest").Value = lstPersonal_Interest.Text

Would someone be able to tell me what the code would be?

Thanks very much.

 

[fumei]

.Variables("Personal_Interest").Value = lstPersonal_Interest.Text

should work. No, it DOES work. I just did it.

I made a Variable named Personal_Interest, and I took the selected item from a Listbox and....made the Variable Value that text.

So....sorry, but it DOES work. I suspect that you are not asking the question fully. Try again. Here are some things to consider.

1. why are you taking entered text (from a textbox) and listing them in a Listbox? Is the user supposed to select all of them again from the listbox? Some of them?

2. if they ARE selecting multiple items, you want each item to be a separate paragraph within a DOCVARIABLE? Think about that.





faq219-2884

Gerry
My paintings and sculpture

 

[NIA2]

Thanks for the reply,

Just to clarify, I'm creating a form where the user can automate a curriculum vitae. On one of the pages I have the text box where they enter their personal interests. Since they would have more than one personal interest, I get them to add each entry from the text box into the list box. Once this page and the rest of the form is complete, the rows in the listbox, ie. the different personal interests, will land in the document and become bullet points.

The following is the code that I have that adds the values from the textbox into the listbox:

Private Sub cmdAddPersonalInterest_Click()
With lstPersonal_Interest
    .AddItem txtPersonal_Interest.Value
End With
txtPersonal_Interest.Value = ""
End Sub

I have this line in the userform code:

.Variables("Personal_Interest").Value = lstPersonal_Interest.Text

and this at a location in the document:

{DOCVARIABLE "Personal_Interest" \* MERGEFORMAT}

When I test it doesn't seem to insert the value/s from the list box.

Do you know what might be happening?

 

[NIA2]

Also, yes you're right - since they will be bullet points then I need them to be separate paragraphs within the DOCVARIABLE. Not sure how to do that though?

 

[PHV]

You may consider something like this:

Dim strInterest As String
Private Sub cmdAddPersonalInterest_Click()
With lstPersonal_Interest
    .AddItem txtPersonal_Interest.Value
End With
strInterest = strInterest & txtPersonal_Interest.Value & vbCrLf
txtPersonal_Interest.Value = ""
End Sub
...
.Variables("Personal_Interest").Value = strInterest
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NIA2]

Thanks for the code.

I tested and it inserts the listbox values as separate paragraphs within the DOCVARIABLE. Unfortunately this isn't giving the required effect, because the point at which it lands, ie. where the DOCVARIABLE is located, is styled with a bullet using Word's styles, so the first item is a bullet but the second and subsequent items are all indented and part of the same first bullet (if you know what I mean). I think this is because it's landing within the DOCVARIABLE. Is there a way to remedy this, ie. so that they all have their own lines and therefore bullet points?

 

[PHV]

You may try any combination of vbCrLf, vbCr and vbLf

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NIA2]

The first one - vbCrLf was in the code line you supplied but didn't work. I tried the second one - vbCr, but it's still landing in the DOCVARIABLE as part of the first bullet point so this didn't help. I also tried the third version vbLf and this just listed the values on the same line, ie. no line breaks.

Do you have any further suggestions?

 

[NIA2]

I've come to the conclusion that the reason it's not working is that it's landing within a DOCVARIABLE and this variable is already formatted to be a bullet point, therefore I guess the only way to go would be to format and style the paragraphs as bullets within the VBA. Is this the way to go? If so, can someone give me a hand as to how to do this?

 

[fumei]

I am slightly confused. You mention "On one of the pages I have the text box ". That sounds like a textbox on a page, NOT on a userform. Yet you have a commandbutton, which sounds like a userform.

Please describe precisely what is going on.

In this particular case, I am wondering if a bookmark may be a better thing to use than a DOCVARIABLE.

faq219-2884

Gerry
My paintings and sculpture

 

[NIA2]

I referred to a page because I'm using a multi-page userform.

Can you explain how I'd solve the problem?

 

[fumei]

A DOCVARIABLE may contain vbCrlf (or vbCr, or vbLF) and so, yes, text separated by those are paragraphs.

However, the value is NOT stored in the document as paragraphs. It is in the field. Therefore, from the document POV, they are NOT separate paragraphs. Therefore your paragraphs will retain the style structure of the paragraph the field is in. Therefore, those "paragraphs" are, as you put it, " part of the same first bullet".

There is no way around this AFAIK, using a DOCVARIABLE. The text (including the paragraph marks) are IN the field code data, NOT the document layer.

Can you resolve this? Yes. As I suggested, using a bookmark.

To make it simple:

A userform with a listbox which I will fill. The ListBox has MultiSelect = True

Private Sub UserForm_Initialize()
Dim stuff()
   stuff = Array("yadda_1", "yadda_2", "yadda_3", _
      "yadda_4", "yadda_5")
   ListBox1.List = stuff()
End Sub

User selects yadda_2 and yadda_3 and yadda_4.

A commandbutton (AddValue), like this:

Private Sub cmdAddValue_Click()
Dim MyString As String
Dim var

For var = 0 To ListBox1.ListCount - 1
   If ListBox1.Selected(var) = True Then
      MyString = MyString & ListBox1.List(var) _
         & vbCrLf
   End If
Next

Call FillABookmark("here", Left(MyString, _
   Len(MyString) - 2))

End Sub

builds a string of the selected items. Note that it adds a vbCrlf - paragraph mark - at the end of each found selected item. The last one must be removed. This is done with the instruction to Call FillABookmark; the Len(MyString) - 2 removes the paragraph mark at the end of the string.

Assuming there is a bookmark "here" in the document.....the string MyString (i.e. the selected items from the listbox separated by paragraph marks) will go into the bookmark.

If the paragraph the bookmark is in is bulleted, then each selected item (now inside the bookmark) will be separate bullets.

Here is the code for FillABookmark():

Sub FillABookmark(strBM As String, strText As String)
Dim oRange As Word.Range
   Set oRange = ActiveDocument.Bookmarks(strBM).Range
   oRange.Text = strText
   With oRange
      .Collapse Direction:=wdCollapseStart
      .MoveEnd Unit:=wdCharacter, Count:=Len(strText)
   End With
   ActiveDocument.Bookmarks.Add strBM, Range:=oRange
End Sub

Note that if you fill the bookmark so the text is inside the bookmark range, it is easy to get the value OUT.

Say the user selected yadda_2 and yadda_3 and yadda_4 (and added those to the bookmark):

MsgBox ActiveDocument.Bookmarks("here").Range.Text

would display:

yadda_2
yadda_3
yadda_4

faq219-2884

Gerry
My paintings and sculpture

 

[NIA2]

Thanks so much for that.

I've tried it out and have just altered the name of the variable MyString and changed it to interest. I put the code inside a cmdFinish_Click() procedure since this is the button that will transfer the values from the whole userform into the document. I then just put the function code exactly as you provided, above this.

I inserted a bookmark at the correct location and called it Personal_Interest, but when I tested it gave the following error:

Run-time error '5': Invalid procedure call or argument.

Do you know what this means?

Private Sub cmdFinish_Click()

With lstPersonal_Interest
Dim interest As String
Dim var

For var = 0 To lstPersonal_Interest.ListCount - 1
   If lstPersonal_Interest.Selected(var) = True Then
      interest = interest & lstPersonal_Interest.List(var) _
         & vbCrLf
   End If
Next

Call FillABookmark("Personal_Interest", Left(interest, _
   Len(interest) - 2))

End With

End Sub

Sub FillABookmark(strBM As String, strText As String)
Dim oRange As Word.Range
   Set oRange = ActiveDocument.Bookmarks(strBM).Range
   oRange.Text = strText
   With oRange
      .Collapse Direction:=wdCollapseStart
      .MoveEnd Unit:=wdCharacter, Count:=Len(strText)
   End With
   ActiveDocument.Bookmarks.Add strBM, Range:=oRange
End Sub

 

[fumei]

Put the FillABookmark procedure into a standard module.

faq219-2884

Gerry
My paintings and sculpture

 

[NIA2]

Okay - I've done this and have tested again but it's still giving me the same error and it's highlighting this call:

Call FillABookmark("Personal_Interest", Left(interest, _
Len(interest) - 2))

Does this line of code need to be moved somewhere also?

 

[NIA2]

Anyone want to give me a hand?

 

[fumei]

I do not know what else to say.

I (just now) copied and paste the code that I posted, and made a new document / userform.

Simply put...it works exactly as I posted that it would.

You may try putting your declarations before the start of your With statement - which BTW is NOT, repeat NOT in the code I posted.

I can not see why you are using the With statement anyway. It is not needed. Why did you add it? However, just for completness, I added it to the code...and.....it works exactly as I posted that it would.

In any case, the error means exactly what it says. You are trying to pass an invalid argument. Do you have a bookmark named Personal_Interest?

faq219-2884

Gerry
My paintings and sculpture

 

[NIA2]

Okay so I tried putting the declarations before the With statement but no luck. I then commented out the With statement - again no luck.

I have a Personal_Interest bookmark so I can't work it out.

I thought if I maybe showed you the code I have for the textbox and listbox on the userform, it may help?

Private Sub cmdChangePersonalInterest_Click()
    Dim var As Integer
    ReDim MyList(lstPersonal_Interest.ListCount - 1, 1)
    For var = 0 To lstPersonal_Interest.ListCount - 1
        MyList(var, 0) = lstPersonal_Interest.Column(0, var)
    Next
    MyList(mySelected, 0) = txtPersonal_Interest.Text
    lstPersonal_Interest.Clear
    lstPersonal_Interest.List = MyList()
    txtPersonal_Interest.Value = ""
End Sub
 
 
Private Sub lstPersonal_Interest_Change()
    mySelected = lstPersonal_Interest.ListIndex
    If mySelected > -1 Then
        txtPersonal_Interest.Text = lstPersonal_Interest.Column(0, mySelected)
        cmdChangePersonalInterest.Enabled = True
    Else
        cmdChangePersonalInterest.Enabled = False
    End If
    cmdDeletePersonalInterest.Enabled = cmdChangePersonalInterest.Enabled
 
End Sub

Private Sub cmdAddPersonalInterest_Click()
With lstPersonal_Interest
    .AddItem txtPersonal_Interest.Value
End With
txtPersonal_Interest.Value = ""
End Sub

Private Sub cmdDeletePersonalInterest_Click()
With lstPersonal_Interest
    .RemoveItem (.ListIndex)
End With
End Sub

The code allows the user to add, delete and change a list item.


This is the complete code I have on the cmdFinish_Click() button:

Private Sub cmdFinish_Click()

Application.ScreenUpdating = False

With ActiveDocument
        .Variables("Name").Value = txtName.Text
        .Variables("Title").Value = txtTitle.Text
        .Variables("Position").Value = txtPosition.Text
        .Variables("Summary_of_Experience").Value = txtSummary_of_Experience.Text
        .Variables("Month1").Value = cboMonth1.Value
        .Variables("Year1").Value = cboYear1.Value
        .Variables("State1").Value = cboState1.Value
        .Variables("Company1").Value = cboCompany1.Value
        .Variables("Month2").Value = cboMonth2.Value
        .Variables("Year2").Value = cboYear2.Value
        .Variables("Company2").Value = txtCompany2.Text
        .Variables("State2").Value = cboState2.Value
        .Variables("Month3").Value = cboMonth3.Value
        .Variables("Year3").Value = cboYear3.Value
        .Variables("Company3").Value = txtCompany3.Text
        .Variables("State3").Value = cboState3.Value
        .Variables("Role1").Value = txtRole1.Text
        .Variables("Job_Summary1").Value = txtJob_Summary1.Text
        .Variables("Role2").Value = txtRole2.Text
        .Variables("Job_Summary2").Value = txtJob_Summary2.Text
        .Variables("Role3").Value = txtRole3.Text
        .Variables("Job_Summary3").Value = txtJob_Summary3.Text

End With

Dim interest As String
Dim var

'With lstPersonal_Interest

For var = 0 To lstPersonal_Interest.ListCount - 1
    If lstPersonal_Interest.Selected(var) = True Then
        interest = interest & lstPersonal_Interest.List(var) _
        & vbCrLf
End If
Next

Call FillABookmark("Personal_Interest", Left(interest, _
    Len(interest) - 2))

'End With

With ActiveDocument
    .PrintPreview
    .ClosePrintPreview
End With

Unload Me

End Sub

And just to confirm I was supposed to put the following into a separate module - right?

Option Explicit

Sub FillABookmark(strBM As String, strText As String)
Dim oRange As Word.Range
    Set oRange = ActiveDocument.Bookmarks(strBM).Range
    oRange.Text = strText
        With oRange
            .Collapse Direction:=wdCollapseStart
            .MoveEnd Unit:=wdCharacter, Count:=Len(strText)
        End With
    ActiveDocument.Bookmarks.Add strBM, Range:=oRange
End Sub

By the way is the above FillABookmark code a procedure or a function - it looks/acts like a function. I could be getting confused though?

So could you take one last look at the above code to see what could be happening?

 

[NIA2]

Sorry, I forgot to add the following as part of my code:

Option Explicit
Public mySelected As Long

Private Sub UserForm_Initialize()

cmdChangePersonalInterest.Enabled = False
cmdDeletePersonalInterest.Enabled = False
cmdChangeEducation.Enabled = False
cmdDeleteEducation.Enabled = False

This may help troubleshoot?

 

[fumei]

By the way is the above FillABookmark code a procedure or a function - it looks/acts like a function. "

Huh? It looks and acts like a Function?????? Ummm, it most certainly does not...at all.

It IS a Sub, it is named as a Sub, and it acts like a Sub. Why do you think it looks and acts like a Function???

Functions are procedures that may have multiple instructions, but always return a single value.

Subs are procedures that may have multiple instructions, but do not return a value.

Anyway. I am stumped. I just created, again, a brand new document (simplified just to test this...so I do not have all your other controls on the userform). I made the bookmark in the document "Personal_Interest"; I made the Listbox on the userform lstPersonal_Interest. I even made the commandbutton the same name "Finish".

So things match up to your code...and it works exactly as it is supposed to. I copied right from your post. And it works.

Private Sub cmdFinish_Click()

Dim interest As String
Dim var

For var = 0 To lstPersonal_Interest.ListCount - 1
    If lstPersonal_Interest.Selected(var) = True Then
        interest = interest & lstPersonal_Interest.List(var) _
        & vbCrLf
   End If
Next

Call FillABookmark("Personal_Interest", Left(interest, _
    Len(interest) - 2))

End Sub

This does NOT get any errors.

Note that I removed the Variables stuff.

It is standard debugging to try and isolate actions into their own procedures. I would suggest you do this:

Private Sub cmdFinish_Click()

Application.ScreenUpdating = False

' update document variables using 
' its own procedure
Call UpDateVariables

For var = 0 To lstPersonal_Interest.ListCount - 1
    If lstPersonal_Interest.Selected(var) = True Then
        interest = interest & _
            lstPersonal_Interest.List(var) _
            & vbCrLf
     End If
Next

Call FillABookmark("Personal_Interest", Left(interest, _
    Len(interest) - 2))

Again, I think I am stumped as I do NOT get an error, and I can not see anything - so far - that would cause your error, except if you not have a Personal_Interest bookmark. EXCEPT, that would not get the error you say you are getting. It would get a 5941 error, the requested member does not exist.

BTW: I have found an interesting OTHER bug though. Repeated use removes the bullets! However, i fixed that by using an explicit Style (MyBullet2). The adjusted code is:

Sub FillABookmark(strBM As String, strText As String)
Dim oRange As Word.Range
Dim StyleUsed As String
   Set oRange = ActiveDocument.Bookmarks(strBM).Range
   StyleUsed = oRange.Style
   oRange.Text = strText
   With oRange
      .Collapse Direction:=wdCollapseStart
      .MoveEnd Unit:=wdCharacter, Count:=Len(strText)
   End With
   ActiveDocument.Bookmarks.Add strBM, Range:=oRange
   oRange.Style = StyleUsed
End Sub

Thanks for that, although I realize you had nothing to do with it really. I use FillABookmark often, but I have never used it on bullets before, just text in paragraphs. Now I will keep the StyleUsed for all usage of it. It makes sense.


Just to confirm, you are getting an error HERE, right?:

[highlight]Call FillABookmark("Personal_Interest", Left(interest, _
Len(interest) - 2))[/highlight]

I wish I could attach a file, but I can not.

faq219-2884

Gerry
My paintings and sculpture