I have a listbox that will paste data on another sheet if selected. The
problem is that when I unselect/deselect what ever you want to call it.
The data stays there. Is there any way to make it stay only if the
selection stays?

Thanks,
Josh

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Josh

The way that I would do that is to write the data to the sheet every time
the listbox selection changes. Whenever the listbox changes, clear the
contents of the destination sheet and re-write the ranges based on what's
currently selected. It's a little more overhead, but it beats trying to
keep track of what data is associated with which listbox entry.

If you need more specifics, post the code your using now and an idea of what
your data is like.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Josh" wrote in message
...
I have a listbox that will paste data on another sheet if selected. The
problem is that when I unselect/deselect what ever you want to call it.
The data stays there. Is there any way to make it stay only if the
selection stays?

Thanks,
Josh

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks for the reply,
Here is the listbox I am using. I am not sure if this is the best way
yo populate the listbox. It was the first way I found to make it work.

Private Sub ListBox1_Click()

' Listbox1 array
Dim arrDocs(1 To 5)

arrDocs(1) = "Doc 1"
arrDocs(2) = "Doc 2"
arrDocs(3) = "Doc 3"
arrDocs(4) = "Doc 4"
arrDocs(5) = "Doc 5"

Dim Entrycount As Long
For Entrycount = 1 To 5
Worksheets("Sheet1").ListBox1.AddItem arrDocs(Entrycount)
Next
End Sub

Here is how I get the data to the other sheet:

Private Sub Worksheet_activate()
Count = 0
For j = 0 To Worksheets("Sheet1").ListBox1.ListCount - 1
If Worksheets("Sheet1").ListBox1.Selected(j) = True Then
Count = Count + 1
Worksheets("Sheet2").Activate
Cells(Count + 15, 3) = Worksheets("Sheet1").ListBox1.List(j)
End If
Next j
End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Josh

Private Sub ListBox1_Click()

' Listbox1 array
Dim arrDocs(1 To 5)

arrDocs(1) = "Doc 1"
arrDocs(2) = "Doc 2"
arrDocs(3) = "Doc 3"
arrDocs(4) = "Doc 4"
arrDocs(5) = "Doc 5"

Dim Entrycount As Long
For Entrycount = 1 To 5
Worksheets("Sheet1").ListBox1.AddItem arrDocs(Entrycount)
Next
End Sub


That's how I do it. I might do it like this:

Dim arrDocs as Variant
Dim Entrycount as Long

arrDocs = Array("Doc 1","Doc 2","Doc 3","Doc 4","Doc 5")

For Entrycount = LBound(arrDocs) to UBound(arrDocs)
Worksheet("Sheet1").ListBox1.AddItem arrDocs(Entrycount)
Next Entrycount

If you add more docs later, it will be less work.

Here is how I get the data to the other sheet:

Private Sub Worksheet_activate()
Count = 0
For j = 0 To Worksheets("Sheet1").ListBox1.ListCount - 1
If Worksheets("Sheet1").ListBox1.Selected(j) = True Then
Count = Count + 1

With Worksheets("Sheet2")
.Columns(3).ClearContents
.Cells(Count + 15,3).Value =
Worksheets("Sheet1").ListBox1.List(j)
End With

End If
Next j
End Sub


This will delete and rewrite everything in column C. I'm not sure why your
using the Worksheet_Activate event. Which worksheet is it?

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

I am getting a runtime error '1004'
Application-defined or object defined error.

With Worksheets("Sheet2")
.Columns(3).ClearContents.Cells(Count + 15, 3).Value =
Worksheets("Sheet1").ListBox1.List(j)
End With

Also, is there a way to populate the listbox when you open the document?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Josh

.Columns(3).ClearContents.Cells(Count + 15, 3).Value =
Worksheets("Sheet1").ListBox1.List(j)

Those are two lines. Look at my previous post.

Also, is there a way to populate the listbox when you open the document?


Put the code to populate in the Workbook_Open event.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.