ListBox selected item retrieval help

[JLynn]

Hello,

This is my first time posting on this site and I am a beginner VB programmer. I have been able to solve many of my previous questions through existing threads but have not found what I need to solve this problem. My problem is that I have a listbox on my main sheet that I can successfully import data to but am having trouble retrieving the selected items and using them in further calculations. Where I am having the biggest problem now is in the following piece of code:

ActiveSheet.Shapes("Set_Listbox").Select
 Dim lItem As Long
 Dim Count As Integer
 Set Count = Set_Listbox.ListCount              (Problem here!!!)
 For lItem = 0 To Count - 1
      If ListBox1.Selected(lItem) = True Then
            Sheets("Main").Range("A65536").End(xlUp)(25, 7) =              ActiveSheet.Shapes("Set_Listbox").List(lItem)
            ActiveSheet.Shapes("Set_Listbox").Selected(lItem) = False
      End If
 Next lItem

I am getting the error that an object is required and after over 4 hours of trying to look for existing threads on the subject, I have not found much help. Any help will be greatly appreciated.

Thanks a lot,

Jordan

[AndreRet]

I'm not exactly sure, but it looks like the error is generated at the following -

Set Count = Set_Listbox.ListCount
'Try to do the following
Set Count AS New Set_Listbox.ListCount
'or if that also generates an error, the following
Set Count AS Set_Listbox.ListCount

I'm not sure if this will help, but worth a try. Also make sure that all your references is in place.

[JLynn]

Thanks for the reply. Sorry it took me so long to reply but I was not at work Saturday or Sunday. I just tried the code but I had no luck. An error occurs because VBA does not like "Set...As" and instead wants "Set...=" which I already had.

[cguan_77]

try this, remove Set Count = Set_Listbox.ListCount
replace with the code below,

For count = 0 To Set_Listbox.ListCount - 1 
Next count

you just want to get the total list count..and use on the other loop right?
hope it helps :)

[JLynn]

Unfortunately that did not work either. I believe my program does not recognize Set_Listbox as an object and consequently will not allow me to assign a function to Set_Listbox. I did name my listbox as Set_Listbox earlier in my code and I believe I did it correctly because I was able to import the desired data to that listbox. However, I have problems extracting that selected data.

Thanks for your help.

[elkhartlynn]

The SET command is used with an object. Count as you're using it is a
variable you can use LET instead of SET but you don't need that either just use
[bbcode]
Count = set_listbox.listcount
[/bbcode]

[JLynn]

I think that would work if my code recognized Set_Listbox as an object. My basic question now is how do I create a listbox and name it so that that listbox is an object and recognized by my code?

[elkhartlynn]

What name did you give the listbox?

I think that would work if my code recognized Set_Listbox as an object. My basic question now is how do I create a listbox and name it so that that listbox is an object and recognized by my code?

[JLynn]

I created the listbox and named it Set_Listbox in one macro and am trying to populate that listbox and extract the selected entries in another macro. Below is my code:
I created and named the listbox here:

Sub Set_Listbox()
    Range("D22").Select
    ActiveSheet.ListBoxes.Add(260, 350, 200, 210).Select
    Selection.ShapeRange.LockAspectRatio = msoTrue
    Selection.ShapeRange.Height = 216#
    Selection.ShapeRange.Width = 144#
    With Selection
        .Placement = xlFreeFloating
        .PrintObject = True
    End With
    With Selection
        .LinkedCell = ""
        .MultiSelect = xlSimple
        .Display3DShading = False
        .Name = "Set_Listbox"
    End With
End Sub

And I am trying to populate the listbox and extract the selected data here:

Sheets("Main").Select
    ActiveSheet.Shapes("Set_Listbox").Select
    With Selection
        .ListFillRange = "'Temp. Charts'!B2:B" & vend4
        .LinkedCell = ""
        .MultiSelect = xlSimple
    End With
    Sheets("Main").Activate
    Cells(28, 7).Select
    ActiveSheet.Shapes("Set_Listbox").Select
    Dim Item As Long
    For Item = 0 To Sheet4.Set_Listbox.ListCount - 1          [Problem here]
        If Set_Listbox.Selected(Item) = True Then
            Sheets("Main").Range("A65536").End(xlUp)(25, 7) = ActiveSheet.Shapes("Set_Listbox").List(Item)
            ActiveSheet.Shapes("Set_Listbox").Selected(Item) = False
        End If
    Next Item

My Set_Listbox is not being recognized as an object. Any ideas?

Thanks a lot.

[elkhartlynn]

After playing with this for some time, I finally discovered that this is the easiest way to create and rename a listbox in VBA code.

Sub CreateListBox()

Dim Obj As Object
Sheets("Sheet2").Select

'create ListBox
    Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", _
    Link:=False, DisplayAsIcon:=False, Left:=Range("D2").Left, Top:=Range("D2").Top, _
    Width:=100, Height:=35)
    Obj.Name = "Set_ListBox"
TestIt
End Sub

Sub TestIt()
    MsgBox "ListBox has " & Sheets("Sheet2").Set_ListBox.ListCount
End Sub

[JLynn]

Thanks for your help. I believe I now have the problem fixed. Thanks again.

Jordan

[José Luís]

I have a List box wich returns a previous criterium

    criterio = "select * from doc where doc.RegCompl = '" & Text1.Text & "'"
    criterio = criterio & " order by doc.NRegisto"
    Data1.RecordSource = criterio
    Data1.Refresh
    On Error Resume Next
    Data1.Recordset.FindFirst criterio

    If Data1.Recordset.NoMatch = True Then
        MsgBox "Registo não encontrado"
        Me.Width = 9930
        Me.Height = 2475
        Text1.Text = ""
        Text1.SetFocus

    ElseIf Data1.Recordset.NoMatch = False Then
        Me.Height = 12645
        Me.Width = 16770

        List1.Clear

        Do While Not Data1.Recordset.EOF
            List1.AddItem Data1.Recordset("NRegisto") & " - " & Data1.Recordset("Dataregist1") & ": " _
            & ": " & Data1.Recordset("Estado") & "; " & Data1.Recordset("Autor")
            Data1.Recordset.MoveNext
        If Data1.Recordset.EOF Then
            Data1.Recordset.MovePrevious
        Exit Do
        End If
        Loop
        Endif

How can I retrieve the NRegisto value from the List1 for a label?
Tnahks