Remove data with listbox
 

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!

Remove data with listbox
 

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!

Remove data with listbox
 

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!

Remove data with listbox
 

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.

Remove data with listbox
 

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!

Remove data with listbox
 

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.

Remove data with listbox
 

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

Those are two lines. Look at my previous post

It won't let me go to the next line after the "=" it highlights the
whole line red.

Instead of clearing the whole column, is it possible to clear cells
C15:C35? There is only 20 selections in the listbox.

Thanks for your help so far. I am begining to understand some of the
basics of VB.

Josh

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

Remove data with listbox
 

Josh


It won't let me go to the next line after the "=" it highlights the
whole line red.


Put your cursor right after the 's' in ClearContents and before the '.' in
..Cells and hit enter

Instead of clearing the whole column, is it possible to clear cells
C15:C35? There is only 20 selections in the listbox.


Change this line

..Columns(3).ClearContents

to

..Range("C15:C35").ClearContents

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

Remove data with listbox
 

Dick

I dont know whats wrong with me, I couldn't get the VB editor to take
it. I did end up getting it to work off some of the things you showed
me. Thanks for your help and patience in working with me on this. Here
is what I came up with, it's kind of ghetto, but it does what I want it
to.

Public Sub CommandButton1_Click()
Worksheets("Sheet2").Range("C15:C35").ClearContent s
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
Worksheets("Sheet1").Activate
End Sub

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