• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 789
  • Last Modified:

ListBox in excel vba

I have a listbox in my code in which I am trying to select an item with vba to set a varaible equal to that selection.  I get a null value.  The listbox is popluated and code does select that appropriate item, but still I get null value.

Code:
    Dim ItemCount As Integer: ItemCount = Group1_List.ListCount
    Dim CurrentItem As String
   
    If Group1_List.ListCount <> 0 Then
        For ListItem = 0 To ItemCount - 1
            Group1_List.Selected(ListItem) = True
            CurrentItem = Group1_List.Value
                                   
        Next ListItem
    End If
0
rckrch
Asked:
rckrch
  • 2
1 Solution
 
Jeff DarlingDeveloper AnalystCommented:
I think you want to test to see if an item is selected.

The line of code Me.ListBox1.Selected(ListItem) = True selects the value, I do not think that you want that to happen.



Dim CurrentItem As String

Dim bSelected As Boolean
bSelected = False
    
    If Me.ListBox1.ListCount <> 0 Then
        For ListItem = 0 To ItemCount - 1
            If Me.ListBox1.Selected(ListItem) = True Then
              CurrentItem = Me.ListBox1.Value
              bSelected = True
            End If
        Next ListItem
    End If

If bSelected Then
  MsgBox CurrentItem
End If

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
You can just use Group1_List.list(Group1_List.listindex)
0
 
rckrchAuthor Commented:
Thanks.  This worked perfectly.
0
 
Martin LissRetired ProgrammerCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now