| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
command button that copies and pastes from one list box to another
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
command button that copies and pastes from one list box to another
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 "gbpg" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
command button that copies and pastes from one list box to ano
"JLatham" wrote: 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 "gbpg" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
command button that copies and pastes from one list box to ano
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 "JLatham" wrote: 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 "gbpg" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
command button that copies and pastes from one list box to ano
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. "gbpg" wrote: 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 "JLatham" wrote: 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 "gbpg" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
command button that copies and pastes from one list box to ano
I don't really need the cbDupicates.I will take this out. It doesn't work I
will send the error message. "JLatham" wrote: 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. "gbpg" wrote: 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 "JLatham" wrote: 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 "gbpg" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
command button that copies and pastes from one list box to ano
I get compile error variable not defined for the i
"JLatham" wrote: 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. "gbpg" wrote: 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 "JLatham" wrote: 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 "gbpg" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
command button that copies and pastes from one list box to ano
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. "gbpg" wrote: I get compile error variable not defined for the i "JLatham" wrote: 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. "gbpg" wrote: 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 "JLatham" wrote: 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 "gbpg" wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
command button that copies and pastes from one list box to ano
Thanks a lot. Worked like a charm!
"JLatham" wrote: 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. "gbpg" wrote: I get compile error variable not defined for the i "JLatham" wrote: 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. "gbpg" wrote: 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 "JLatham" wrote: 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 "gbpg" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| clicking on a button copies the choice *(VBA) | Excel Discussion (Misc queries) | |||
| Some formulas don't track copies, pastes, fill right, fill down | Excel Discussion (Misc queries) | |||
| Where is a list of Command Button uses? | Excel Discussion (Misc queries) | |||
| A Macro that Copies from Excel & Pastes into a webpage | Excel Discussion (Misc queries) | |||
| Copies 02/22/2005, Pastes 02/21/2001 | Excel Worksheet Functions | |||
Linear Mode
