Print - Userform Multipage selection

[Sagaron]

Hi

I am trying to allow users to select the pages they would like to print - I have devoped the list box with the choices and employed the code below. I have initially sort for the selection to be pasted in a cell and then I would transfer this to the print sheet array code. However, the result in the cell comes in a format which is broken up by a "box" style symbol between each selection and which the find, edit, replace etc does not work. Can someone please help. See code below.

thanks

Ron

Option Explicit
Private Sub obMulti_Click()
ListBox1.MultiSelect = fmMultiSelectMulti
End Sub



Private Sub OKButton_Click()
Dim Result As String
Dim i As Integer

If ListBox1.ListIndex = -1 Then
Result = "Nothing"
Else
Result = ""
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then _
Result = Result & ListBox1.List(i) & vbCrLf
Next i
End If

Sheets("sheet1").Range("a7") = vbCrLf & Result
Unload Me


End Sub

The userform code is:

Sub Printpagelist()
With UserForm1.ListBox1
.MultiSelect = fmMultiSelectSingle
.RowSource = ""
.AddItem "January"
.AddItem "February"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "November"
.AddItem "December"
End With
UserForm1.Show
End Sub

[Dave Peterson]

Try changing this:
Result = Result & ListBox1.List(i) & vbCrLf
to:
Result = Result & ListBox1.List(i) & vbLf
Or even:
Result = Result & ListBox1.List(i) & vbNewLine

Then if you see the little box, make sure that the cell has wraptext enabled.

If you want to replace this, you can use edit|Find and search for alt-0010 (hit
and hold the alt while typing 0010 on the numeric keypad)--or hit ctlr-j
instead.

Or you can use a worksheet formula:
=substitute(a1,char(10)," ")

==========
You can pick up that vbcrlf via a worksheet function:

=substitute(a1,char(13)&char(10)," ")

(vbcrlf is carriage return followed by line feed.)

Sagaron wrote:
>
> Hi
>
> I am trying to allow users to select the pages they would like to print
> - I have devoped the list box with the choices and employed the code
> below. I have initially sort for the selection to be pasted in a cell
> and then I would transfer this to the print sheet array code. However,
> the result in the cell comes in a format which is broken up by a "box"
> style symbol between each selection and which the find, edit, replace
> etc does not work. Can someone please help. See code below.
>
> thanks
>
> Ron
>
> Option Explicit
> Private Sub obMulti_Click()
> ListBox1.MultiSelect = fmMultiSelectMulti
> End Sub
>
> Private Sub OKButton_Click()
> Dim Result As String
> Dim i As Integer
>
> If ListBox1.ListIndex = -1 Then
> Result = "Nothing"
> Else
> Result = ""
> For i = 0 To ListBox1.ListCount - 1
> If ListBox1.Selected(i) Then _
> Result = Result & ListBox1.List(i) & vbCrLf
> Next i
> End If
>
> Sheets("sheet1").Range("a7") = vbCrLf & Result
> Unload Me
>
> End Sub
>
> --
> Sagaron
> ------------------------------------------------------------------------
> Sagaron's Profile: http://www.excelforum.com/member.php...o&userid=24643
> View this thread: http://www.excelforum.com/showthread...hreadid=382292

--

Dave Peterson

[Sagaron]

Thanks Dave , I used vbnewline and that worked fine. It also dawned on me to take out the vbcrlf function and replace with "," to seperate the sheets for the print macro. Thanks for your help! The worksheet formula was also great