Remove data with listbox

J

Josh

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!
 
D

Dick Kusleika

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.
 
J

Josh

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!
 
D

Dick Kusleika

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
Click to expand...

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
Click to expand...

With Worksheets("Sheet2")
.Columns(3).ClearContents
.Cells(Count + 15,3).Value =
Worksheets("Sheet1").ListBox1.List(j)
End With
End If
Next j
End Sub
Click to expand...

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

Josh

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!
 
D

Dick Kusleika

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

Those are two lines. Look at my previous post.
Also, is there a way to populate the listbox when you open the document?
Click to expand...

Put the code to populate in the Workbook_Open event.
 
J

Josh

.Columns(3).ClearContents.Cells(Count + 15, 3).Value =
Worksheets("Sheet1").ListBox1.List(j)
Click to expand...
Those are two lines. Look at my previous post
Click to expand...

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!
 
D

Dick Kusleika

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

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.
Click to expand...

Change this line

..Columns(3).ClearContents

to

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

Josh

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").ClearContents
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!
 
Top