PDA

View Full Version : [SOLVED] Strange behaviour ListBox.Selected(index).



stranno
01-03-2014, 03:49 PM
Hi,

In an userform I have a listbox. This listbox is loaded with filenames. In another userform I have another listbox loaded with the same (and more) filenames. if I double click on a filename in the first listbox, the corresponding filename in the second listbox should be selected (by code of course). The strange thing is that it does work sometimes, but most of the time it doesn't. Maybe one of you guys experienced the same behaviour? In my case i found a workarround. Just before the item in de second listbox is beeing selected, i let a msgbox appear. After a click on OK of this msgbox the filename selection succeeds all the time? Can someone explain what's going on here?

Regards, stranno

KevCarter
01-03-2014, 04:08 PM
Hi,

In an userform I have a listbox. This listbox is loaded with filenames. In another userform I have another listbox loaded with the same (and more) filenames. if I double click on a filename in the first listbox, the corresponding filename in the second listbox should be selected (by code of course). The strange thing is that it does work sometimes, but most of the time it doesn't. Maybe one of you guys experienced the same behaviour? In my case i found a workarround. Just before the item in de second listbox is beeing selected, i let a msgbox appear. After a click on OK of this msgbox the filename selection succeeds all the time? Can someone explain what's going on here?

Regards, stranno

Sounds like a little bug I fought with yesterday. Are you opening the second dialog on top of the first one?

Kevin

stranno
01-04-2014, 02:46 AM
Sounds like a little bug I fought with yesterday. Are you opening the second dialog on top of the first one?

Kevin

