Need someone to cast an eye over this code to see what I have missed-CodePudding

I wrote this code a few months ago (with the help of folks here) to handle data submission to an Excel sheet, everything worked fine. I revisited the sheet today and found that everything still works apart the list box submission, I get the confirmation to say that the data has been input to the sheet, that is true apart from the the data in the listbox, nothing is submitted to the sheet.

This is the entire sub code. If anyone can see if anything seems to be incorrect as I have looked at the code several times and cannot see why it no longer works that would be great.

The only thing that has changed from my end is my work environment has recently been moved to Sharepoint but I am not sure how that could impact just list boxes.

Private Sub Production_Submit_Click()

    Sheets("Sales Order Log").Unprotect Password:="XstrahlSalesOrderLog"

    'Locks all fields within the form and also displays a msgbox informing the user of this if trying to submit information to the sheet within inputting a Sales Order Number
    If SalesOrderProd = "" Then
        WorkOrderNo.Locked = True
        ProdDesc.Locked = True
        ProdDate.Locked = True
        ProdComplete.Locked = True
        MsgBox "Please insert a Sales Order Number to continue", vbCritical, "Missing Sales Order Number"
        Exit Sub
    Else
        WorkOrderNo.Locked = False
        ProdDesc.Locked = False
        ProdDate.Locked = False
        ProdComplete.Locked = False
    End If

    Dim Reference As String
    Dim wkord As String

    Reference = Trim(SalesOrderProd.Text)
    LastRow = Worksheets("Sales Order Log").Cells(Rows.Count, 2).End(xlUp).Row
    For i = 2 To LastRow

        'Submits the Production information to the sheet
        If Worksheets("Sales Order Log").Cells(i, 2).Value = Reference Then
            'Worksheets("Sales Order Log").Cells(i, 10).Value = WorkOrderNo
            Worksheets("Sales Order Log").Cells(i, 13).Value = ProdDesc
            Worksheets("Sales Order Log").Cells(i, 14).Value = ProdDate
            Worksheets("Sales Order Log").Cells(i, 15).Value = ProdComplete

            If WkOrderList.ListIndex <> -1 Then
                For j = LBound(Me.WkOrderList.List) To UBound(Me.WkOrderList.List)
                    wkord = wkord & IIf(wkord = "", "", vbNewLine) & Me.WkOrderList.List(j, 0)

                    With Sheets("Sales Order Log").Range("Sales_Data_Start")
                        ' .Offset(TargetRow, 1).Value = SalesOrderNo
                        ' (...)
                        Worksheets("Sales Order Log").Cells(i, 12).Value = wkord
                    End With
                Next
            End If
        End If
    Next

    'Displays a message box informing the user that the form information has been input into the spreadsheet
    MsgBox "Information added to Sales Order: " & SalesOrderProd & " successfully", vbInformation, "Production"

    'Blanks the form once information has been submitted to the sheet
    SalesOrderProd.Value = ""
    WorkOrderNo.Value = ""
    ProdDesc.Value = ""
    ProdDate.Value = ""
    ProdComplete.Value = ""
    WkOrderList.Clear

    Sheets("Sales Order Log").Protect Password:="XstrahlSalesOrderLog"

End Sub

CodePudding user response：

If WkOrderList.ListIndex <> -1 doesn't test if the list box is empty as you suggest you expect (in a comment). It tests if something is selected in the list.

To test for not empty, use

If Me.WkOrderList.ListCount > 0