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

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

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" wrote in message
oups.com...
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