command button that copies and pastes from one list box to another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I inserted the below code to accomplish the below. Why do I get an error for
the i ?

Private Sub CommandButton1_Click()
If ListBox2.ListIndex = -1 Then Exit Sub
If Not cbDuplicates Then
'See if item already existss
For i = 0 To ListBox2.ListCount - 1
If ListBox2.Value = ListBox3.List(i) Then
Beep
Exit Sub
End If
Next i
End If
ListBox3.AddItem ListBox2.Value

End Sub
 
You're basing i counter on ListBox2 list items, perhaps in the first If
ListBox3 doesn't have as many items in the list? Then you get an error
because you're trying to reference something that doesn't exist.

I think you probably meant:
For i = 0 to ListBox3.ListCount-1
 
JLatham said:
You're basing i counter on ListBox2 list items, perhaps in the first If
ListBox3 doesn't have as many items in the list? Then you get an error
because you're trying to reference something that doesn't exist.

I think you probably meant:
For i = 0 to ListBox3.ListCount-1
Click to expand...
 
If ListBox2.ListIndex = -1 Then Exit Sub
If Not cbDuplicates Then
'See if item already exists
For i = 0 To ListBox3.ListCount - 1
If ListBox2.Value = ListBox3.List(i) Then
Beep
Exit Sub
End If
Next i
End If
ListBox3.AddItem ListBox2.Value

End Sub

this still does not work. I get an error compile errror
 
What does the compiler error message say? And where is cbDuplicates declared
and set up? By the way: did your list box(es) come from the Controls Toolbox
or the Forms toolbox, and are they on worksheets or userforms?

Your code as it now stands?
If ListBox2.ListIndex = -1 Then Exit Sub

If Not cbDuplicates Then
'See if item already exists
For i = 0 To ListBox3.ListCount - 1
If ListBox2.Value = ListBox3.List(i) Then
Beep
Exit Sub
End If
Next i
End If
ListBox3.AddItem ListBox2.Value

Let's analyze what your code is doing and see if that doesn't ring a bell
somewhere.

if ListBox2.ListIndex = -1 Then Exit Sub
we will assume you're making it past that point and there is something in
ListBox2

First Test: is cbDuplicates False (or zero)? If it is False or 0, then
For Each item in ListBox3's list
If current ListBox2 value is one of ListBox3's items then
Beep and QUIT! at the very first match, don't finish looking at all
of them.
I interpret this to mean that you do want to add ListBox2.Value to
ListBox3's list when there's no match found
However, because the
ListBox3.AddItem ListBox2.Value is outside of the ...
If Not cbDuplicates Then

End If
....block, the ListBox3.AddItem ListBox2.Value statement is ALWAYS going to
be executed, even if ListBox2.Value is already an item in ListBox3's list.
That statement probably needs to go up ahead of the last End If statement.
 
I don't really need the cbDupicates.I will take this out. It doesn't work I
will send the error message.
 
Your code module probably has a statement at the very beginning:
Option Explicit

this requires you to declare any variable or constant you are going to use
before you use it. This is a GOOD thing, it makes sure your code is
internally consistent (guards you agains typo's if nothing else). Simply put
this statement somewhere near the beginning of your routine. The usual place
for these is right after the Sub declaration statement.

Dim i As Integer

The 'Dim' declares the variable. Now your code should run. There are other
benefits from Option Explicit and declaring your values early on - one of
those is that code runs faster, and that's important in big code pieces, or
even in small ones with lots of loops.
 
Thanks a lot. Worked like a charm!

JLatham said:
Your code module probably has a statement at the very beginning:
Option Explicit

this requires you to declare any variable or constant you are going to use
before you use it. This is a GOOD thing, it makes sure your code is
internally consistent (guards you agains typo's if nothing else). Simply put
this statement somewhere near the beginning of your routine. The usual place
for these is right after the Sub declaration statement.

Dim i As Integer

The 'Dim' declares the variable. Now your code should run. There are other
benefits from Option Explicit and declaring your values early on - one of
those is that code runs faster, and that's important in big code pieces, or
even in small ones with lots of loops.
Click to expand...
 
Back
Top