select cell after ListBox selection

Question

Private Sub ListBox1_Click()

ActiveCell.Value = ListBox1.Value

ActiveCell.Offset(1, 0).Select

End Sub

click item 1, works

click item 2, works

click item 1, works

click item 1, not work

I guess because it is related to the ListBox index/value not changing???  Can I modify this where I can select the same item in succession?

thank you

Answer 1

Your comment. "I guess because it is related to the ListBox index/value not changing???  "

Yes! That is correct.

I am assuming that you have an ActiveX control and if so then you could use the LostFocus event and then instead of selecting the required cell and then selecting the value in the ListBox you could select the value in the ListBox and then select the cell where you want the value. Note that if you select the required cell for the value first then you will need to re-select the same cell to trigger the lost focus event and the lost focus event will populate the cell that you select after selecting the ListBox value.

Private Sub ListBox1_LostFocus()

    ActiveCell.Value = ListBox1.Value

    ActiveCell.Offset(1, 0).Select

End Sub

Answer 2

hmm...ok.  not what i'm used to being able to do in Access, but it will have to do until Excel comes around.