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