UserForm ListBox Values to DocVariable

[AndreaM]

Good day

I have a multi-column ListBox on a Userform which is populated from multiple TextBoxes on CommandClick. This can generate a ListBox with multiple values and columns which I would like all items in the ListBox to populate a DocVariable in a Word doc. I also do not want to select the item in the ListBox to populate the DocVariable as I would like all items in the ListBox to fill-in a table on the Word doc.

Your help would be greatly appreciated.

[gmaxey]

DocVariables are values "A", "Joe" "Blue" etc. not tables. Can you attach your document with code and an example of what you are trying to do?

[AndreaM]

Thanks for the quick reply Grey, it will be very difficult to upload the doc as I have tons of code & userforms. It is just the one that I cannot get working. I have a table in Word with DocVariables in the table. I then have a UserForm with a ListBox which populates when I click on the + button. I can also select and item in the ListBox & remove it by clicking on the - button 18842.
In Word I have a table with the DocVariables which I want populated with all the items in the ListBox without having to select each item. 18843

[AndreaM]

Hi, I have removed the stuff not relevant to make it easier to see how the doc work or at least how I am trying to get it to work. On the Office365 Form you can add and remove items to the ListBox, but I would like it to populate the DocVariable in the table of the Word doc with all the items in the list and then pull the price from the Pricing UserForm or DocVariables. I hope this helps. 18846

[gmayor]

Are you hoping to create a new row in the table for each item in the list box? That being the case, you cannot use docvariables to insert the data into those rows as all the rows would have the same value. It would be better to write the values directly from the listbox to the table cells, adding a new row each time. Add the row, set a range to each cell in the row in turn, reduce the length of the range by one character to remove the cell end character from the range, then write the value to the range. repeat for each cell with data.

[AndreaM]

Are you hoping to create a new row in the table for each item in the list box? That being the case, you cannot use docvariables to insert the data into those rows as all the rows would have the same value. It would be better to write the values directly from the listbox to the table cells, adding a new row each time. Add the row, set a range to each cell in the row in turn, reduce the length of the range by one character to remove the cell end character from the range, then write the value to the range. repeat for each cell with data.

Yes, I am wanting to create a new row, are you able to help me with this as I am battling to find a solution to this?

[gmaxey]

I would make the table in the document interactive with the listbox. When the form loads, each item row in the table (if any) would populate an item row in the listbox. As you add rows to the listbox, include all relevant data (if showing or not) then simply rebuild (add or delete) rows in the table for each item in the listbox writing directly to the table cells as Graham suggests.

[AndreaM]

Thanks, but how do I do this?

[gmayor]

Your table format does not make this easy, but something like the following should work. In your example document the table is table 2 so based on that.
The five cell values are the values associated with the row from your list box. Loop through the items in the list box and run the following code for each item. A new row is created each time it is run


Dim oTable As Table
Dim oRow As Row
Dim lngCell As Long
Dim oCell As Range
Set oTable = ActiveDocument.Tables(2)
Set oRow = oTable.Rows.Add(BeforeRow:=oTable.Rows(oTable.Rows.Count - 2))
oRow.Height = oTable.Rows(oTable.Rows.Count - 4).Height
For lngCell = 1 To 5
oRow.Cells(lngCell).Width = oTable.Rows(oTable.Rows.Count - 4).Cells(lngCell).Width
oRow.Cells(lngCell).Borders = oTable.Rows(oTable.Rows.Count - 4).Cells(lngCell).Borders
Set oCell = oRow.Cells(lngCell).Range
oCell.End = oCell.End - 1
Select Case lngCell
Case Is = 1: oCell.Text = "This is the value for Cell 1"
Case Is = 2: oCell.Text = "This is the value for Cell 2"
Case Is = 3: oCell.Text = "This is the value for Cell 3"
Case Is = 4: oCell.Text = "This is the value for Cell 4"
Case Is = 5: oCell.Text = "This is the value for Cell 5"
End Select
oCell.Font.Name = "Calibri Light"
oCell.Font.Bold = True
oCell.Font.Size = 8.5
Next lngCell

[gmaxey]

The attached shows a document table interactive with a multi-column listbox.

[AndreaM]

Thank u so much. This is exactly what I needed and is working great. I am just having an issue with the multiplication as it gives an error, but I am trying to figure it out. If I put values to it, then it works, so it has something to do with the format. Thanks again & I really appreciate all the help.

.List(.ListCount - 1, 3) = .List(.ListCount - 1, 1) * .List(.ListCount - 1, 2)

[gmaxey]

That is just saying the value in column 3 of the listbox (the sub-total) is equal to the value in column 1 (the quantity) multiplied by the value in column 2 (the unit price). If either are null then yes it would error.

[AndreaM]

Thank you Greg, I have everything working as it should except when I rerun the Macro then it duplicates some of the items. How can I get it to clear all the rows and then rewrite the items from the ListBox?

[gmaxey]

The code I gave you should be deleting all but three rows in the document table (The heading row, one item row, and the last row)


For lngIndex = oTbl.Rows.Count - 1 To 3
oTbl.Rows(lngIndex).Delete
Next lngIndex

I would have to see your current document in order to troubleshoot.

[AndreaM]

Thank you. I managed to figure it out because for some reason it would skip over the oTbl.Rows(lngIndex).Delete, so I added a Step -1 which now deletes the rows always.

For lngIndex = oTbl.Rows.Count - 1 To 3 Step -1
oTbl.Rows(lngIndex).Delete
Next lngIndex