Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    May 2005
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Object required (2002)

    I have been trying to figure out how to populate a userform from a listbox. What I keep getting is "object required" error message on the Date1.value=data(1,1).value. I cannot figure what I have missed out here. There are many combo, textbox that needs to be populated, but in here have only showed 1 as an example.

    Private CellRange as Range

    Listbox list
    RangeInfo = Range("A2:C2", Range("A2:C2").End(xlDown)).Value
    With ListBox1
    .ColumnWidths = "55pt;90pt;40pt"
    .List = RangeInfo
    End With

    Populating the userform
    Private Sub CMDRetrieve_Click()
    Sheets("RawData").Select
    Rowcnt = 0
    For r = 0 To ListBox1.ListCount
    If ListBox1.Selected® Then
    Rowcnt = Rowcnt + 1
    If Rowcnt = 1 Then
    Set CellRange = ActiveSheet.Range(Cells(r - -2, 1), Cells(r - -2, 47))
    Else
    Set CellRange = _
    Union(CellRange, ActiveSheet.Range(Cells(r - -2, 1), Cells(r - -2, 47)))
    End If
    End If
    Next r
    If Not CellRange Is Nothing Then CellRange.Select
    data = CellRange.Value
    Date1.Value = data(1, 1).Value
    End Sub

    Appreciate any help.

    Thanks in advance

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Object required (2002)

    Data is an array, not an object. To refer to an item, simply use Data(1, 1) instead of Data(1, 1).Value.

  3. #3
    New Lounger
    Join Date
    May 2005
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Object required (2002)

    Thanks Hans!!!!! It worked!!!

  4. #4
    New Lounger
    Join Date
    May 2005
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Object required (2002)

    Is there a way to edit the 'populating the userform' macro to make sure that if the user does not pick any item from the list, there will be a pop-up telling them to do so?

    Thanks in advance.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Object required (2002)

    If the user hasn't selected any item, RowCnt will be 0 after the For r = ... Next r loop, so you could insert something like this after Next r:

    If RowCnt = 0 Then
    MsgBox "Please select at least one item!", vbExclamation
    Me.ListBox1.SetFocus
    Exit Sub
    End If

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •