[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

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?
0
scurvylion
Asked:
scurvylion
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now