Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

problem clearing a selectbox in VBA.

Function ClearList(lst) As Boolean
    Dim varItem As Variant

    With lst
        For Each varItem In .ItemsSelected
            varItem.Selected = False
        Next
    End With

    ClearList = True

End Function

I want to use the above function to clear the selections made in a selectbox.
However, I get the following error:

error# 424
Object required
(message was in dutch, so I translated).

I used the correct reference to the object while calling the function:
ClearList (Me!filter_CL_contract_status)

I have a `sister`-function that will get the selected items and convert that to a string that can be used in a where-condition. That one works just fine.

What am I doing wrong??
0
DoppyNL
Asked:
DoppyNL
  • 4
  • 3
1 Solution
 
dqmqCommented:
Try this:

With lst
        For Each varItem In .ItemsSelected
           .Itemdata(varItem).Selected = False
        Next
    End With
0
 
DoppyNLAuthor Commented:
no effect.

note: the actual error message occurs on the line with for.
0
 
dqmqCommented:
On second thought, the above for each construct may have the problem of progressively diminishing the .itemsselected collection at the same time it is iterating through it.  You may get undesirable results. If soi, try a for/next loop.

dim i as integer
with lst
   for i = 0 to .listcount -1
    .itemdata(i).selected = False
   next
 end with
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
DoppyNLAuthor Commented:
Same result; again `an object is required` on the line with for.

The sister function:

Function MultiWherePart(FormField, TableField As String, Optional Delimiter As String = "")
    ' Variables for multi-select
        Dim MultiSelected As Variant      'Selected items
        Dim MultiLength As Long          'Length of string
    '
    ' Generate part
        With FormField
            For Each MultiSelected In .ItemsSelected
                If Not IsNull(MultiSelected) Then
                    MultiWherePart = MultiWherePart & Delimiter & .ItemData(MultiSelected) & Delimiter & ","
                End If
            Next
        End With
       
        MultiLength = Len(MultiWherePart) - 1
        If (MultiLength > 0) Then
            MultiWherePart = TableField & " IN (" & Left$(MultiWherePart, MultiLength) & ")"
        End If
    '
End Function

Here the variable is called FormField
Both functions are called with exactly the same reference to exact the same object.
But MultiWherePart works fine...
0
 
dqmqCommented:
You are passing a listbox control, right?  Try this:

Function ClearList(lst as control) As Boolean

Also, put a message box inside the for loop to see if you are failing the first time through or after you have de-selected some of the rows.
0
 
jefftwilleyCommented:
on the form

ClearListBox me.name, "yourcontrolname"

The function

Function ClearListBox(strForm As String, strControl As String)
Dim I As Long
Dim f As Form
Dim c As Control
With Forms(strForm).Form(strControl)
    For I = 0 To .ListCount - 1
        .Selected(I) = False
    Next I
End With
End Function

0
 
DoppyNLAuthor Commented:
Using this:
Function ClearList(lst as control) As Boolean

The same error moves to the function call.
So it seems that the object I'm passing is not what it supposed to be.

However, the function cal to the other function works fine. Also when I also add "control" there.
Both function calls look like this.

ClearList (Me!filter_CL_contract_status)
Add = MultiWherePart(Me!filter_CL_contract_status, "[CL_contract_status]")
0
 
DoppyNLAuthor Commented:
Jeftwilley's suggestion works perfectly :)

All items are deselected as required.

Accepting that solution.

A big thanks to everyone for their help.
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!

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