Count the selected rows from a Listbox

[otrava18]

Hello all,

I have an issue and i searched all day a solution but i didn`t find it. I have a List box with multiple selection and it has 6 rows (it`s fixed). I want to do something if the list box has no selection (i want to hide a shape). I find on this forum a code but for me is not working because if i select one row i need to hit enter to unhide that shape. For unhide is the same, i unselect all rows and i need to hit enter to hide the shape. I nee a solution where i don`t need to hit enter.

Dim X As Long, FoundOne As Boolean

For X = 0 To ListBox1.ListCount

  If ListBox1.Selected(X) Then

    FoundOne = True

    Exit For

  End If

Next

If FoundOne Then

  Worksheets("Sheet1").Shapes("Picture1").Visible = False

Else

  Worksheets("Sheet1").Shapes("Picture1").Visible = True

End If

This code is working only after i hit enter. This code is not mine is from Rick Rothstein

Thank you all !

 

[DanteAmor]

You could comment on the following:
- How do you execute the code? you did not put the complete code and I cannot know how you execute it.
- The listbox is in a userform?
- The listbox is on the sheet, what type is it, form or activeX?

 

[otrava18]

Hello DanteAmor,

- How do you execute the code? you did not put the complete code and I cannot know how you execute it.
I forgot only

Private Sub Worksheet_Change(ByVal Target As Range)

. Is working for me like i said.
- The listbox is in a userform?
No is not in a userform. Is an activeX ListBox
- The listbox is on the sheet, what type is it, form or activeX?
Is an activeX ListBox. Is it on the Sheet1 and it has 6 rows. (If i understand well the question)

Thank you !

 

[DanteAmor]

With the Change event it is necessary to modify a cell and press Enter, that way the event is activated and your code is executed.

So replace your code to the following.
It means that every time you click on one of the items in the listbox it will check if you selected one item or if all items are deselected.

Private Sub ListBox1_Change()
  Dim i As Long
  
  Worksheets("Sheet1").Shapes("Picture1").Visible = True
  For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
      Worksheets("Sheet1").Shapes("Picture1").Visible = False
      Exit For
    End If
  Next
End Sub

 

[otrava18]

Thank you very much DanteAmor ! You helped me again.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.