Count the selected rows from a Listbox

otrava18

Board Regular
Joined
Feb 11, 2018
Messages
52
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.

VBA Code:
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 !
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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

Board Regular
Joined
Feb 11, 2018
Messages
52
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
VBA Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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.

VBA Code:
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,002
Messages
5,545,440
Members
410,684
Latest member
LakTik
Top