stranno
01-04-2014, 03:17 AM
The second userform is already loaded and visible (so is the second listbox). The first userform, which contains the first listbox, can be opened by clicking on a commandbutton on the second userform. Just before the item in the second listbox should be selected (by means of the code: userform2.listbox2.selected(i) or userform2.listbox2.listindex = i, the first userform is being unloaded.

KevCarter
01-04-2014, 03:57 AM
The second userform is already loaded and visible (so is the second listbox). The first userform, which contains the first listbox, can be opened by clicking on a commandbutton on the second userform. Just before the item in the second listbox should be selected (by means of the code: userform2.listbox2.selected(i) or userform2.listbox2.listindex = i, the first userform is being unloaded.

Sorry, my issue was different. I was opening a dialog with a listbox. It was opened via a button. Same code as I've used in other projects that never failed. In my other projects, the button was off to the side. In this project, the button was in the middle of the screen. The listbox in the dialog would pick up the mouse click from clicking the button, and the wrong item was almost always selected.. I moved the button off to the side and all is well. Strange behavior! Good luck solving your issue, I wish I could help.

Kevin

stranno
01-04-2014, 04:04 AM
Thanks Kevin, Does anyone else have a clue?

snb
01-04-2014, 04:21 AM
Why not showing us what you are referring to by posting a sample workbook ?

stranno
01-04-2014, 09:55 AM
Why not showing us what you are referring to by posting a sample workbook ?

I could have done that, but the procedures loads files from a specified folder on my computer. The listboxes would remain empty on somebody else computer. But i will adapt the code somewhat. Right now a have to walk the dogs. See you later.

SamT
01-04-2014, 10:48 AM
The second userform is already loaded and visible (so is the second listbox). The first userform, which contains the first listbox, can be opened by clicking on a commandbutton on the second userform. Just before the item in the second listbox should be selected (by means of the code: userform2.listbox2.selected(i) or userform2.listbox2.listindex = i, the first userform is being unloaded.

That doesn't make sense

UserForm2.CommandButton1.Click = UserForm1.Show
UserForm2.ListBox1.Select = UserForm1.Unload

stranno
01-04-2014, 01:02 PM
Yes it does makes sense. Keep in mind that the userform and listbox numbers do not refer to a synchronical order. Userform1 could also have been Userform2. It are just names. I know it's a bit confus. But in my last sentence, the first userform refers to userform2. Hold on, i will upload an example.

stranno
01-04-2014, 01:48 PM
Here is a workbook which demonstrates the unexpected listbox behaviour.

KevCarter
01-04-2014, 02:21 PM
Your code is selecting the same line in the second listbox as the first. I got it to work by changing:

UserForm2.ListBox1.ListIndex = i

to


UserForm2.ListBox1.Value = Value

Kevin

GTO
01-04-2014, 02:25 PM
When you double-click in the unsorted list box, let us say the first entry "Fuller", do you want "Fuller" to be found and selected in the sorted listbox, or do you want the same position/index selected?

Mark

SamT
01-04-2014, 05:11 PM
Yes it does makes sense. Keep in mind that the userform and listbox numbers do not refer to a synchronical order. Userform1 could also have been Userform2. It are just names

:rotflmao:

stranno
01-04-2014, 05:42 PM
He guys,
I'am so sorry. Forget the previous workbook. I made a mistake because i was in a hurry. Hereby the right workbook. This one does demonstrate what i meant.
Note: if you activate the messagebox "MsgBox "Index: " & i & " = " & UserForm2.ListBox1.List(i)" it goes right. What is the problem here?
regards, Stranno

stranno
01-04-2014, 05:48 PM
And SamT, My comment wasn't meant cynical. Sorry if you experienced it that way.
regards, Stranno

GTO
01-05-2014, 12:46 AM
Yes it does makes sense. Keep in mind that the userform and listbox numbers do not refer to a synchronical order. Userform1 could also have been Userform2. It are just names. I know it's a bit confus (sic - confusing). But in my last sentence, the first userform refers to userform2. Hold on, i will upload an example.

Dear Stranno,

I do not oft gamble, but I'd put a nice sum on this: Most of us would have read it (I did) just the way Sam did. I would suggest that it is not a bit confusing. It is very confusing and counter-intuitive. While I am sure it was unintentional on your part, could I ask you to consider these things? In short - maybe take a moment of effort on your part, in order to help those willing to donate their time to you; or, at least, not to write the code (including naming) in a manner confusing to the vast majority. Does that make sense?

Now, I didn't really spot any big differences between the two workbooks posted, but I admit that I only glanced at the one attached to #15. Here is the workbook from #11, and what I am guessing at, as you did not answer my question at #13.

In UserForm2 (the 'parent' or FIRST form):


Option Explicit

Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Sub CommandButton1_Click()

With UserForm1 '<---SECOND form
'// Make the SECOND form show up where it's not on top of the first. //
.StartUpPosition = 0
.Left = Me.Left + Me.Width
.Top = Me.Top + ((Me.Height - .Height) / 2)
'// Note: This form's caption needs to be unique, as the classname is not. //
.hWndForm1 = FindWindow("ThunderDFrame", Me.Caption)
.Show vbModeless
End With

End Sub

Private Sub UserForm_Initialize()
Dim rngLastCell As Range

'// Find the last cell with data... //
Set rngLastCell = RangeFound(Blad1.Range("B:B"))

'// ...and IF we find any cells with data in the appropriate column, set the //
'// range based on that.
If Not rngLastCell Is Nothing Then
Me.ListBox1.List = Blad1.Range(Blad1.Range("B1"), rngLastCell).Value
End If

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

'// Maybe add? Get rid of the SECOND form if the first form (USERFORM2) is //
'// dismissed first. //
On Error Resume Next
Unload UserForm1
On Error GoTo 0

End Sub


In UserForm1 (the 'child' or SECOND form):

Option Explicit

Private Declare Function SetFocusAPI Lib "user32" _
Alias "SetFocus" (ByVal hWnd As Long _
) As Long

Private Userform1Hwnd As Long

Public Property Let hWndForm1(h As Long)
Userform1Hwnd = h
End Property
Public Property Get hWndForm1() As Long
hWndForm1 = Userform1Hwnd
End Property

Private Sub UserForm_Initialize()
Dim rngLastCell As Range

Set rngLastCell = RangeFound(Blad1.Range("A:A"))

If Not rngLastCell Is Nothing Then
Me.ListBox1.List = Blad1.Range(Blad1.Range("A1"), rngLastCell).Value
End If

End Sub

Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) 'open het geselecteerde project
Dim Index As Long
Dim Ret As Long
Dim tmp

tmp = UserForm2.ListBox1.List

On Error Resume Next
Index = -1
'// MATCH is 1-based, so we minus 1 for ListIndex, which is 0-based. Due to //
'// On Error Resume Next, Index will remain -1 if no match occurs. //
Index = Application.Match(Me.ListBox1.Value, tmp, 0) - 1
On Error GoTo 0

If Index > -1 Then
UserForm2.ListBox1.ListIndex = Index
Ret = SetFocusAPI(hWndForm1)
DoEvents
UserForm2.ListBox1.SetFocus
End If
End Sub

In a Standard Module:

Option Explicit

Function RangeFound(SearchRange As Range, _
Optional ByVal FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range

If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange(1)
End If

Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function

General Notes:
I made the API calls private, as the example is small. I normally would place all the API Functions and any call backs in one Standard Module.

IMO, you should be using Option Explicit at all times. You are declaring your variables (good IMO), why not use Option Explicit as a "Spell Check" if nothing else?

If there is any way for the second form to be showing on its own (there is, just close the first form with the second still displayed), then I would think (not tested, eyes already bleary...) you need to handle the double-click event.

Hope that helps,

Mark

ZVI
01-05-2014, 12:55 AM
Try also another way of finding the selected item (but it's without changing of userform's focus)

stranno
01-05-2014, 05:03 AM
Hi Mark,

After i had read your comment, i knew that the code in the workbook i had posted was wrong because i wasn't interested in "the same position/index selected", but in the value i.e "Fuller". So your comment was very helpful to me. (I admit that i even confused myself with my inimitable story. Sorry again for that).

But with regard to the second workbook? you said that you didn't spot any big differences between the two books. But IMO the difference is decisive. And i still have no answer on this question. Why does the code do what it's "supposed to do" if the msgbox code is active and fails it if the msgbox code is disabled?

And don't worry, i always use Option Explicit. But as I said, i was in a hurry.

Thanks for the code you've came up with. I' am going to look at it now. I'll let you know if it's useful in my case (although i don't really doubt about that). The reason why i prefer not to use API calls is that most of my (unpretending) VBA-programmes should run in different enviroments (32 bit and 64 bit computers, and Office 2003 - 2013). I know there are rubust solutions for this problem but nevertheless, in case of relative small programs i prefer to avoid API calls.

But having said this i'm affraid that my above stated original question is still not answered. (Don't hope this makes you angry with me again ;-))

regards, Stranno

stranno
01-05-2014, 05:17 AM
Hi ZVI,

Thanks for your solution. But in my "real" programm the listboxes are populated with files (no columns on sheets), which are read from a folder. So i can't refer to specific cells. Also the arrays i used to load the listboxes are erased already at this moment.

regards, Stranno

snb
01-05-2014, 06:18 AM
In userform 1:



Private Sub UserForm_Initialize()
ListBox1.List = Blad1.Columns(1).SpecialCells(2).Value
End Sub


Sub ListBox1_change()
UserForm1.Tag = ListBox1.Value
UserForm2.Show
End Sub

In Userform2:


Private Sub UserForm_Initialize()
ListBox1.List = Blad1.Columns(2).SpecialCells(2).Value
ListBox1.Value = UserForm1.Tag
End Sub

How to fill listboxes / comboboxes: http://www.snb-vba.eu/VBA_Fill_combobox_listbox.html

PS. Dit had ik je ook op worksheet.nl kunnen vertellen.

stranno
01-05-2014, 08:18 AM
Hi,

thanks a lot all of you for investing your time in my case. I finaly found a solution. As a matter of fact it was quite simple after all (see attached workbook)
KevCarter brought me to the idea of a different approach. But i have still no clue why my first attempt did not work.

kind regards, stranno

snb
01-05-2014, 08:25 AM
That's unnecessarily complicated.

stranno
01-05-2014, 08:31 AM
Hallo snb,

Sorry this is a quick answer in Dutch.
Mijn probleem was niet dat ik niet wist hoe ik een listbox moest vullen, maar waarom een bepaalde methode niet werkt. Dat wil zeggen soms wel werkt maar meestal niet.
Op die vraag heb ik nog steeds geen antwoord. Bovendien vul ik de listbox niet met waarden uit een werkblad maar met bestanden uit een map. Omdat het programma niet op
andere computers zou werken (mijn map bevat honderden bestanden en staat alleen op mijn computer of op de computer van de gebruiker), heb ik deze waarden gefingeerd
door een willekeurige database in kolom A en B te dumpen. Voor het probleem maakt het niet uit. Als je tijd hebt zou ok het op prijs stellen als jij eens zou wilen kijken naar het tweede
werkboek dat ik heb opgestuurd. Daarin gebeurt precies wat ik bedoel. Het verkeerde item wordt in listbox1 op userform2 geselecteerd nadat je op het overeenkomstige item
in listbox1 op userform1 hebt gedubbelklikt. Maak je echter de msgbox actief door het aanhalingsteken ervoor weg te halen, dan gaat het wel goed. Hoe kan dit? Dat was mijn vraag.

vriendelijke groet, Stranno

stranno
01-05-2014, 08:37 AM
so it was not unnecessarily complicated, right?

stranno
01-05-2014, 08:42 AM
By the way snb, I found some valuable tips on your site. Good work!!

snb
01-05-2014, 01:56 PM
You definitely must have overlooked my answer in #21

stranno
01-05-2014, 02:12 PM
Sorry Snb, I don't get it. You mean the Tag phenomena?
Or do you refer to the dutch part of the text?

snb
01-05-2014, 02:32 PM
See the attachment

stranno
01-05-2014, 02:52 PM
Thanks, tomorrow i will take a look at it. My ipad is not able to open these files.

Aflatoon
01-06-2014, 03:51 AM
It's an issue of timing and focus. I suggest you do one of:
1. Use one form (say with a Multipage control)
2. Don't use the double-click event. Use a button press after selecting the item in the listbox.
3. Use modal forms.

#2 would be my personal preference.

stranno
01-08-2014, 10:41 AM
It's an issue of timing and focus. I suggest you do one of:
1. Use one form (say with a Multipage control)
2. Don't use the double-click event. Use a button press after selecting the item in the listbox.
3. Use modal forms.

#2 would be my personal preference.

In the meantime i have chosen for another method, but you're probably right. It has something to do with de focus i guess. I will also try your suggestion using a button instead of the double click event. thanks Aflatoon.