CODEWITHSUNDEEP
excelvba
Strexxin
Strexxin

Reputation: 29

Submit listbox data to sheet

I wrote this code a few months ago (with the help of folks here) to handle data submission to an Excel sheet.

Today 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 data in the listbox, nothing is submitted to the sheet.

The only thing that has changed from my end is my work environment has recently been moved to SharePoint.

This is the entire code.

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

Upvotes: 0

Views: 98

Answers (1)

chris neilsen
chris neilsen

Reputation: 53137

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

Upvotes: 1

Related Questions