Help Needed for user form command button

[freeisgood]

Hello All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Codes below is for a command button of a user form (codes found on the web), for each execution of this command, items selected by users is separated by a comma. My goal is to have them separate by a break line. I added “char(10)” to line 6 "strItems = strItems & "," & Chr(10) & ListBox1.List(lngItem)". It works, with the exception that it start by a break line then entry selected by users. <o></o>
<o></o>
I am seeking help to make the necessary adjustment to remove the unnecessary break line (first one only). I am learning VBA and a quick explanation would be greatly appreciated.<o></o>
<o></o>
Code:<o></o>
Private Sub CommandButton1_Click()<o></o>
<o></o>
Dim lngItem As Long, strItems<o></o>
<o></o>
With ListBox1<o></o>
For lngItem = 0 To ListBox1.ListCount - 1<o></o>
If ListBox1.Selected(lngItem) Then<o></o>
strItems = strItems & "," & Chr(10) & ListBox1.List(lngItem) – 1<o></o>
<o></o>
End If<o></o>
Next lngItem<o></o>
End With<o></o>
<o></o>
ActiveCell.Value = Replace(strItems, ",", "", 1, 1)<o></o>
<o></o>
Unload Me<o></o>
<o></o>
End Sub<o></o>
<o></o>
Regards<o></o>
VL<o></o>

 

[repairman615]

Hello,

I think I understand. Maybe the addition of an IF like:

If strItems = "" Then
    srtitems = strItems & "," & ListBox1.List(lngItem) – 1
Else
    strItems = strItems & "," & Chr(10) & ListBox1.List(lngItem) – 1
End If

in place of:

    strItems = strItems & "," & Chr(10) & ListBox1.List(lngItem) – 1

-Jeff

 

[repairman615]

Also wanted to add it may be a good Idea to set srtItems to "" in the beginning.

Here is an updated version:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br><br><SPAN style="color:#00007F">Dim</SPAN> lngItem <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, strItems<br><br>strItems = ""<br><br>    <SPAN style="color:#00007F">With</SPAN> ListBox1<br>        <SPAN style="color:#00007F">For</SPAN> lngItem = 0 <SPAN style="color:#00007F">To</SPAN> ListBox1.ListCount - 1<br>            <SPAN style="color:#00007F">If</SPAN> ListBox1.Selected(lngItem) <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> strItems = "" <SPAN style="color:#00007F">Then</SPAN><br>                    strItems = strItems & "," & ListBox1.List(lngItem) – 1<br>                <SPAN style="color:#00007F">Else</SPAN><br>                    strItems = strItems & "," & Chr(10) & ListBox1.List(lngItem) – 1<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> lngItem<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br>    ActiveCell.Value = Replace(strItems, ",", "", 1, 1)<br><br>Unload Me<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[freeisgood]

Hi Repairman615,<!-- / sig -->
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Thanks for trying, but your alternative doesn’t work. I am getting an “Compile error: Syntax error”.

VL<o></o>

 

[repairman615]

Hello,

I did have a typo in the variable.

Did you catch srtItem and change to strItem?

 

[freeisgood]

Hello,

I did have a typo in the variable.

Did you catch srtItem and change to strItem?

I copied and pasted codes from post #3 which contain no typo, still same error pop up.

 

[freeisgood]

I found the mistake, there no “-1” needed for line #8 and #10. Should be:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
If strItems = "" Then<o></o>
strItems = strItems & "," & ListBox1.List(lngItem)<o></o>
Else<o></o>
strItems = strItems & "," & Chr(10) & ListBox1.List(lngItem)<o></o>
End If<o></o>
<o> </o>
Thanks for your help Repairman615!<o></o>

 

[repairman615]

Hmm,

I am at a lose for certainty, yet maybe the "-1" should go inside the parens??


I am tyring to duplicate on a userform etc. I also get a syntax error.

after changing:

strItems = strItems & "," & Chr(10) & ListBox1.List([COLOR=red]lngItem) – 1[/COLOR]

to:

strItems = strItems & "," & ListBox1.List([COLOR=red]lngItem - 1)[/COLOR]

Seemed to debug without error.

Does that also work for you?

Also if the first entry was selected, would this be attempting to access list item -1? I get an error there.

Jeff

 

[repairman615]

You are welcome!

That seemed to also take care of the selecting the first item error I ws getting.

Does the activecell return the expected results?

 

[freeisgood]

Everything work accordingly, again thanks!