[RESOLVED] ListBox dynamically select cells

[krees]

This should be an easy one.

I have a listbox which houses the address of certain cells (i.e. Sheet1 E39). I would like for the listbox to immediately take the user to the selected cell while remaining open so that said user may quickly hop over to a different cell by selecting it from the list. This should work for scrolling as well (so that if you press "Down" Excel jumps to the address of the next value.

As of now, one has to select the cell, then press enter to go to the cell.

Here's my code for reference (I've also tried Listbox1_Click()) to no avail..

Private Sub ListBox1_Change()
choice = Split(ListBox1.Value, " ")
Sheets(choice(0)).Select
Range(choice(1)).Select

End Sub

Any suggestions?? Thanks!

[CDRIVE]

The ListBox Control doesn't have a 'Change' event.

[krees]

Hmm... OK, but 'Click()' didn't work either. Is it a matter of just finding the right control?

[krees]

Err.. Is it a matter of finding the right event?

[jcis]

Here a complete example assuming there is a workbook in C:\ named MyWB.xls

Private lEX As Excel.Application
Private lWB As Excel.Workbook

Private Sub Form_Load()
    Set lEX = New Excel.Application
    Set lWB = lEX.Workbooks.Open("C:\MyWB.xls")
    lEX.Visible = True 'So you can see what happends when you click
    'Fill List for this example
    With List1
        .AddItem "Sheet1 E1"
        .AddItem "Sheet1 E2"
        .AddItem "Sheet2 E3"
        .AddItem "Sheet2 E4"
        .AddItem "Sheet3 E5"
    End With
End Sub

Private Sub List1_Click()
Dim lChoice() As String

    lChoice = Split(List1.List(List1.ListIndex), " ")
    lWB.Sheets(lChoice(0)).Select
    lEX.Range(lChoice(1)).Select
    'Or you could directly add some text to the cell without select like this:
    lEX.Range(lChoice(1)) = "I'm cell " & lChoice(1)
End Sub

The App will work when you click an item in your ListBox or just move up/down arrows to change items.

[krees]

Thanks Jcis. That helps. It looks like you have to perform some action on the cell in question in order for excel to hop over to that cell. Merely selecting the cell will not transport you there while the userform is still open.

For my purposes, I am developing a precedent tracer that enables you to quickly navigate and view each precedent. Thus, the listbox is present only to display precedents, and enable the user to jump to the relevant address to see what's going on there (this is incredibly useful when auditing large financial worksheets). I was able to jerry-rig a command that copies and pastes the selected precedent's formula from and to itself. This allows the user to see the selected cell while scrolling through the listbox. Pretty stupid, I know, and there's probably a more elegant way around this problem, but it gets the job done.

I'll mark this thread as resolved but if anyone has more to add, please do so.

-Chris

[jcis]

Well, i tested this and it does what you need, if I use..

Private Sub List1_Click()
Dim lChoice() As String

    lChoice = Split(List1.List(List1.ListIndex), " ")
    lWB.Sheets(lChoice(0)).Select
    lEX.Range(lChoice(1)).Select
End Sub

.. it jumps to the Selected cell. There is no need to perform anymore actions.

[krees]

Hmm.. I must be doing something wrong JCis. I threw the code into a module (after I saved a workbook under the name MyWB) but it returns an error on the ".AddItem "Sheet 1 E1"" line informing me "Object required". Not sure if I needed to create a userform beforehand. I thought that's what the "Form_Load()" sub was doing.

On a separate note, my suspicion is that calling the lEX application (via "lEX.Range(1choice(1)).Select) is what enables the jump to the selected cell. That is the only substantive difference between your prototype code and mine. Or perhaps it is the "lEX.Visible = True"? Hmm...

[jcis]

You're ListBox name is List1? You're referencing the list from the module like FormName.ListName? another way would be sending the ListBox as parameter to that sub/function in the module.

Also there will be a problem if using "Sheet 1 E1", you are using 1 blank for splitting the string, that string has 2 blanks and will create 3 items in the array:

1) Sheet
2) 1
3) E1

So use another string and delimiter, like:

'Filling the list like: "Sheet 1,E1"
lChoice = Split(List1.List(List1.ListIndex), ",")

[Ellis Dee]

He's not using VB6 at all, but instead using Excel only. Excel listboxes default to the name ListBox1 instead of List1, and in Excel the default event is ListBox1_Change(), which doesn't exist in VB6.

I'm wondering how he "floats" his listbox between sheets. Doesn't it disappear when you click an item that sends you to a different sheet?

[krees]

Jcis: Sorry about the "Sheet 1 E1" mix-up. Just a typo on my part (supposed to be "Sheet1 E1"). Anyway, I got your prototype to compile and you're right, it does indeed jump between cells without having to actually do anything other than select the cell. Even the application.visible wasn't necessary.

For some reason though, my precedent tracer won't do the same thing. No big problem though as I simply used a Listbox1_Enter() event to select the first cell and paste it's formula into itself. Then I'm able to scroll through the cells no problem via the click() event.

Ellis: The userform remains active while jumping between sheets. I think it has something to do with the fact that focus is never entirely shifted to the spreadsheet (i.e. I cannot alter any cell's contents until I have closed the form). I didn't tweak with the modal/non-modal characteristic so I believe this is a stock feature.