asked on

How do I update an Excel ListBox form control in VBA

Hi,

I have a Listbox form control on an Excel sheet which I want to update automatically when the user moves to the sheet. Items will be added to the list from a range of dates that are held on another sheet (called Tables).

However, I only want to display two dates in the list, and they will be the first two dates in the range it finds that are greater than TODAY'S date.

I'm using a For Next loop to go through the range of dates, and using ListBox1.AddItem to display the 2 dates BUT if I change the dates in the range, the listbox items don't change to display the new dates. I would have expected the list items to change to reflect the new dates in the range.

I've attached my procedure code for reference.

Thanks
Toco

Private Sub Worksheet_Activate

'This should update the list every time the user moves to the sheet

For Row = 2 to 20
   If Sheets("Tables").Cells(Row, 10) > Date Then
      ListBox1.Addition Sheets("Tables").Cells(Row, 10)
      ListBox1.Addition Sheets("Tables").Cells(Row + 1, 10)
      Row = 99   '(Yes, n ugly way of exiting a loop !!)
   End If
Next Row

End Sub

Open in new window

dlmille

First, your syntax should be lb.AddItem to add to the listbox.

You can use Exit For to exit your for loop.

Just to be clear, is this a forms or an activeX control that we're dealing with?

Dave

dlmille

I do so much with Active-X controls, that I had to revisit some documentation and build a test case before giving you any sample reframing of your code.

Here's the code I would use, with a sample spreadsheet:

Private Sub Worksheet_Activate()
Dim lb As Object

    'This should update the list every time the user moves to the sheet
    
    Set lb = ActiveSheet.ListBoxes("List Box 1")
    lb.RemoveAllItems
    For Row = 2 To 20
       If Sheets("Tables").Cells(Row, 10).Value > Date Then
          lb.AddItem (Sheets("Tables").Cells(Row, 10).Value)
          lb.AddItem (Sheets("Tables").Cells(Row + 1, 10).Value)
          Exit For
       End If
    Next Row

End Sub

Open in new window

First, I assign the variable lb the value of the ListBox1 forms object on the spreadsheet
Then, existing items are removed
Finally, in your loop, the 2 dates are added, with an Exit For on that condition.

See attached.

Enjoy!

Dave
MS-Forms-LB-Add-r1.xls

Tocogroup

ASKER

Hi Dave,

Sorry, yes, I do have AddItem (and not Addition as above). However, when I replace ListBox1. with lb. then I get a runtime error.

By the way, it's an Active X form control.

Toco

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tocogroup

ASKER

Fantastic ! Works a treat. Yes, I use the term 'forms controls' rather loosely. Ignorance on my behalf.
Thanks for that. It seems it was the ListBox1.Clear I was missing.
Thanks again
Toco