remove selection from listbox.

[Joe]

Hi all,

Tool : Excel 2003 SP2 & VBA

I am using a listboxs to show a list of items. Few of these are
priority items.
I show only those items first. Also at the bottom I added an item
"Show full list".
When the user clicks the item "Show full list", the listbox will show
all the items. If the user clicks any other item, it will show its
children items in a second list box.

I use the following code for the same. I use a function
"refresh_Listbox" to do the adding of the contents.

Level01 = First listbox
Level02 = Second Listbox

user clicks "Show full list" (here a variable named More).
refresh_Listbox(1, False) means refresh the listbox no 1 with all
items.

user clicks "Show full list" (here a variable named More).
refresh_Listbox(2, True) means refresh the listbox no 2 with only
priority items.

' ***************************

Private Sub Level01_Click()

If Level01.Value = More Then

' MsgBox Level01.ListIndex
temp = refresh_Listbox(1, False)
Level01.ListIndex = -1

Else
temp = refresh_Listbox(2, True)
End If

End Sub


' ***************************


All of these works fine except for one thing. the command
<Level01.ListIndex = -1> is not working. That means, I want to
remove the selection once it is updated. But instead what happens is
that, if the "Show full list" is the fifth item before refreshing, the
fifth item gets selected after refreshing. I dont want that to be
selected. Infact during refresing operation I clear the list and
add everything fresh. Even then it works this way.

Interestingly, if I use a msgbox command somewhere (for eg <MsgBox
Level01.ListIndex> in the code above) then it works. I mean the
Listbox will not have any selection, even if you remove the code <
Level01.ListIndex = -1>.

I have spend a day trying to figure out a way around this. Only time
it works is when I have a msgbox, which is an irritation for the user
and I dont prefer that.

I have even tried putting the code in MouseUp instead of Click. No
use.

I know this is slightly long and may be complicated. But if someone
can suggest something to understand why this is happening and a way
around, that would be great.

Thanks a lot.

Regards,
Joe

 

[Carlo]

Hi Joe

did you ever try to find out what the value is?
For me it looks like it sets the value, but does not
refresh the display of the selection.

Try and enter this line before and after the Level01.ListIndex = -1
debug.print Level01.ListIndex

if vba returns twice the same number, then something is weird.
If the second debug returns -1, then we have to see if you have
a Application.ScreenUpdating = false somewhere, or something
similar.

Hth

Carlo

 

[Joe]

Hi Joe

did you ever try to find out what the value is?
For me it looks like it sets the value, but does not
refresh the display of the selection.

Try and enter this line before and after the Level01.ListIndex = -1
debug.print Level01.ListIndex

if vba returns twice the same number, then something is weird.
If the second debug returns -1, then we have to see if you have
a Application.ScreenUpdating = false somewhere, or something
similar.

Hth

Carlo

























- Show quoted text -

Thanks Carlo......

Unfortunately its still unclear and is not solved.. I modified the
code as follows

' ***************************
Private Sub Level01_Click()


If Level01.Value = More Then

Debug.Print "before calling : " & Level01.ListIndex
temp = refresh_Listbox(1, False)
Debug.Print "after calling : " & Level01.ListIndex

Else
temp = refresh_Listbox(2, True)
End If

End Sub

' ***************************
result in Immediate window was....

before calling : 1
after calling : -1

------------------

Before calling : 1
makes sense as I had the "Show full list" as second in the list and
hence ListIndex =1
After Calling : -1
This also is OK because I had cleared the ListBox before add the
full contents..

what I dont understand is that after all this happens, VBA
automatically assign the ListIndex its previous value !!!!!
Is there anything like - since the execution started from
Level01.click, you cannot manually modify its own listindex from
within that...

?????????????


Thanks
Joe

 

[Carlo]

Hi Joe

that at least tells us, that the value is set properly.
Maybe Excel has some problem with refreshing the graphic of your list.

Let's try those things, to check, if something changes or not.

First make another button on your form with following sub:

sub button_click()
msgbox Level01.ListIndex
end sub

Click this button after the phenomena happens, just to see if it is still -1
Because if it is, than VBA does NOT assign the Value, but can simply not
redraw your list.

Then try following line after the "after" debug:
me.Level01.setFocus
If nothing happens, delete this line, and add these to lines:
me.Level01.visible = false
me.Level01.visible = true

If nothing helps.......then I am out of Ideas
Tell me how it works

Carlo

 

[Joe]

Hi Joe

that at least tells us, that the value is set properly.
Maybe Excel has some problem with refreshing the graphic of your list.

Let's try those things, to check, if something changes or not.

First make another button on your form with following sub:

sub button_click()
msgbox Level01.ListIndex
end sub

Click this button after the phenomena happens, just to see if it is still -1
Because if it is, than VBA does NOT assign the Value, but can simply not
redraw your list.

Then try following line after the "after" debug:
me.Level01.setFocus
If nothing happens, delete this line, and add these to lines:
me.Level01.visible = false
me.Level01.visible = true

If nothing helps.......then I am out of Ideas
Tell me how it works

Carlo















- Show quoted text -

Thanks again Carlo.

You have hit the nail on its head when u asked me to use msgbox from
another command button.
But strangely, it returned -1 where as the selection was still there
on item 2 in the listbox.

I tried both setting focus as well as toggling the visibility.. But
it seems excel wont budge that easily -

Hope someone else comes up with a solution !!!

Thanks again
Joe