+ Reply to Thread
Results 1 to 3 of 3

Must Be Missing Something Obvious About ListBoxes

  1. #1
    Johnny Meredith
    Guest

    Must Be Missing Something Obvious About ListBoxes

    I have a four-column list box with the following code in the change
    event (not all columns are used in this example):
    ..
    ..
    With lstbox
    Range("Name1").Value = .Columns(1)
    Range("Name2").Value = .Columns(0)
    Range("Name3").Value = .Columns(2)
    end with
    ..
    ..

    If I comment out the last two range lines, it works. If I don't, a
    very strange thing happens. If I place a breakpoint on the first line,
    the executions breaks here as normal; but when I hit play it breaks on
    this line again, and, on the third play, throws exception #381.
    Meanwhile, I notice that the lstbox reference has become null.

    If I dim a module-level interger as a counter (adding one to it at the
    top of the event handler), it appears that the code is being called 3
    times.

    Am I using the Columns property incorrectly? Why is this code
    executing three times?

    Thanks,
    Johnny


  2. #2
    Jim Thomlinson
    Guest

    RE: Must Be Missing Something Obvious About ListBoxes

    Your events are recursive. When you assign the value to the cell a change is
    initiated and the event fires again. You need to change the application
    settings as follows (it is safest to use an error handler here)

    on error goto errorhandler
    application.enableevents = false
    ....
    With lstbox
    Range("Name1").Value = .Columns(1)
    Range("Name2").Value = .Columns(0)
    Range("Name3").Value = .Columns(2)
    end with
    ....
    ErrorHandler:
    application.enableevents = true
    End sub
    --
    HTH...

    Jim Thomlinson


    "Johnny Meredith" wrote:

    > I have a four-column list box with the following code in the change
    > event (not all columns are used in this example):
    > ..
    > ..
    > With lstbox
    > Range("Name1").Value = .Columns(1)
    > Range("Name2").Value = .Columns(0)
    > Range("Name3").Value = .Columns(2)
    > end with
    > ..
    > ..
    >
    > If I comment out the last two range lines, it works. If I don't, a
    > very strange thing happens. If I place a breakpoint on the first line,
    > the executions breaks here as normal; but when I hit play it breaks on
    > this line again, and, on the third play, throws exception #381.
    > Meanwhile, I notice that the lstbox reference has become null.
    >
    > If I dim a module-level interger as a counter (adding one to it at the
    > top of the event handler), it appears that the code is being called 3
    > times.
    >
    > Am I using the Columns property incorrectly? Why is this code
    > executing three times?
    >
    > Thanks,
    > Johnny
    >
    >


  3. #3
    Carl
    Guest

    Re: Must Be Missing Something Obvious About ListBoxes

    It seems that you are looking at the properties of the Listbox but trying to
    assign values to a range at the same time. I am not sure that flies.


    "Johnny Meredith" <[email protected]> wrote in message
    news:[email protected]...
    >I have a four-column list box with the following code in the change
    > event (not all columns are used in this example):
    > .
    > .
    > With lstbox
    > Range("Name1").Value = .Columns(1)
    > Range("Name2").Value = .Columns(0)
    > Range("Name3").Value = .Columns(2)
    > end with
    > .
    > .
    >
    > If I comment out the last two range lines, it works. If I don't, a
    > very strange thing happens. If I place a breakpoint on the first line,
    > the executions breaks here as normal; but when I hit play it breaks on
    > this line again, and, on the third play, throws exception #381.
    > Meanwhile, I notice that the lstbox reference has become null.
    >
    > If I dim a module-level interger as a counter (adding one to it at the
    > top of the event handler), it appears that the code is being called 3
    > times.
    >
    > Am I using the Columns property incorrectly? Why is this code
    > executing three times?
    >
    > Thanks,
    > Johnny
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1