Yet more problems with Excel forms :(

[Richard Finnigan]

I'm using a form to maintain a data list in Excel and the list box click
event sets the scroll bar value to its index even when the click event hasnt
even happed, infact the only way to change the scrolbars value is to click
on the list box, If you click on the scroll bar it must also be triggering
the list box click event....is this a fault in the software perhaps ?

I fixed it by using the mouse down event on the list box and sure enough
everything works fine so its not my code

Private Sub lstCastNav_Click()
scrNav.Value = lstCastNav.ListIndex + 1
End Sub

Private Sub scrNav_Change()
If doSave = True Then
Call SaveRecord
End If
Set rgData = Range("cast_name").Rows(scrNav.Value)
Call LoadRecord
doSave = True
lstCastNav.RowSource = "CastFnames" 'maybe this is triggering a listbox
click event ?
End Sub

I'm really annoyed about this and other problems with Excel forms because
I've always asumed that Microsoft make rock solid reliable products, When
it comes to Excel this does not seem to be the case.

If anyone wants to try to reproduce this problem on their computer in order
to see if this software fault also occurs with their installation of
Microsoft Office please go ahead and have a go, I would actually quite like
to believe that there is something wrong with my PC rather this being an MS
Office problem. I have installed all of the MS Office patches.

 

[RB Smissaert]

If you can post the file to me I will test.
Don't believe there is a bug in Excel here.
Did you run the CodeCleaner?

RBS

 

[Richard Finnigan]

Thanks for the offer.

Yeh I'm inclined to believe that such a major problem would be patched if it
was wide spread..I have tried using code cleaner and will continue to do so
thanks for that. If I get time I will try to reproduce this problem with
another file and if it reoccurs I'll send it to you.

I've put the list box code in a double click event and now everying seems to
be fine. Still I'd prefer to use a single click event.

Richard

 

[Mark Lincoln]

Richard, you asked if

lstCastNav.RowSource = "CastFnames"

is triggering a listbox Click event.

Have you tested for this by setting a breakpoint and running the code?
If it does, you'll see it happen as you step through the code in the
debugger. I can't count the number of times I've found that Excel is
behaving differently from what I want because it's doing exactly what I
told it to do.

On the other hand, Excel is a big, complicated mass of code that runs
within Windows (which is itself a big, complicated mass of code), and
some things that really should work just don't for some reason. I've
had several occasions where code didn't work and no one could figure
out why. That's why "workaround" is in our vocabulary.