[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

problem clearing a selectbox in VBA.

Posted on 2007-03-30
8
Medium Priority
?
312 Views
Last Modified: 2013-11-27
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
Comment
Question by:DoppyNL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 18822293
Try this:

With lst
        For Each varItem In .ItemsSelected
           .Itemdata(varItem).Selected = False
        Next
    End With
0
 
LVL 6

Author Comment

by:DoppyNL
ID: 18822312
no effect.

note: the actual error message occurs on the line with for.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 18822314
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 6

Author Comment

by:DoppyNL
ID: 18822323
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
 
LVL 42

Expert Comment

by:dqmq
ID: 18824479
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
 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 total points
ID: 18831102
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
 
LVL 6

Author Comment

by:DoppyNL
ID: 18834736
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
 
LVL 6

Author Comment

by:DoppyNL
ID: 18834743
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question