Value from listbox on form, in vba.

[redbull]

I have a listbox on my form, it has a list of items. The user is able to select one of the items..

I am having a hard time obtaining the value of the selection within the VBA code, keeps coming up "" despite having a selection made.



The listbox name is "lstEmp"

The form name is "frmMainnew"

the code is in a module, not behind the form.

I have tried about a million iterations of this ..

Forms!frmMainnew!lstEmp.Value
Forms.frmMainnew.lstEmp.Value

However everything is coming up "", OR i get "Invalid use of null" despite having something selected in the listbox. .. I am fairly certain this is an ID10.T error, so maybe a second set of eyes will help.

[pbaldy]

Is one of the multi-select options on? A multi-select listbox is always Null.

[redbull]

Yea, I have the multi-select set to simple

[redbull]

Interesting, I changed the listbox so multi select was off. Then ran my code. and it is coming back with the "value" meaning the number in the list.. like if the 3rd thing down is selected it returns with a value of 3.

I am wondering if a select box is not the best approach to this. However, with 40-50 names in the list... and I NEED to be able to select more then one... I don't know what else to use. an entire form filled with checkboxes does not seem appealing.

[pbaldy]

The number should be the value of the bound column, whatever it is (typically an ID field). You have to loop a multi-select listbox to get the selections. Not sure what the end goal is, but here's an example of the loop:

http://www.baldyweb.com/multiselect.htm

or

http://support.microsoft.com/default...b;en-us;210203

[redbull]

While the listbox is set to not allow multi select this is giving the index number correctly.

Dim i As Variant


With Forms!frmMainnew.lstEmp
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            MsgBox .Value
            Exit Sub
        End If
    Next
End With

The content on this listbox currently is around 100 users, A manager will select multiple employees to run a process against (report, sub,query, etc etc)

The end goal is to have a multi select listbox that will cycle thru, and give me the names (via msgbox or whatever) that are selected in the listbox. Not the index numbers.

[pbaldy]

You can get a different column with:

.Column(0, i)

[redbull]

its still not returning the name in the list, just the number of the selection. Can the literal text be returned?

[pbaldy]

I should have specified, but did you change the column from 0 to the appropriate column?

[redbull]

With Forms!frmMainnew.lstAnalyst
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            MsgBox .Column(1, i)
            Exit Sub
        End If
    Next
End With

OH HAPPY DAY!!!!!

Thank you, I did not even think of that. It WAS giving me what I wanted, however it was on the wrong column so it was dishing out the ID instead of what I wanted. Yikes, lol...

ID10T error hard at work.

[redbull]

Tried giving you rep, however I was told I need to spread it around to other people before giving you anymore.

[pbaldy]

Happy to help! By the way, the loop in my link is a little more efficient, as it only loops selected items rather than the whole listbox. Not sure you'd see a difference though, so I'd probably leave it alone.

[redbull]

Pbaldy, I figured you might get a kick out of this.

Sub CyclebothLists()
Dim Clr As InternetExplorer
Dim User, Pass As String
Dim oCell As Object
Dim LstEmpSelect As String
Dim i As Variant
Dim Name As Integer
Dim CurrentList As ListBox


Set Clr = FindIE("https://website")


'Loops next list item name
  For Name = 0 To Forms!frmMainnew.lstAnalyst.ListCount - 1
  If Forms!frmMainnew.lstAnalyst.Selected(Name) Then
  PauseApp 3


    Do Until InStr(Clr.Document.Body.innertext, "Adjustment Views") > 0 'checks screen placement
    Call SleepIE(Clr)
    PauseApp 3
    Loop  ' end of screen check


      With Clr.Document.all
      i = 0


      For Each oCell In .tags("td")
      If oCell.innertext = Forms!frmMainnew.lstAnalyst.Column(1, Name) Then  ' TD loop IF
        .tags("td").Item(i - 2).all.Item("manageTimesheet").Click
          Call SleepIE(Clr)
            PauseApp 3
              Clr.Navigate "javascript:submitForm('page','timeadmin.populateTimesheet');"
                 PauseApp 4
              Call SleepIE(Clr)
            Call eights
          Clr.Document.all.Item("timeadmin.timesheetBrowserReturn").Click
        Call SleepIE(Clr)
      PauseApp 2


      Exit For
      End If   ' End of TD loop
      i = i + 1
      Next oCell
      End With
  
  End If
  Call SleepIE(Clr)
  Next Name
  Set oCell = Nothing
  
 ' MsgBox "Complete", , "LIFT MESSAGE"






End Sub

It is looping thru the list box, once it hits a selected item... it interrogates the website (all table data and rows) What do you think?

edit --

and its working flawlessly

[pbaldy]

Pretty cool; other than automated downloads of files, I haven't really worked with code to automate IE.