on double click item in listbox (multi-select) call sub with variable of item that has been clicked

[dumbledown]

Hi,

I have a multi-select listbox within a userform.

After double clicking on a value within it I want to call a sub (easy, even I can do that) that uses the item that has been clicked on as a variable.
I'm unwilling to change the multi-select listbox to something else as it has other functionality.



Not sure how you get the item as a variable?

Any help would be great

Thanks

[Micron]

Not sure if you want to enumerate or only grab one item. This is part of a sub that moves selected items to a related listbox whose list of chosen values become the criteria of a query. The key part is in red

Dim varItem as variant
Set lstFrom = Me!lstDept
' Enumerate through selected items.
    For Each varItem In lstFrom.ItemsSelected
  yourVariable= lstFrom.ItemData(varItem) 'Assigns value to varItem
        'Using direct listbox reference and string var - adds new item
        Me!lstDeptCriteria.AddItem strCriteria
    Next varItem

set lstFrom = Nothing

[dumbledown]

Thanks for your response Micron,

I will have a look at that, but in the mean time I can tell you I just want to grab the name of the single item I double click on.
I will then use this name elsewhere (i.e. run a sub with this name as a table name)

So if there is a simpler way to do this than your what looks effective code, please let me know!

Thanks
Hugh

[ItsMe]

...uses the item that has been clicked on as a variable...

I do not see how this is going to happen with a Multiselsect. IIRC, the Value property is no longer a valid property in Multiselect mode. So that is out. That leaves you with the ItemsSelected collection. I was thinking maybe go after the ordinal position of an Item within the collection. However, the collection uses a key. It does not care what order the User selected the items.

In other words, the following will always produce the greatest Key, regardless of the order the Items were selected.

Dim intCount As Integer
intCount = Me.List0.ItemsSelected.Count
Debug.Print Me.List0.Column(0, Me.List0.ItemsSelected(intCount - 1))

I looked at the other members of the control, but could not find anything that would tell us what Item was clicked last.


.

[dumbledown]

Thanks Micron,

do you think it could work on a single-select listbox easily (I may be able to do a workaround)?

[Micron]

We are trying to work within your constraints, but you are trying to use a multi select listbox as though it were single select. As noted, it seems you are stuck with using the listbox collection or iterating through the entire list to see if an item is selected. In the first case:

Dim varItem As Variant, yourVariable As Variant
Dim ctl As Control
Set ctl = Me.Needs
For Each varItem In ctl.ItemsSelected
 yourVariable = ctl.ItemData(varItem)
 Debug.Print yourVariable
Next

OR as ItsMe has suggested, or a variation (missing declarations):

 
For intCurrentRow = 0 To ctlSource.ListCount - 1 
  If ctlSource.Selected(intCurrentRow) Then   
    strItems = strItems & ctlSource.Column(0, _   intCurrentRow) & ";"   
  End If   
Next intCurrentRow

I think none of these will work as you wish as long as the listbox is multi select.

[Micron]

I just posted as the email notice for your last post came in. Perhaps you already got the drift that a single select box is what would work best. In that case, on listbox double click simply

debug.print lstYourListbox

If it's possible that the listbox could have no items and someone were to double click on it, you should check the selected count first.

If lstYourListbox.ItemsSelected.Count = 0 then
 msgbox "No list item was selected."
 Exit Sub
End If

[dumbledown]

Awesome, thanks guys