holding value in listbox on a userform

[rickblunt]

Greetings - Can anyone confirm for me when the values placed in a listbox on a userform are removed? I always thought it would be whenever the userform was closed (or perhaps reinitialized), and that as long as you kept it open the value in that listbox would not be null. I am using the code below to transfer data from a userform to two different WS's and I am pretty sure it is correct. It works perfectly on the "Device List" section , but as soon as it moves to the "Repair Log" portion it fails to find the correct row for the data to be entered, and places the data in the first blank row.

I tracked it down to the listbox value becoming "null" after it executes the Device List portion of the code. While I do close and reopen the userform much later in the code to refresh all of the data on the userform, I haven't done that at this point in the code so I do not understand why I am losing the value in the DeviceId listbox. (Removing the code to close and reopen the userform did not correct the issue.)

Does anyone have any thoughts on this - or is it this not the way it works? I appreciate the input.

Private Sub CommentButton_Click()

    Dim i As Long
    Dim Lastrow As Long
    Dim Found As Range
          
    Worksheets("Device List").Activate
    
    Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    
        For i = 1 To Lastrow
            If Cells(i, 8).Value = "FAIL" And Cells(i, 7).Value = "" Then
            Cells(i, 7).Value = RepairComments.Value
                
    'Sheets("Repair Log").Activate        
        
       Set Found = Range("'Repair Log'!A:A").Find(DeviceId.Value, , xlValues, xlWhole)
    If Found Is Nothing Then
        MsgBox "No match for " & DeviceId.Value, , "No Match Found"
    ElseIf Found(1, 3).Text = "" Then
        Found(1, 3).Value = RepairComments.Value
    Else
        MsgBox "No match for " & DeviceId.Value, , "No Match Found"
    End If
    
    Exit For
    End If
    Next
' and a lot more code after this - later on in this code I do reinitialize this userform to update the data in it.....

 

[GWteB]

Can anyone confirm for me when the values placed in a listbox on a userform are removed?

- when its Clear method is invoked;
- when its RemoveItem method is invoked on all its rows;
- when the Userform has been terminated: either closed by the user using the top right X close button or unloaded by the process which loaded the userform.

as long as you kept it open the value in that listbox would not be null.

A Listbox's Value property cannot be used (and always will be Null) when its MultiSelect property is set to frmMultiSelectMulti (=1) or frmMultiSelectExtended (=2).

Hopefully this helps.

 

[rickblunt]

- when its Clear method is invoked;
- when its RemoveItem method is invoked on all its rows;
- when the Userform has been terminated: either closed by the user using the top right X close button or unloaded by the process which loaded the userform.


A Listbox's Value property cannot be used (and always will be Null) when its MultiSelect property is set to frmMultiSelectMulti (=1) or frmMultiSelectExtended (=2).

Hopefully this helps.

Thank you for the knowledge - it very helpful . That was also good information about the value properties - I have mine set to fmMltiSelectSingle,