Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Object not set (Excel 2002)

    Hi all,

    I need help with setting an object, I am trying load comment text within a range into a label on a form. I have been able to make it do what I want, but only from outside the userform module. When I tried to retrieve the text from inside within Change Event from a listbox, get Object not set, can some one please take a look and tell me where my mistake might be?

    Thanks
    Darryl.

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

    Re: Object not set (Excel 2002)

    Apparently, you don't require explicit declaration of variables. As is mentioned frequently in these forums, that is extremely dangerous.

    Your code contains one reference to a non-existing variable irow (instead of lrow). Since explicit declaration isn't required, this error isn't flagged.

    The line lcol = lcol does nothing at all, so it can be omitted.

    Consistent indentation would make it much easier to read the code.

    The line Me.Label6 = Me.Label6.Caption doesn't do anything, so it can be omitted.

    You don't change the cell oCells inside the loop.

    Your use of oCells.Cells(...) makes no sense.

    You define a variable sLabel but don't use it consistently in your code.

    Your code loops through a diagonal series of cells - is that really what you want?

    As the user clicks items in the list box, the label caption will be getting longer and longer and longer - is that really what you want?

    For what it's worth, here is a debugged version of your code:

    Private Sub lbNAMES_Change()
    Dim lRow As Long
    Dim lCol As Long
    Dim oCells As Range
    Dim i As Integer
    Dim osCell As Range
    Dim sLabel As MSForms.Label
    Set sLabel = Me.Label6
    Set osCell = Cells.Find(Me.lbNames.Value, , , , , xlNext, True)
    lRow = osCell.Row
    lCol = 5
    Do Until i = 52
    Set oCells = Sheets(1).Cells(lRow, lCol)
    If HasComment(oCells) Then
    sLabel.Caption = sLabel.Caption & _
    oCells.Comment.Text
    End If
    i = i + 1
    lRow = lRow + 1
    lCol = lCol + 1
    Loop
    End Sub

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Object not set (Excel 2002)

    Thank you Hans

Posting Permissions

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