Userform insert listbox as numbered list

[ben.robeck]

I'm incredibly new to VBA, userforms, macros, etc... With a lot of Advil and coffee I've been able to create almost exactly what I need except for one part. This shouldn't be too difficult, but I can't find the answers I need anywhere.

I have a listbox in my userform, and I need to import that as a numbered list, each number on a new line, at a bookmark. Not only does the list need to be numbered, but it cannot alter the location of any text on the same line. I used the tab indexes in the hopes they will help prevent that.

The document is a macro enabled template, so I cannot attach it because .dotm is not a valid file extension to upload on here.

Any help would be great, thanks in advance.

[ben.robeck]

Here's what I have. It's a very simple userform, but I'm having no luck getting the list to populate the way I want. Like I said, I'm extremely new to all of this. No formal training; just digging around online, guess and check, and going with what works. If something in the current code is wrong or could be done better, I'd love the advice. Thanks again.


Private Sub CommandButton1_Click()

With ActiveDocument
.Bookmarks("Body").Range _
.InsertBefore Body
.Bookmarks("Enl").Range _
.InsertBefore Enl
.Bookmarks("First").Range _
.InsertBefore First
.Bookmarks("Last").Range _
.InsertBefore Last
.Bookmarks("Last2").Range _
.InsertBefore Last
.Bookmarks("Last4").Range _
.InsertBefore Last
.Bookmarks("MI").Range _
.InsertBefore MI
.Bookmarks("SSN").Range _
.InsertBefore SSN
.Bookmarks("Encls").Range _
.InsertBefore Encls
.Bookmarks("Rank").Range _
.InsertBefore Rank
.Bookmarks("Rank2").Range _
.InsertBefore Rank
.Bookmarks("Rank4").Range _
.InsertBefore Rank
.Bookmarks("Addendum").Range _
.InsertBefore Addendum
.Bookmarks("Amount").Range _
.InsertBefore Amount
.Bookmarks("Incentive").Range _
.InsertBefore Incentive
.Bookmarks("EnlExt").Range _
.InsertBefore EnlExt
End With

UserForm1.Hide

End Sub

Private Sub UserForm_Initialize()

With EnlExt
.AddItem "enlisted"
.AddItem "extended"
End With

With Addendum
.AddItem "SLRP"
.AddItem "Bonus"
End With

With Incentive
.AddItem "Student Loan Repayment Program (SLRP)"
.AddItem "Enlistment Bonus"
.AddItem "Reenlistment Bonus"
End With

With ListBox1
EncList.List = Array("ETP Memos", "SLRP Addendum", "Bonus Addendum", "DD 4", "DA 4836", "DD 1966", "NSLDS Sheets", "DD 2475s", "RPAM", "Orders")
End With

End Sub

[SamT]

Save it as a regular macro enabled doc and upload that.

SamT, Excel specialist :dunno

[gmayor]

What you need is a process to populate bookmarks:


Public Sub FillBM(strBMName As String, strValue As String)
Dim oRng As Range
With ActiveDocument
On Error GoTo lbl_Exit
Set oRng = .Bookmarks(strBMName).Range
oRng.Text = strValue
oRng.Bookmarks.Add strBMName
End With
lbl_Exit:
Exit Sub
End Sub

You can the call that process as required from your userform e.g.

FillBM "Body", Body

This will write the variable 'Body' to the bookmark 'Body' and bookmark the added value with the same bookmark name (so it can be changed later by re-running the process). I would recommend using bookmark names such as 'bmBody' and variable names such as 'strBody' to make them easier to identify by type and thus easier to follow when you (or someone else) has to edit your code.

You can zip your template and upload the zip here or use a web facility such as dropbox (http://db.tt/tqlLaHAS) or onedrive (https://onedrive.live.com/) and post the link to that should you require further assistance.

[ben.robeck]

Here's the document saved as a macro enabled document. For some reason the userform doesn't run on opening when it's saved like this, but this will give you an idea of how I have it set up currently.

[gmayor]

It doesn't run on opening because it runs when you create a new document.
The attached should do what you want. I have removed the field for the enclosures count because it is unnecessary. You can get the count from the list.
The enclosures list wouldn't have worked as you had it, so I have fixed that also.
There's still an issue with spacing when you have empty fields such as the middle initial field, so I would suggest assembling the name in code as a string before writing it to the bookmark. That way you can easily allow for missing data.

[ben.robeck]

Awesome, thank you. Now that I'm looking at the code it makes more sense. Thanks for all your help!

[ben.robeck]

Few more quick things...
1. If there is only one enclosure, how can I change it to omit the count and say Encl instead of #Encls?
2. How do I turn the enclosures in to a numbered list if there's more than one selected?

Thanks again. This is a huge help.

[gmayor]

OK. You'll need to add the missing variables at the top of the module


Option Explicit
Private oFrm As New frmData
Private i As Long, j As Long, k As Long
Private vEncl As Variant
Private strEncl As String
Private oTable As Table

and then


strEncl = "" 'Initial value changed
j = 0
For i = 0 To .EncList.ListCount - 1
If .EncList.Selected(i) Then
j = j + 1
strEncl = strEncl & .EncList.List(i)
If i < .EncList.ListCount - 1 Then
strEncl = strEncl & Chr(44) 'create a list separated by commas
End If
End If
Next i
If j = 1 Then
strEncl = "Encl:" & vbCr & Left(strEncl, Len(strEncl) - 1) 'remove the comma
ElseIf j > 1 Then
vEncl = Split(strEncl, Chr(44)) 'Split the string at the commas
strEncl = "" 'Empty the string
For k = 0 To UBound(vEncl) - 1 'now rebuild the string with the numbering and line breaks
strEncl = strEncl & (k + 1) & Chr(32) & vEncl(k)
If k < UBound(vEncl) - 1 Then
strEncl = strEncl & vbCr
End If
Next k
strEncl = "Encls:" & vbCr & strEncl 'Add the decription text
End If

oTable.Range.Cells(1).Range.Text = strEncl

[ben.robeck]

Excellent. Again, thank you so much. This was a huge help!!