excel vba listbox variable type miss match

0
votes

I've been struggling for a while now with passing a listbox to a sub. After 3 days of searching... i need help. My ultimate goal is to select item in one listbox then click button and have it move to another listbox. Here's what I've tried so far.

In UserForm:

Private Sub CommandButton7_Click()
Dim lbINDX As Integer
Dim lbSource As String, lbDest As String

lbINDX = ListBox3.ListIndex
lbSource = Me.ListBox3.Name
lbDest = Me.ListBox1.Name
Call move(lbINDX, lbSource, lbDest)

End Sub

In Userform sub I've also tried: (without .name) lbSource & lbDest as object, control, listbox, and msforms.listbox

In Module:

Private Sub move(itm As Integer, objTarget As String, objDest As String)

txt = objTarget.ListIndex(itm).Text
UserForm10.Controls(objTarget).RemoveItem Item:=itm
UserForm10.Controls(objDest).AddItem txt

End Sub

In Module sub I've also tried: (with and without Userform10.Controls()) objTarget & objDest as object, control, listbox, and msforms.listbox

How do I pass the listbox as a whole through a variable? Or is there an easier way?

Revision 1 in response to Nathan_Sav:

Private Sub CommandButton7_Click()
Dim lbINDX As Long
Dim lbSource As MSForms.ListBox, lbDest As MSForms.ListBox

lbINDX = ListBox3.ListIndex
Set lbSource = Me.ListBox3
Set lbDest = Me.ListBox1
Call move(lbINDX, lbSource, lbDest)

End Sub

and

Private Sub move(lngIndex As Long, objSource As MSForms.ListBox, objDest As MSForms.ListBox)

objDest.AddItem objSource.ListIndex(lngIndex).Text
objSource.RemoveItem Item:=lngIndex

End Sub
2
excelvbalistbox
Appologies, I left out a critical part. I am getting 'Type Mismatch' error at "Call move(lbINDX, lbSource, lbDest) - gde211
The 1st var, itm needs to be long msdn.microsoft.com/en-us/library/office/ff193579.aspx itm also needs to be like Index:=itm - Nathan_Sav
@Nathan_Sav got the 'long' part although im not sure why, but the 'like Index:=itm' is throwing me off. Where would this go? in the module or userform sub? - gde211
No, where you have removeitem item=itm, should be removeitem index=itm, i believe msdn.microsoft.com/en-us/library/office/ff194439.aspx - Nathan_Sav
just saw your item vs index comment. changed that but same error... its not even getting that far. (type mismatch @ Call move(blah, blah,blah). Also, to be noted that when i hover over me.listbox# its returning a string for the selected value in the listbox not the listbox itself. - gde211

2 Answers

0
votes

I would do like this, not tested

Public Sub MoveEntry(cboSource As MSForms.ComboBox, cboDestination As MSForms.ComboBox, lngIndex As Long)
    cboDestination.AddItem cboSource.ListIndex(lngIndex).Text
    cboSource.RemoveItem Index:=lngIndex
End Sub
0
votes

After some help from Nathan_Sav and some further tinkering I have arrived at the following. Although it does what I needed, it's not quite finished as it adds the value to the bottom of the destination listbox, regardless of how many blank ("" or null or whatever) values there are.

In Userform

Private Sub CommandButton7_Click()
Dim lbINDX As Long
Dim lbSource As MSForms.ListBox, lbDest As MSForms.ListBox

lbINDX = ListBox3.ListIndex
Set lbSource = Me.ListBox3
Set lbDest = Me.ListBox1
Call Module1.MoveEntry(lbINDX, lbSource, lbDest)

End Sub

In Module Sub MoveEntry(lngIndex As Long, objSource As MSForms.ListBox, objDest As MSForms.ListBox) Dim txt As String

MsgBox objSource.Name
txt = objSource.Value
objDest.AddItem txt
objSource.RemoveItem lngIndex

End Sub

As it stands I am only allowing one value to be transferred at the time. I suspect that if the listbox were multiselect, there would need to be further modifications.

Thank you very much Nathan for your help.

If someone can please explain why this worked but the other attempts did not please post an answer so I may make it as answered.