Solved: Transfer selection from multicolumn listbox to bookmarks

[em.]

Hi there! Any help on this would be great! Previous help from this site has been fantastic by the way :)

I have an 8 column listbox on a userform that is populated from an Excel sheet. No worries there.

Here's my issue (no amount of searching has given me a clue):

A user would scroll through the listbox entries and select a row.
That selected row would then get transferred to the word document via an OK button on the userform.
Each column from the selected row would go into a different bookmark.I just can't get the selected listbox entry into the word document, let alone separating the columns into different bookmarks.

I hope this makes sense.
Many thanks, em.

[macropod]

Hi em,

Is the listbox in Excel, or in Word?

If in Excel, are you manipulating bookmarks in an existing document, or in a new document based on a template containing the bookmarks? Have you instantiated Word? Is the Word document already open? If its an existing document, can you guarantee its presence in a particular folder? If not, what do you want to do if its not found there?

For either option (Word or Excel), are you updating the bookmarks, or replacing them with the listbox values?

[em.]

Hi macropod,

I hope this answers your queries:
Basically, the user will be in an Excel workbook, then they will click on a button to open a new document in Word based on an envelope template and userform. The user will choose a name from the listbox on the userform and then the selected data will flow into the envelope with the data going to separate bookmark locations.

So:
The data is in an Excel spreadsheet, the listbox itself is in a Word userform.
The bookmarks are in a Word template and it will reside in a specific folder that is for templates (won't be moved). The bookmarks are empty and waiting to be filled with data - I'd like to update the bookmarks.
Word will be opened and a new document opened (based on the template) from a button in Excel.

I can get the data into the listbox, I can select it, but I don't know how to get the selected data separated (there are 8 columns) and into each bookmark (matching 8 of those). Any help would be fantastic :)

[macropod]

Hi em,

You say the listbox data are in 8 columns. In that case, assuming the useform is being driven from code attached to your document's template (as you post seems to suggest) and your boorkmars are named "Bkmrk1" through "BkMrk8", you could use code in the template like:
Private Sub CommandButton1_Click()
Dim i As Integer
' Update bookmarks with contents of columns 1 to 8
With ListBox1
For i = 1 To 8
Call UpdateBookmark("BkMrk" & i, .List(.ListIndex, i))
Next i
End With
End Sub

Sub UpdateBookmark(BmkNm As String, NewTxt As String)
Dim BmkRng As Range
If Documents.Count > 0 Then
MsgBox BmkNm & vbTab & NewTxt
If ActiveDocument.Bookmarks.Exists(BmkNm) Then
Set BmkRng = ActiveDocument.Bookmarks(BmkNm).Range
BmkRng.Text = NewTxt
ActiveDocument.Bookmarks.Add BmkNm, BmkRng
End If
End If
Set BmkRng = Nothing
End SubWith this code, the bookmarks are effectively updated, rather than simply being replaced with the listbox values.

[em.]

Thank you very much!

I did have to make one small change to the following line to get it to work:

For i = 1 To 8

I changed the 1 to a 0 because I think the listbox indexes work from 0 rather than 1.

One last question. I'm using this for an address label. I have some address details that don't use up all 8 columns. Is there a way to remove blank or empty bookmarks and corresponding paragraphs if there is no data to go into it?

For example
Attention Line
GPO Box
(empty)
(empty)
City State Postcode

Would ideally show as:
Attention Line
GPO Box
City State Postcode

I hope that makes sense. If there isn't a relatively easy way to do this, I will make do - it doesn't look that terrible with the spaces.

So many thanks for your help :rotlaugh:

[macropod]

Hi em,

Easy: put all the bookmarks on the one line, then add a line-feed or carriage return to the output for the non-empty fields. For example:

With ListBox1
For i = 0 To 5
If Trim(.List(.ListIndex, i)) <> "" Then Call UpdateBookmark("BkMrk" & i, .List(.ListIndex, i) & vbLf)
Next i
For i = 6 To 8
Call UpdateBookmark("BkMrk" & i, .List(.ListIndex, i))
Next i

[em.]

That's great! Works like a charm
Thanks for your help with this macropod :)