Is refreshing listbox rowsource in listbox click event possible?

[Jeremy Gollehon]

Explanation:
The source data the listbox contains is in columns A, B and C of Sheet1. I
use the rowsource property so the listbox list can have headers.
"WorkList" is a dynamically updating named range. When a selection is made
in Listbox1, the selection is moved to the top of the list on Sheet1 and the
rest of the list is sorted. Everything works great to that point, the
problem is that when I reset the RowSource property, the Listbox1 list
doesn't get updated. I assume this is because it is from Listbox1's click
event, as it works if I do the same thing from another procedure. Am I
doing something wrong, or is there possibly another way to accomplish this?

Thanks!
-Jeremy

----------------------------------------------------------------------------
Private Sub Listbox1_Click()
Dim rng As Range

If DisableFormEvents Then Exit Sub

With Worksheets("Sheet1")
Set rng = .Columns(1).Find(What:=Me.Listbox1, LookIn:=xlValues)
Range(rng, rng(1, 3)).Cut
.Range("A2").Insert Shift:=xlDown
With Range(.Range("A3"), .Cells(.Rows.Count, 26).End(xlUp))
.Sort Key1:=Range("A3"), Order1:=xlAscending,
End With
DisableFormEvents = True
Me.Listbox1.RowSource = _
.Names("WorkList").RefersToRange.Address(External:=True)
DisableFormEvents = False
End With

End Sub
----------------------------------------------------------------------------

 

[jim c.]

I have a project with a similar process.

I "think" the problem is the Exit Event hasn't occured for
your listbox. I notice that changes to my table visually
inside my listbox arn't reflected until focus is passed to
another control, but if a change is made to the table
outside the listbox and the rowsource is reset everything
works fine.

to test this, after your last line of code in your listbox
pass focus to another control...

ie: TextBox1.SetFocus

If i'm incorrect, i would love to know why also. I fight
this often.

 

[jim c.]

forgot to mention that my listbox is on a userform. not
sure if the same problem applies to one on the worksheet.

i'll try it and post back...

 

[jim c.]

I tested on a userform using 2 ranges "test" & "test1"

used the line of code:

Private Sub ListBox1_AfterUpdate()
If ListBox1.RowSource = "test1" Then
ListBox1.RowSource = "test"
End If
End Sub


this would only work properly AFTER the change event
so I guess my previous post was a mistake...
Hope this helps...

 

[Jeremy Gollehon]

Hi Jim. I actually sent this yesterday at 9:55 AM, but it never got posted.
My listbox is also on a userform, and I got it! I had been playing with the
AfterUpdate event after I made my post, and your right, its the only way to
get the list to update, but the problem is that it creates a continuous loop
as the AfterUpdate event fires again and again when the Rowsource is
changed.

Using your example, here's how I got around the loop.

----------------------------------------------------------------------------
Public DisableAfterUpdate as Boolean
Public DisableFormEvents as Boolean

Private Sub Listbox1_AfterUpdate()

If DisableAfterUpdate Then Exit Sub
DisableAfterUpdate = True
DisableFormEvents = True
With Userform1.Listbox1
If .RowSource = Worksheets(1).Names("Test1") _
.RefersToRange.Address(External:=True) Then
.RowSource = _
Worksheets(1).Names("Test").RefersToRange.Address(External:=True)
End If
End With
DisableFormEvents = False

End Sub

Private Sub Listbox1_Click()

If DisableFormEvents Then Exit Sub
DisableAfterUpdate = False

'Code that changes "Test" and/or "Test1"

End Sub
----------------------------------------------------------------------------