Unwanted Paragraph Marks

[ccpsc]

Hi all, I am trying to populate a combobox in a userform with a Name, Company Name and Address using the code below. This shows in the combobox with paragraph marks. Using P to represent the paragraph marks it looks like this;

NamePCompany NamePAddress

Can anyone tell me how to display the info in the combobox without the paragraph marks or another way of doing it?




With frmDetails.cboClient
.AddItem "Name" & Chr(13) & "Company Name" & Chr(13) & "Address"
End With

[MacroShadow]

Chr(13) = vbCrLf (return) so if you don't want it just remove the & Chr(13).
If all you want is a space in between use this:
With frmDetails.cboClient
.AddItem "Name" & " Company Name" & " Address"
End With

[gmaxey]

I would use a multi-column combobox. There is an example here:

http://gregmaxey.mvps.org/word_tip_pages/populate_userform_listbox_or_combobox.html



Hi all, I am trying to populate a combobox in a userform with a Name, Company Name and Address using the code below. This shows in the combobox with paragraph marks. Using P to represent the paragraph marks it looks like this;

NamePCompany NamePAddress

Can anyone tell me how to display the info in the combobox without the paragraph marks or another way of doing it?




With frmDetails.cboClient
.AddItem "Name" & Chr(13) & "Company Name" & Chr(13) & "Address"
End With

[ccpsc]

Thanks for the replies.

Macro , I need it to display in my document as;

Name
Company Name
Address

That's why I have the carriage returns. The line of code in my OP does this but it displays the paragraph marks as described in the OP. It works but doesn't look good.


gmaxey, I have looked at that page before and it is beyond my abilities to adapt it to work for me. I am hoping it is possible to hide the paragaraph marks

[IanFScott]

A combobox (or a listbox) cannot display multiple lines. Multiple columns yes but not multiple lines. The drop down list would be impossible to use.
You have two options:
1. Change the Chr(13) to spaces so that the paragraph marks go.
2. Give the box more columns and put the name in the first column, company name in the second column, address in the third etc.

[ccpsc]

Hi Ian,

1. It is displaying on one line in the combobox list but it also displays the the carriage return symbol.
2. I don't know how to do that, just hoping there is a way to hide the symbols so they aren't seen in the list but will still do their job.

[IanFScott]

I think I can now guess what you want. When an item is selected you want it to be put in the document with carriage returns.
The only easy answer I can think of is to use semi-colons ";" instead of the Chr(13) for the combo box and then use the Replace function before inserting the text.
strInsert = Repalce(cmbBox.List(cmbBox.Listindex,0),";",Chr(13))

[ccpsc]

I'm not sure how to use that. I have a form with three text boxes, three labels, cancel and ok buttons.

Below is the code I have so far in two modules. HeadingsDetails opens the form and loads the combobox. OK button inserts the selection in the document.



Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub cmdOk_Click()
With ActiveDocument
.Bookmarks("bmDate").Range _
.InsertBefore txtDate
.Bookmarks("bmJob").Range _
.InsertBefore txtJob
.Bookmarks("bmClient").Range _
.InsertBefore cboClient
End With
Unload Me
End Sub



Sub HeadingDetails()
frmDetails.Show vbModeless
With frmDetails.cboClient
.AddItem "John Smith" & Chr(13) & "ABC Constructions" & Chr(13) & "PO Box 1111" & Chr(13) & "Some Town Qld 4000"
.AddItem "Paul Brown" & Chr(13) & "DEF Constructions" & Chr(13) & "PO Box 2222" & Chr(13) & "Another Town QLD 4111"
.AddItem "James White" & Chr(13) & "GHI Constructions" & Chr(13) & "PO Box 3333" & Chr(13) & "City QLD 4222"
End With
End Sub

[ccpsc]

I got it working by adapting the code from the link provided by gmaxey. As shown I used a seperate document with a table to record my client list. Modified the code to get it to work and display how I need it to. Added some code to open my form and positon it on Word.

Here it is it might help someone in the future.



Sub UserForm_Initialize()
Dim arrData() As String
Dim sourcedoc As Document
Dim i As Integer
Dim j As Integer
Dim myitem As Range
Dim m As Long
Dim n As Long
' Open form
frmDetails.Show vbModeless
' Position of form on Excel App
With frmDetails
.Top = Application.Top
.Left = Application.Left
.Top = Application.Top + 280
.Left = Application.Left + 125
End With
' Set focus and highlights Heading
frmDetails.txtDate.SetFocus
With frmDetails.txtDate
.SelStart = 0
.SelLength = Len(.Text)
End With
Application.ScreenUpdating = False
' Modify the following line to point to your list member file and open the document
Set sourcedoc = Documents.Open(FileName:="C:\insert full address\Client Details.docx", Visible:=False)
' Get the number of list members (i.e., table rows - 1 if header row is used)
i = sourcedoc.Tables(1).Rows.Count - 1
' Get the number of list member attritbutes (i.e., table columns)
j = sourcedoc.Tables(1).Columns.Count
' Set the number of columns in the Listbox
frmDetails.cboClient.ColumnCount = j
' Load list members into an array
ReDim arrData(i - 1, j - 1)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
myitem.End = myitem.End - 1
arrData(m, n) = myitem.Text
Next m
Next n
' Use the .List property to populate the listbox with the array data
frmDetails.cboClient.List = arrData
' Close the source file
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
lbl_Exit:
Exit Sub
End Sub



Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub CmdOk_Click()
Dim i As Integer
Dim Client As String
Dim oRng As Word.Range
Client = ""
For i = 1 To frmDetails.cboClient.ColumnCount
'Set the .BoundColumn property. Note .BoundColumn indexed starting at 1.
frmDetails.cboClient.BoundColumn = i
'Use .Value property to get data from listbox bound column.
Select Case True
'Build the address display
Case i = frmDetails.cboClient.ColumnCount - 1
Client = Client & frmDetails.cboClient.Value & vbCr
Case i = frmDetails.cboClient.ColumnCount
Client = Client & frmDetails.cboClient.Value & vbCr
Case Else
Client = Client & frmDetails.cboClient.Value & vbCr
End Select
Next i
' Inserts text into document
ActiveDocument.Bookmarks("bmDate").Range.InsertBefore txtDate
ActiveDocument.Bookmarks("bmJob").Range.InsertBefore txtJob
ActiveDocument.Bookmarks("bmClient").Range.InsertBefore Client
Me.Hide
lbl_Exit:
Exit Sub
End Sub

[Frosty]

As an FYI, the VBA tags will make your posted code prettier than the CODE tags.