Select UserForm ListBox Value For Closed WB Input

**bdb1974** · 04-19-2011, 03:49 PM

Hi all,

Seems my problems are never ending.

Using a userforms listbox item. BTW only 1 item is listed. I'm trying to select that
item with code so the value/text can be copied over to an previously closed workbook(WB). Currently, I'm trying to select the item using the following:

Dim LB as string
Me.ListBox5.Selected(Me.ListBox5.ListCount - 1) = True
LB5 = Me.ListBox5
'more code then
Msgbox LB5
Sheets(X).Cells(FindIt.Row, FindIt.Column).Offset(0, 1).Value = LB5

It seems that the method that I'm using to select the list box items doesn't always work.
My MsgBox will return a empty box. Where if I walk through the code and manually select the listbox I will get LB5 to hold the listbox5 value.

I've been trying to figure this out over the past few days. Any help as always much appreciated.

Thanks,

BDB

**davegugg** · 04-19-2011, 04:25 PM

It seems to me that you are not declaring your LB5 variable. You probably want to declare it as a string, then get the value from the listbox using the .Value property. As is, LB5 is not declared a certain data type, and when you set it to your listbox it probably is using the Object data type.

Dim LB as string
Dim LB5 as String
Me.ListBox5.Selected(Me.ListBox5.ListCount - 1) = True
LB5 = Me.ListBox5.Value
'more code then
Msgbox LB5
Sheets(X).Cells(FindIt.Row, FindIt.Column).Offset(0, 1).Value = LB5

**bdb1974** · 04-19-2011, 05:07 PM

davegugg, In my example i just forgot to show that I had dim'd LB5 As string.

I know that's not my problem.

In regards to setting
LB5 = Me.ListBox5.Value

I've tried this too.

I've got 3 list boxes I'm trying to obtain info from. I've placed a msgbox after each
string variable to visually show me what the value is for each LB. Running the following
code will leave the first LB empty, the second LB will capture the listbox2 value and
the 3rd LB will be empty.



   ' Toggling the property value to try to make it stick.
              Me.ListBox3.Selected(Me.ListBox3.ListCount - 1) = True
              Me.ListBox3.Selected(Me.ListBox3.ListCount - 1) = False
              Me.ListBox3.Selected(Me.ListBox3.ListCount - 1) = True
              MsgBox Me.ListBox3.Value  'gives back no value = empty
             ' MsgBox Me.ListBox3.ListCount
           
              LB3 = Me.ListBox3.Value
                 LB3 = LB3

         Trying some set property tricks to get the 2nd list box value 
                Me.ListBox3.Selected(Me.ListBox3.ListCount - 1) = False
 
                  Me.ListBox4.Selected(Me.ListBox4.ListCount - 1) = True
                 MsgBox Me.ListBox4.Value
                
              LB4 = Me.ListBox4.Value
               MsgBox Me.ListBox4.Value  ' This will show the listbox value
   



            
                 Me.ListBox4.Selected(Me.ListBox4.ListCount - 1) = False
     
                 Me.ListBox5.Selected(Me.ListBox5.ListCount - 1) = True
                 Me.ListBox5.Selected(Me.ListBox5.ListCount - 1) = True

             MsgBox ListBox5.Value  ' msgbox is empty
              LB5 = Me.ListBox5.Value
   MsgBox LB5   ' msgbox is empty 
                 LB5 = LB5

Still seems the only way I can get all 3 listboxes values is to manually select them.

I didn't want to do this but I might have better luck using textboxes.

Still hoping someone might have a solution.

thanks,

BDB

**bdb1974** · 04-19-2011, 05:45 PM

If a listbox contains an item (not empty) , then maybe do something like
for each item in listbox add to an array then from the array set a variable
equal to the element in the array?

Goto be another way of pulling this value out of the listbox.

BDB

**bdb1974** · 04-19-2011, 06:02 PM

Great news! I stumbled accross an idea which actually works.

Here's goes:

Dim LB3 As string
Dim lItem as Integer

For lItem = 0 To ListBox3.ListCount - 1

If ListBox3.Selected(lItem) = True Then
LB3 = Me.ListBox3.List(lItem)
LB3 = LB3
MsgBox LB3
ListBox3.Selected(lItem) = False
End If

Next

I did this for all 3 listboxes and got all 3 to return with the item listed.

Yeah!!!!

Regards,

BDB