Run-time error '91' where trying to transfer data between 2 listboxes

Hi guys,

I have a simple question about transferring data between 2 listboxs that are in the same userform. I keep getting an "object variable or with block variable not set" error. Since I am just learning VBA I am unsure how to handle this problem. I also am unsure how to have the default combobox entry set to the first value of the data array instead of an empty string.

The way the code works is as follows:

The user clicks a commandbutton which has the following macro assigned to it. The macro which is located in a module loads the return series into ListBox1 and shows UserForm1.

Public Sub InsertReturnSeries()
    Dim lastcol As Long, lastrow As Long, i As Long, j As Long
    Dim RSArray() As Variant, TimeArray() As Variant
    Dim wks As Worksheet
   
    Set wks = Worksheets("Return Series")
   
    ' make sure the RowSource property is empty
    UserForm1.Listbox1.RowSource = ""
   
    'determine values that are in return series
    lastcol = wks.Range("ReturnSeries").Columns.Count
    lastrow = wks.Range("ReturnSeries").Rows.Count
   
    ReDim RSArray(1 To lastcol)
    ReDim TimeArray(1 To lastrow)

    For i = 1 To lastcol
        RSArray(i) = wks.Cells(2, 2 + i)
    Next i

    ' Write the array of return series items to Listbox1

    UserForm1.Listbox1.List = RSArray

    ' make sure the RowSource property is empty
    UserForm1.ComboBox1.RowSource = ""
    UserForm1.ComboBox2.RowSource = ""

    'determine time periods that are in return series

     For j = 1 To lastrow
        TimeArray(j) = wks.Cells(2 + j, 2)
    Next j

    ' Write the array of time series periods to Combobox1 and ComboBox2

     UserForm1.ComboBox1.List = TimeArray
     UserForm1.ComboBox2.List = TimeArray
   
    UserForm1.Show
End Sub

The user then selects the items from the return series in ListBox1 and uses another commandbutton called AddButton to add these items to ListBox2.


Private Sub AddButton_Click()
    Dim i As Long
    Dim Listbox1 As Object, Listbox2 As Object
   
    If Listbox1.ListIndex = -1 Then Exit Sub
    If Not cbDuplicates Then
'       See if item already exists
        For i = 0 To Listbox2.ListCount - 1
            If Listbox1.Value = Listbox2.List(i) Then
                Beep
                Exit Sub
            End If
        Next i
    End If
    UserForm1.Listbox2.AddItem UserForm1.Listbox1.Value
End Sub

The problem is that when the AddButton is clicked I get the "object variable or with block variable not set" error. What am I missing here?

Also the comboboxes fill with the date series correctly but have an empty string in the default window. How do I get the first entry on the series to appear in the comboboxs?
scurvylionAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
put a breakpoint to see which line you get the error...
0
Muhammad KhanManager, ITCommented:
in addbutton_click()... you have to qualify ListBox1 and ListBox2 with UserForm1 i.e.

    If UserForm1.Listbox1.ListIndex = -1 Then Exit Sub
0
danaseamanCommented:
Try this code for AddButton:
To set default for combo use UserForm1.ComboBox2.ListIndex = 0

Private Sub AddButton_Click()
    Dim i As Long
   
    If UserForm1.Listbox1.ListIndex = -1 Then Exit Sub
    If Not cbDuplicates Then
'       See if item already exists
        For i = 0 To UserForm1.Listbox2.ListCount - 1
            If UserForm1.Listbox1.Value = UserForm1.Listbox2.List(i) Then
                Beep
                Exit Sub
            End If
        Next i
    End If
    UserForm1.Listbox2.AddItem UserForm1.Listbox1.Value
End Sub

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

scurvylionAuthor Commented:
@aiklamha and danaseaman

I fully qualified ListBox1 and ListBox2 as you suggested; however, the code still does not run. When I click the AddButton control to transfer items from LstBox1 to ListBox2 I now get the following error.

Run-time error '-2147352571 (80020005)':

Type mismatch.

When I go into debug mode, the last line in this code is highlighted (just before End Sub).

Private Sub AddButton_Click()
    Dim i As Long
    'Dim UserForm1ListBox1 As Object, ListBox2 As Object
   
    If UserForm1.ListBox1.ListIndex = -1 Then Exit Sub
    If Not cbDuplicates Then
'       See if item already exists
        For i = 0 To UserForm1.ListBox2.ListCount - 1
            If UserForm1.ListBox1.Value = UserForm1.ListBox2.List(i) Then
                Beep
                Exit Sub
            End If
        Next i
    End If
    UserForm1.ListBox2.AddItem UserForm1.ListBox1.Value
End Sub

Could the fact that I am placing a variant array into LitBox1 have anything to do with this error?

Also with regard to the combobox is there a property that I can change which will then use the first data entry as the default visible value in the combobox window?

Thanks
0
HainKurtSr. System AnalystCommented:
UserForm1.ListBox2.AddItem UserForm1.ListBox1.Value

for this one to work, ListBox must have its MultiSelect Property set to 1 fmMultiSelectSingle. Can you select multiple items? if it is set to multiple select, use code below instead of this one line to add all selected items...
UserForm1.ListBox2.AddItem UserForm1.ListBox1.Value
-->
Dim lItem As Long
For lItem = 0 To UserForm1.ListBox1.ListCount - 1
  If UserForm1.ListBox1.Selected(lItem) = True Then
    UserForm1.ListBox2.AddItem UserForm1.ListBox1.List(lItem)
  End If
Next

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
scurvylionAuthor Commented:
@HainKurt

Thanks a million for the great advice.

Your suggestion works perfectly - I have one question - can I use the same code to remove items from listbox2 when they are selected by the user? Both listboxes are multiple select.

Also would you know about the combobox question I mentioned?

Thanks
scurvylion
0
HainKurtSr. System AnalystCommented:
you can use similar code to remove...

ListBox
http://msdn.microsoft.com/en-us/library/aa223123(office.11).aspx
ListBox.RemoveItem
http://msdn.microsoft.com/en-us/library/aa221565(office.11).aspx

Dim lItem As Long 
For lItem = 0 To UserForm1.ListBox1.ListCount - 1 
  If UserForm1.ListBox1.Selected(lItem) = True Then 
    UserForm1.ListBox2.RemoveItem UserForm1.ListBox1.List(lItem)
  End If 
Next

Open in new window

0
HainKurtSr. System AnalystCommented:
check here for combobox properties and methods... it should be very similar to listbox (if not the same ;)

http://msdn.microsoft.com/en-us/library/aa223097(office.11).aspx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.