Endless Loop when using ComboBox1.BoundColumn = 2
 

I am trying to update a cell with the 2nd value in a ComboBox. Please
see the code below. I get stuck in a long loop if I use only one line
of code for "ComboBox1.BoundColumn = 2", but I get into and endless
loop if I use two lines like you will see below.


Private Sub ComboBox1_Change()
ComboBox1.Width = 89.25
ComboBox1.BoundColumn = 2
Range("B10").Value = ComboBox1.Value ' Description
ComboBox1.BoundColumn = 5
Range("D10").Value = ComboBox1.Value ' Price
End Sub


Thank you


---
Message posted from http://www.ExcelForum.com/

Endless Loop when using ComboBox1.BoundColumn = 2
 

shrekut,

if b10 or D10 are part of the source datafor the combo box you quite
likely will get into a loop.

Try something like this:

Private Sub ComboBox1_Change()
application.enableevents = false
ComboBox1.Width = 89.25
ComboBox1.BoundColumn = 2
Range("B10").Value = ComboBox1.Value ' Description
ComboBox1.BoundColumn = 5
Range("D10").Value = ComboBox1.Value ' Price
application.enableevents = false
End Sub


---
Message posted from http://www.ExcelForum.com/

Endless Loop when using ComboBox1.BoundColumn = 2
 

Kieran,

Thanks for the suggestion but B10 and D10 are not part of the source
The ComboBox's source is on another sheet. I stilled tried you
suggestion and it did not help. In stepping through the debugger I se
that it returns to the first line after it hits
ComboBox1.BoundColumn = 2. Is it possible that the changing of th
BoundColumn property calls this sub routine Private Su
ComboBox1_Change()? If so then why could I not try to create anothe
instance of this ComboBox1 control with something like

Set cb to ComboBox1
cb.BoundColumn = 2

which I tried, by the way, and it still did not work

--
Message posted from http://www.ExcelForum.com