Listbox Use via VBA

jpskiller

New member
Local time
Today, 10:30
Joined
Nov 29, 2016
Messages
8
Hi,

I am very new to VBA and having issue with listbox.

for example

Formname = Main shows in VB as Form_MAin
Listbox = JobStatus
Multi Column

I have a function in a module

Code:
Public Function FillStatusList()

With Form_Main.JobStatus
    .ColumnCount = 2
    .RowSourceType = "Value List"
    .RowSource = ""
    .AddItem ("Loading Main WORKBOOK File;")
    .AddItem ("Loading Latest Cut Off File;")
    .AddItem ("Copy Data From Latest Orders File;")
    .AddItem ("Pasting Data into Main Workbook File;")
End With

End Function

If I call this from a button on the form it works, but if I try to call it from another function in the module it generates an error access does not allow you yo use this method in the current view.

I basically want to be able to fill a listbox on a form via the module so I can update column 2 later.

Help sppreciated
 
Listboxes do not work that way. That is an Excel listbox.
In access, you use a table to provide the list items.
If you need to add an item, run an append query to put it in the table.
 
... but if I try to call it from another function in the module it generates an error access does not allow you yo use this method in the current view.
Is the form open and in form view, when you call it from another function?
 
Listboxes do not work that way. That is an Excel listbox.
In access, you use a table to provide the list items.
If you need to add an item, run an append query to put it in the table.
I agree it is easier to use a table, but you can fill it as shown in the code, in MS-Access.
 
Is the form open and in form view, when you call it from another function?


Yes the form is open,

I have a button on a form that calls a function and as part of that function called I want to fill the listbox on the form then while processing the rest of code I can update the list box to show completed at each step.

I found this code, again works called directly from a form button

Code:
 With Form_Main.JobStatus
      .Selected(2) = True
      .AddItem "-REPLACED-;Complete", .ListIndex
      .Selected(3) = True
      .RemoveItem .ListIndex 
End With

This is just an example I would turn this into a function where you I passed the string value of column 1 data that you wanted to update
 
Last edited:
Show all the code you've for filling the listbox.
 
NO code is needed to do this. just queries.
 
Think I will use the a table and link listbox to that and then just update table.


If I do it this way how do I get the listbox to refresh to show the changes in the table


I was hoping it was going to be easy using just plain code to create and update listbox like it is in excel, vb





Cheers guys
 
Last edited:
If the changes to the source table are made on the form you are on then simply requery the form or listbox, after you have made the changes to the data.

If not then you may want to put a timer event to requery the listbox periodically.
 
I can't get this to error unless the form is in design view.

Code:
Public Function FillStatusList()
    With Form_Main
        .Visible = True
        If .CurrentView = 0 Then
            MsgBox "can't add values in design view"
            Exit Function
        End If
        
        With .JobStatus
            .ColumnCount = 2
            .RowSourceType = "Value List"
            .RowSource = ""
            .AddItem ("Loading Main WORKBOOK File;")
            .AddItem ("Loading Latest Cut Off File;")
            .AddItem ("Copy Data From Latest Orders File;")
            .AddItem ("Pasting Data into Main Workbook File;")
    
            'repl col2
            For i = 0 To .ListCount - 1
                s = .Column(0, i)
                .RemoveItem i
                .AddItem s & ";" & i, i
            Next

        End With
    End With
End Function

Seems a strange way of doing it anyway. Why not just add it to the open event?

Code:
Private Sub Form_Open(Cancel As Integer)
    FillStatusList
End Sub

Private Function FillStatusList()
    With JobStatus
        .ColumnCount = 2
        .RowSourceType = "Value List"
        .RowSource = ""
        .AddItem ("Loading Main WORKBOOK File;")
        .AddItem ("Loading Latest Cut Off File;")
        .AddItem ("Copy Data From Latest Orders File;")
        .AddItem ("Pasting Data into Main Workbook File;")

        'repl col2
        For i = 0 To .ListCount - 1
            s = .Column(0, i)
            .RemoveItem i
            .AddItem s & ";" & i, i
        Next
    End With
End Function
 

Users who are viewing this thread

Back
Top Bottom