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 = 0 To .ListCount - 1
.Selected(i) = False
Next i
End With

[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!!

[Sandler]

I am glad it worked out for you. Aflatoon, snb thanks for sharing your knowledge :)

[Juriemagic]

Yes, absolutely, thanx guys, your efforts and time spent on this is appreciated much more than you'll ever know!..stay well, until next time!