Updating A Worksheet Embedded ActiveX ListBox

[Ark68]

I have an ActiveX listbox (listbox1) on my worksheet ("FRONT"). I am creating the dynamic list source from values placed in worksheet "LISTS", starting at cell A2. As values are added to column A, the listbox needs to update. My goal, and I'm not sure it's possible, is to have the most recent addition to the list at the top of the listbox, with the older entries shifting down as new ones are added. The most recent event will always be inserted at cell A2 of worksheet "LISTS" (previous values shifting down). and the listbox updated hopefully with the same structure as the range in column A.

I have created this function ...

Function fn_updateaxlb(emsg As String)
    With ws_lists
        .Range("A2").Insert Shift:=xlDown
        .Range("A2") = emsg
    End With
    ws_front.ListBox1.ListFillRange = ws_lists.Range("A2:A100")
End Function

emsg is a string value representing the value that will be added to the dynamic list and ultimately the listbox.

I have no idea how to update the listbox. My guess is resulting in a "Method or data member not found error" in object highlighted in red.

 

[DanteAmor]

ws_front.ListBox1.ListFillRange = ws_lists.Range("A2:A100")

Check that the name of the listbox is ListBox1 and that it exists in the "ws_front" sheet

After review and that the name and sheet are correct. Use the statement like this:

  ws_front.ListBox1.ListFillRange = ws_lists.Range("A2:A100").Address(external:=True)

Or better yet:

  ws_front.ListBox1.ListFillRange = ws_lists.Range("A2", ws_lists.Range("A" & Rows.Count).End(3)).Address(external:=True)

 

[Ark68]

Thank you Dante.
I used your second solution with success, but I had to exchange "ws_front" with "Worksheets("FRONT") for it to work.

"ws_front" is publicly declared and set in what I call an initialization module executed at the start of the application. I put a stop in my code (below) and used the immediate box to check check the name of ws_front. It came back as "FRONT", which is correct.

Function fn_updateaxlb(emsg As String)
    With ws_lists
        .Range("A2").Insert Shift:=xlDown
        .Range("A2") = emsg
    End With
    Stop
    ws_front.ListBox1.ListFillRange = ws_lists.Range("A2", ws_lists.Range("A" & Rows.Count).End(3)).Address(external:=True)
End Function

This code, although ws_front is properly recognized throws the error with ListBox1. However, as mentioned, changing ws_front to "Worksheets("FRONT") will not throw the error. ANy explanation as to why this might be happening?

 

[DanteAmor]

"ws_front" is publicly declared and set in what I call an initialization module executed at the start of the application.

I don't know how you have declared the variable. These are details that you must share, otherwise I will be guessing.

If you have ws_front declared as type Worksheet, then change to Object, for example:

Public ws_front As Object

Try again.

 

[Ark68]

Yes, I had it declared as a worksheet, and redeclaring it to object worked.
Are there circumstances why one would want to declare a worksheet as an object? I've never had to do this before. Is it because of the ActiveX control I put on it?

 

[DanteAmor]

I've never had to do this before. Is it because of the ActiveX control I put on it?

There are some objects that are not recognized in a worksheet declaration, so you use Object.

But you can also use the following.
You declare as Worksheet:

Public ws_front As Worksheet

In the function:

Function fn_updateaxlb(emsg As String)
  With ws_lists
    .Range("A2").Insert Shift:=xlDown
    .Range("A2") = emsg
  End With
  
  ws_front.OLEObjects("Listbox1").ListFillRange = ws_lists.Range("A2", ws_lists.Range("A" & Rows.Count).End(3)).Address(external:=True)
End Function

 

[Ark68]

Ah, OK. That was a helpful explanation. Thanks.