Clear multi - select Listbox

[Juriemagic]

Hi good people!,

I have a multi-select listbox. This box is on a worksheet. Only the items appear in the listbox, there are no tickboxes next to them. if I select an item, a blue line goes through it. Every selection I make is transferred to the same sheet to column F. I am struggling to get my macro button to clear the blue lines (deselect the items). These are the codes I currently have:
ListBox3 code:

Dim i As Integer, c As Long
With ActiveSheet.Shapes("ListBox3").OLEFormat.Object
            For i = 1 To .ListCount
              If .Selected(i) Then
                c = c + 1
                Cells(c, "F") = .List(i)
            End If
        Next i
End With

and macro button code:

Sub ClearSelections()
'
' ClearSelections Macro
'
'
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Select
    Range("F1:F16").Select
    Selection.ClearContents
    With Sheet1
    .ListBox3.Clear
End With

   Range("F1").Select
End Sub

I get "method or datamember not found.". This part is yellow:

Sub ClearSelections()

and this part is blue:

.ListBox3

.

would someone please be so kind and help me with this...I am not very clued up when it comes to coding. Thank you all very much..

[Sandler]

Found on a Microsoft site, with similar issue...

Worksheets("Sheet1").OLEObjects("ListBox1").Object


With Worksheets("Sheet1").OLEObjects("ListBox1").Object
For i =0To.ListCount -1
.Selected(i)=False
Next i
EndWith

[SamT]

I get "method or datamember not found.". This part is yellow: Sub ClearSelections()

Yellow is the next line of code to be executed.

Blue is is near where the problem is.

There is no "Clear" method for a ListBox, (or any other form control that I recall.)

[Juriemagic]

Hallo Sandler,

I have utilized your code like this:

Sub ClearSelections()
'
' ClearSelections Macro
'
'
    Worksheets("Sheet1").OLEObjects("ListBox3").Object
    With Worksheets("Sheet1").OLEObjects("ListBox3").Object
    For i = 0 To .ListCount - 1
    .Selected(i) = False
  Next i
End With

However, I now get an error "Unable to get the OLEobjects property of the worksheet class". This line is yellow:

Worksheets("Sheet1").OLEObjects("ListBox3").Object

Thanx for your time...

[Juriemagic]

Thanx SamT, yes I understand the yellow and blue line properties, wasn't sure of the "Clear" method though...Thanx for your time..

[snb]

use:

 
With Sheet1.ListBox3.Object
    For j = 0 To .ListCount-1
        If .Selected(j) Then
            sheet1.Cells(1, 6) = .List(j)
           .Selected(j) = False
        End If
    Next
End With

[Juriemagic]

Hi snb,

I get "method or datamember not found"...I keep getting this no matter what coding I try...

[snb]

What is the codename of the sheet that contains the listbox ?

Why don't you mention the codeline that errors out ?

Did you use F8 ?

[Juriemagic]

If I look right at the top of the sheet module I see

Book2.xlsm[Break] - [module1(code)]

, I don't know if this tells you anything about the codename. This part of the code highlites blue:

.ListBox3

. Not sure how to use F8 and what it will tell me, but I'll check that out in the meantime...Thank you..

[Juriemagic]

F8 runs from "Sub Clear Selections" to

With Sheet1.ListBox3.Object

. On pressing again F8 the mentioned error displays..

[snb]

You'd better start to master the VBA fundamentals first.


Run this code:

Sub M_snb()
    MsgBox ActiveSheet.CodeName
End Sub

[Juriemagic]

Yes I know I lack big time, but I find myself more often not thinking at all...my apologies. I was in break mode before, therefore the stupid reply in thread 9. I checked again now, the code name is "Sheet1".

[Juriemagic]

snb,

I have created another List Box. I then adapted the clear code to this:

Sub ClearSelections()
'
' ClearSelections Macro
'
'
    With ActiveSheet.Shapes("List Box 4").OLEFormat.Object
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            Sheet1.Cells(1, 6) = .List(i)
            .Selected(i) = False
        End If
    Next
End With
 
   Range("F1").Select
End Sub

A new error I now get:

Unable to get the selected property of the list box class

. This part is yellow:

If .Selected(i) Then

. Maybe it's a bit easier from this point on?

[Aflatoon]

It looks like you are using a Form control rather than activex. The index for those starts at 1 not 0, so you'd need:

For i = 1 To .ListCount

[Juriemagic]

Aflatoon,

there's a bit of progress..yes, it's a Form Control, I really am sorry if I mislead anyone on that one...I changed the "0" to a "1", still get the same error but on this this line (yellow)..

Sheet1.Cells(1, 6) = .List(I)

thanx for your time..

[snb]

Why not uploading a file ?

[Aflatoon]

still get the same error but on this this line (yellow)..

Sheet1.Cells(1, 6) = .List(I)

You couldn't really get the same error on that line.

[Juriemagic]

I have uploaded the file...thanx guys..

[Aflatoon]

Works fine except in the ClearSelections code you have:

    For i = 1 To .ListCount - 1

which should be:

    For i = 1 To .ListCount

as in the other code.

[Juriemagic]

I feel like crying with happiness!!!!..gosh I have been sitting with this now for 3 days literally!...Thank you very very much Aflatoon..what a relief!!..have a nice day!!