Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2011
  • Last Modified:

Excel VBA - Deselect All Items in Listbox on a form

Hi,
I have written some code to "Deselect All Items in Listbox on a form".  

Why is it not working, see attached code.

The Error Message is:
"Run-time error '438': Object doesn't support this property or method"

Thanks!
Sub TestRun()

Call ListboxDeselectAllItems(MainForm.lstbxBrand_Converted)

End Sub


Public Sub ListboxDeselectAllItems(TheListBox As Object)
Dim TheItems As Variant

    If TheListBox.MultiSelect = 0 Then
        TheListBox = Null
    Else
        For Each TheItems In TheListBox.ItemsSelected
            TheListBox.Selected(TheItems) = False
        Next
    
    End If

End Sub

Open in new window

0
tgtg7
Asked:
tgtg7
  • 5
  • 3
2 Solutions
 
gowflowCommented:
What is it giving ?
gowflow
0
 
tgtg7Author Commented:
The Error Message is:
"Run-time error '438': Object doesn't support this property or method"

If the listbox is not multi select it works, it is the second part that is not working.
0
 
gowflowCommented:
offhand you can try the below code.
gowflow
Public Sub ListboxDeselectAllItems(TheListBox As Object)
Dim TheItems As Long

    If TheListBox.MultiSelect = 0 Then
        TheListBox = Null
    Else
        For TheItems=0 to TheListbox.ItemSelected -1
            TheListBox.Selected(TheItems) = False
        Next
    
    End If

End Sub

Open in new window

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
gowflowCommented:
Opps I don't think this will work Try this instead
gowflow
Public Sub ListboxDeselectAllItems(TheListBox As Object)
Dim TheItems As Variant

    If TheListBox.MultiSelect = 0 Then
        TheListBox = Null
    Else
        For Each TheItems In TheListBox.ItemsSelected
            TheItems.Selected = False
        Next
    
    End If

End Sub

Open in new window

0
 
gowflowCommented:
If it does not work the try replacing this line
TheListBox.Selected(TheItems) = False

by this
TheListBox.Selected(TheItems.Listindex) = False

gowflow
0
 
tgtg7Author Commented:
I get the same error on this line:

For Each TheItems In TheListBox.ItemsSelected
0
 
tgtg7Author Commented:
I tried all three options for TheItems.Selected = false,

I think the code is bombing on the line before, it is never getting to that line.  The line where the problem is i think is:

For Each TheItems In TheListBox.ItemsSelected

I code in Access alot, I kind of followed the same method I use there, my guess is Excel is different.
0
 
SiddharthRoutCommented:
One line code if it is not a multi select.

TheListBox.ListIndex = -1

Open in new window


If it is a multiselect

http://siddharthrout.blogspot.com/

Sid
0
 
gowflowCommented:
ok for sure this property does not exist. You did not mention where was the error so overlooked this one sorry.
gowflow
Public Sub ListboxDeselectAllItems(TheListBox As Object)
Dim TheItems As Long

    If TheListBox.MultiSelect = 0 Then
        TheListBox = Null
    Else
        For TheItems=0 to TheListBox.Listcount-1
            if TheListbox.Selected(TheItems) then TheListbox.Selected(TheItems) = False
        Next
    
    End If

End Sub

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now