asked on

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?

HainKurt

put a breakpoint to see which line you get the error...

Muhammad Khan

in addbutton_click()... you have to qualify ListBox1 and ListBox2 with UserForm1 i.e.

If UserForm1.Listbox1.ListIndex = -1 Then Exit Sub

Dana Seaman

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

scurvylion

ASKER

@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

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

scurvylion

ASKER

@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

HainKurt

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

HainKurt

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