Link the ListCount to a Label automatically.

[Joe]

Happy New Year to all....

I have a listbox(Listbox1) and a label (Label1).

I want to display the count in the label as in code..
Label1.caption = "List Count : " & Listbox1.ListCount

But I am adding and deleting the contents in different places in the
code.
Can there be way to Link that deynamically so that whenever the
ListCount is changed, the Label1 will get updated.

I even tried to put that code in the Sub Listbox1_Change()
But its not conclusive. Some operations it will be Updated, but
sometimes Not.

I also tried the event - Sub ListBin_AfterUpdate(), but thats the same
situation.

Can anyone help?


Thanks & Regards
Joe

 

[Barb Reinhardt]

I've not used Lists at all, but I suspect you could use a worksheet change
event to do what you want.

Here is some code that might be useful. You need to go to the sheet that
the list is on, click on the worksheet tab and select View Code. Then paste
it in.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myListObject As ListObject
Dim myRange As Range

For Each myListObject In Me.ListObjects

If Not Intersect(Target, myListObject.Range) Is Nothing Then
MsgBox ("You have changed a value in the list " & myListObject.Name)
End If

For Each myRange In myListObject.Range
Debug.Print myListObject.Name, myRange.Address
Next myRange
Next myListObject
End Sub

 

[Joe]

I've not used Lists at all, but I suspect you could use a worksheet change
event to do what you want.  

Here is some code that might be useful.  You need to go to the sheet that
the list is on, click on the worksheet tab and select View Code.  Then paste
it in.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myListObject As ListObject
Dim myRange As Range

For Each myListObject In Me.ListObjects

    If Not Intersect(Target, myListObject.Range) Is Nothing Then
        MsgBox ("You have changed a value in the list " & myListObject.Name)
    End If

    For Each myRange In myListObject.Range
        Debug.Print myListObject.Name, myRange.Address
    Next myRange
Next myListObject
End Sub

--
HTH,
Barb Reinhardt












- Show quoted text -

Thanks Barb ...

I have not understood it fully...
But what I understood is...
The ListBox is somehow linked to a worksheet range.. (i dont know
how)..
and you are checking if there is a change in that Range and thus its
implied that there is a change in the ListBox

It sounds nice but for my application I can not adopt that...