Avatar of Tocogroup
Tocogroup
Flag for United Kingdom of Great Britain and Northern Ireland 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

Microsoft Excel

Avatar of undefined
Last Comment
Tocogroup

8/22/2022 - Mon
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
dlmille

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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