View Full Version : [SOLVED:] Clear multi - select Listbox
Juriemagic
05-25-2016, 05:51 AM
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
05-25-2016, 07:11 AM
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
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
05-25-2016, 11:11 PM
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
05-25-2016, 11:14 PM
Thanx SamT, yes I understand the yellow and blue line properties, wasn't sure of the "Clear" method though...Thanx for your time..
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
05-26-2016, 02:56 AM
Hi snb,
I get "method or datamember not found"...I keep getting this no matter what coding I try...
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
05-26-2016, 03:27 AM
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
05-26-2016, 03:29 AM
F8 runs from "Sub Clear Selections" to
With Sheet1.ListBox3.Object. On pressing again F8 the mentioned error displays..
You'd better start to master the VBA fundamentals first.
Run this code:
Sub M_snb()
MsgBox ActiveSheet.CodeName
End Sub
Juriemagic
05-26-2016, 03:58 AM
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
05-26-2016, 04:11 AM
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
05-26-2016, 05:09 AM
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
05-26-2016, 05:30 AM
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..
Why not uploading a file ?
Aflatoon
05-26-2016, 05:44 AM
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
05-26-2016, 06:22 AM
I have uploaded the file...thanx guys..
Aflatoon
05-26-2016, 06:36 AM
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
05-26-2016, 06:49 AM
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
05-26-2016, 07:11 AM
I am glad it worked out for you. Aflatoon, snb thanks for sharing your knowledge :)
Juriemagic
05-26-2016, 07:25 AM
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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.