VBA newbie -- updating listbox AND worksheet

[Panda XXIV]

hi, I'm a newbie with excel VBA. I'm building a multi page userform and thanks to this forum and several other forums I was able to arrive at this point.


But here's the catch:


>> Update button only updates columns on the resultbox. I tired modifying the code that populates worksheet on page1, and tried other codes from different threads but I still cant get it to work.


can you guys enlighten me, or drop me a code for this?


update button should also update columns G,H,I of worksheet if "PASS", and update G,H,I together with 2 uploaded images in columns J & K if selection is "FAIL"


also i've set the resultbox into multiselect, Im looking for the possibility to update multiple selections at once instead of ticking "PASS" and updating them one by one... current script enables the user to select multiple BUT only updates 1 selection.. No need for this if item is "FAILED" since I'm requiring user to upload pics if thats the case..




no codes yet for the Controls section on page 2 will seek help again from you guys if I can't work 'em out


link for the workbook is https://drive.google.com/file/d/16oMvCLEErFhQrM3X6MFiI2ujE8x6qFP-/view?usp=drivesdk
you guys are free to dissect it as you wish. lol
The worksheet contains dummy data so no need to worry bout the contents.
Also, forgive me for the messy codes. I've no idea with regards to coding aesthetics yet heheheh.

 

[Panda XXIV]

Updating thread qith the codes used.. hope somebody helps hehehe

Private Sub UserForm_Initialize()

    datelabel.value = Format(Now(), "mm/dd/yyyy ")
    usernamelabel = Environ("username")
    xrayuser = Environ("username")
    


End Sub
Private Sub AddItem_Click()
Dim X As String
X = ListBox1.ListCount


   
    If ESNInput.value = "" Then
            MsgBox "Please enter ESN", vbOKOnly + vbExclamation, "ESN"
                Exit Sub
        Else
            
            If SKUInput.value = "" Then
                MsgBox "Please enter SKU", vbOKOnly + vbExclamation, "SKU"
                 Exit Sub
        Else
            
    If shiftLabel.value = "" Then
            MsgBox "Please specify your Shift", vbOKOnly + vbExclamation, "SHIFT"
                Exit Sub
        Else
    
    If LiNEnumber.value = "" Then
            MsgBox "Please select Line Number", vbOKOnly + vbExclamation, "LINE"
                 Exit Sub
    Else
    
        With ListBox1
             .AddItem
             .List(X, 0) = datelabel.value
             .List(X, 1) = shiftLabel.value
             .List(X, 2) = ESNInput.value
             .List(X, 3) = SKUInput.value
             .List(X, 4) = LiNEnumber.value
             .List(X, 5) = usernamelabel.value
             
        End With




                ESNInput.value = ""
                SKUInput.value = ""
                LiNEnumber.value = ""


    End If
    End If
    End If
    End If
    
    


End Sub


Sub DeleteItem_Click()
    
    ListBox1.SetFocus
 
        If ListBox1.ListCount >= 1 Then
            If ListBox1.ListIndex = -1 Then
                ListBox1.ListIndex = ListBox1.ListCount - 1
        End If
            ListBox1.RemoveItem (ListBox1.ListIndex)
        End If
    
End Sub
Private Sub Submitbutton_Click()


    Dim ws As Worksheet
    Dim nextAvailableRow As Long
    Dim i As Long
    Dim notify As Integer
    
            notify = MsgBox("Notify X-Ray Personnel?", vbOKCancel + vbExclamation, "Submit Samples")
            
            If notify = 1 Then
            
            Set ws = Sheets("RawDATA")




            For i = 0 To ListBox1.ListCount - 1
            nextAvailableRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
            ws.Range("A" & nextAvailableRow) = ListBox1.Column(0, i)
            ws.Range("b" & nextAvailableRow) = ListBox1.Column(1, i)
            ws.Range("c" & nextAvailableRow) = ListBox1.Column(2, i)
            ws.Range("d" & nextAvailableRow) = ListBox1.Column(3, i)
            ws.Range("e" & nextAvailableRow) = ListBox1.Column(4, i)
            ws.Range("f" & nextAvailableRow) = ListBox1.Column(5, i)


            Next i


                ListBox1.Clear
                ActiveWorkbook.Save
                
            Else
            
            Exit Sub
            
            '---insert create & send email code here---'
            
            End If
            
    
End Sub


Private Sub filter1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    filter1.value = Format(CalendarForm.GetDate, "mm/dd/yyyy")
End Sub


Private Sub loadbutton_click()


    Dim crit1 As String
    Dim crit2 As String
    Dim ws As Worksheet


    Set ws = Sheets("rawdata")
    crit1 = filter1.value
    crit2 = filter2.value


    ws.Range("r1") = crit1
    ws.Range("s1") = crit2


        ws.Columns("A:J").AdvancedFilter Action:=xlFilterInPlace, criteriaRange:=Range _
        ("rawdata!criteria"), unique:=False


    Dim result As Range
    Dim c1 As Range
    Dim LR As Long


    LR = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set result = ws.Range("A2:A" & LR).SpecialCells(xlCellTypeVisible)


    
        With Me.ResultBox
            .Clear
            .ColumnWidths = (50)
            
                For Each c1 In result


                    .AddItem (c1.value)
                    .List(.ListCount - 1, 1) = c1.Offset(0, 1).value
                    .List(.ListCount - 1, 2) = c1.Offset(0, 2).value
                    .List(.ListCount - 1, 3) = c1.Offset(0, 3).value
                    .List(.ListCount - 1, 4) = c1.Offset(0, 4).value
                    .List(.ListCount - 1, 5) = c1.Offset(0, 5).value
                    .List(.ListCount - 1, 6) = c1.Offset(0, 6).value
                    .List(.ListCount - 1, 7) = c1.Offset(0, 7).value
                    .List(.ListCount - 1, 8) = c1.Offset(0, 8).value
                
                Next


        End With




End Sub
Private Sub UpdateBTN_Click()
 
 
     If PASSopt.value = True Then
     
        With ResultBox
            .AddItem
            .List(.ListIndex, 7) = "PASS"
            .List(.ListIndex, 8) = xrayuser.value
        End With
        
            PASSopt.value = False
            
            '-----code to update corresponding column in worksheet here---'
            
            
            ActiveWorkbook.Save
            
     Else
        
        
   If FAILOpt.value = True Then
        
        If Faildetails.value = "" Then
        MsgBox "Please specify failure details.", vbOKOnly + vbExclamation, "REMARKS"
        Exit Sub
        
        
        Else
        
            With ResultBox
            .AddItem
            .List(.ListIndex, 7) = "FAIL"
            .List(.ListIndex, 8) = xrayuser.value
            .List(.ListIndex, 9) = Faildetails.value
            
        MsgBox "Please Upload Failed Image", vbOKOnly + vbExclamation, "Fail Preview"
            
            
        Dim failimage
        failimage = Application.GetOpenFilename(ImgFileFormat)
        Frame2.Picture = LoadPicture(failimage)


        MsgBox "Please Upload Good Reference", vbOKOnly + vbExclamation, "Good Sample"
            
            
        Dim goodimage
        goodimage = Application.GetOpenFilename(ImgFileFormat)
        Frame3.Picture = LoadPicture(goodimage)
        
                         
        End With
            
            Faildetails.value = ""
            FAILOpt.value = False
            
            '-----code to update corresponding column in worksheet here---'
            '-------code to save uploaded photo on its column here--------'
            
            
            ActiveWorkbook.Save
            
    End If
    End If
    End If
    




End Sub