PDA

View Full Version : [SOLVED] delete sign pilcrow in combobox



Lichress
04-06-2017, 05:24 PM
hi, tell, please, how to clean the sign pilcrow ( ¶ ) in combobox?

Paul_Hossler
04-06-2017, 05:44 PM
Remove the Chr(10) in




Private Sub UserForm_Initialize()
With ComboBox2
.AddItem "text" & Chr(10) & "number"
End With
End Sub

Lichress
04-06-2017, 06:05 PM
I need to keep a cell format = text 1
number 1

mdmackillop
04-07-2017, 01:38 AM
Remove the RowSource from the Combobox

Private Sub UserForm_Initialize()
Dim txtlist As Range, cel, x As String
With Sheets(2)
Set txtlist = Range(.Cells(1, 1), .Cells(10, 1))
End With

With ComboBox1
.AddItem "text" & vbTab & "number"
For Each cel In txtlist
x = Replace(cel.Value, Chr(10), vbTab)
.AddItem x
Next
End With
End Sub

snb
04-07-2017, 01:46 AM
@mdmac

Never use .additem or rowsource to populate a combobox/listbox; use .List instead.

See http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

mdmackillop
04-07-2017, 02:26 AM
Alternative

Private Sub UserForm_Initialize()
Dim txtlist As Range, cel, x As String
Dim arr
With Sheets(2)
'arr needs an extra cell to hold the Combobox Header
arr = Range(.Cells(1, 1), .Cells(10, 1)(2)).Value
End With
For a = UBound(arr) - 1 To 1 Step -1
arr(a + 1, 1) = Replace(arr(a, 1), Chr(10), vbTab)
Next
arr(1, 1) = "Text" & vbTab & "Number"
ComboBox1.List = arr
End Sub

mdmackillop
04-07-2017, 03:10 AM
@snb
Not that I didn't believe you, but I checked speed of both methods above;
10 items over 5 runs
AddItem = 2436, List = 12976
1000 items over 5 runs
AddItem = 5235, List = 17408

rlv
04-07-2017, 06:33 AM
FWIW, for me List was ~10 times faster than AddItem for 1000 items. Interestingly, for both methods it took longer to populate a listbox than a combo box.

Lichress
04-07-2017, 07:18 PM
thank you very much