Data from userform only submitting to 2 columns?

[jacob1234]

Hi All,

Hopefully somebody can help me with this one, I have a userform that is submitting data to the next empty row of columns "T:AB" but the code is only submitting data to columns T and U, could anyone please help me to find out why?


Thanks in advance


Jacob.

Private Sub CommandButton7_Click()
Dim LastRow1 As Long, lngIndex1 As Long, ws As Worksheet

    
    
        
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    With OutMail
        .To = ComboBox7.Value
        .CC = ""
        .BCC = ""
        .Subject = "CAOOS" & " " & MonthView1.Value & " " & "Unique ID Number:" & " " & Label4.Caption
        .HTMLBody = "Hi" & " " & ComboBox7.Value & "<br>" & "<br>" & "You have a new Customer Acceptance form to fill out." & "<br>" & "<br>" & "This has been submitted by:" & " " & Label1.Caption & "<br>" & "<br>" & "To view new submission" & " " & "<A HREF=""file://" & ActiveWorkbook.FullName & """>Click Here</A>" & "<br>" & "<br>" & "<br>" & "<br>" & "Unique ID Number:" & " " & Label4.Caption
  
        
    .Send   'or use .Display
        
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    
    MsgBox "This row has now been updated", vbOKOnly, "Success"
    
        End With
        
        
        
        
    LastRow1 = Range("S" & Rows.Count).End(xlUp).Row
    lngIndex1 = Me.ListBox3.ListIndex
    Set ws = Sheets("DATABASE")
    
    
    ws.Range("T" & LastRow1).Cells(lngIndex1 + 1) = UserForm1.TextBox9.Value
    ws.Range("U" & LastRow1).Cells(lngIndex1 + 1) = UserForm1.MonthView2.Value
    ws.Range("V" & LastRow1).Cells(lngIndex1 + 1) = UserForm1.TextBox10.Value
    ws.Range("W" & LastRow1).Cells(lngIndex1 + 1) = UserForm1.TextBox14.Value
    ws.Range("X" & LastRow1).Cells(lngIndex1 + 1) = UserForm1.TextBox15.Value
    ws.Range("Y" & LastRow1).Cells(lngIndex1 + 1) = UserForm1.TextBox16.Value
    ws.Range("Z" & LastRow1).Cells(lngIndex1 + 1) = UserForm1.TextBox17.Value
    ws.Range("AA" & LastRow1).Cells(lngIndex1 + 1) = UserForm1.ComboBox6.Value
    ws.Range("AB" & LastRow1).Cells(lngIndex1 + 1) = UserForm1.TextBox18.Value
        
    
    UserForm1.Hide
    
    
    
    
End Sub

 

[jacob1234]

I am a little confused by your construction... why are you establishing a Range (column letter followed by LastRow) and then specifying those Cell offsets from it... is that really what you want to do? What cells are you actually trying to put values into?

My aim is for users to select a row from a listbox3 that has already been inputted into the sheet and basically add another column of data into that listbox. So for data I enter into Userform1.textbox9 I want it to submit to the row I have selected in the listbox and column T which currently has no data? - trying to submit values to columns T,U,V,W,X,Y,Z,AA,AB

Thanks Again

Jacob

 

[Rick Rothstein]

My aim is for users to select a row from a listbox3 that has already been inputted into the sheet and basically add another column of data into that listbox. So for data I enter into Userform1.textbox9 I want it to submit to the row I have selected in the listbox and column T which currently has no data? - trying to submit values to columns T,U,V,W,X,Y,Z,AA,AB

But which row... LastRow or lngIndex1+1?

 

[jacob1234]

But which row... LastRow or lngIndex1+1?

lngIndex+1 which is the listindex of listbox3 ?

 

[Rick Rothstein]

lngIndex+1 which is the listindex of listbox3 ?

Then I am thinking the code snippet you posted should probably look like this instead...

LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
lngIndex = ListBox3.ListIndex
Set ws = Sheets("DATABASE")

ws.Cells(lngIndex + 1, "T") = UserForm1.TextBox9.Value
ws.Cells(lngIndex + 1, "U") = UserForm1.MonthView2.Value
ws.Cells(lngIndex + 1, "V") = UserForm1.TextBox10.Value
ws.Cells(lngIndex + 1, "W") = UserForm1.TextBox14.Value
ws.Cells(lngIndex + 1, "X") = UserForm1.TextBox15.Value
ws.Cells(lngIndex + 1, "Y") = UserForm1.TextBox16.Value
ws.Cells(lngIndex + 1, "Z") = UserForm1.TextBox17.Value
ws.Cells(lngIndex + 1, "AA") = UserForm1.ComboBox6.Value
ws.Cells(lngIndex + 1, "AB") = UserForm1.TextBox18.Value

 

[jacob1234]

Then I am thinking the code snippet you posted should probably look like this instead...

LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
lngIndex = ListBox3.ListIndex
Set ws = Sheets("DATABASE")

ws.Cells(lngIndex + 1, "T") = UserForm1.TextBox9.Value
ws.Cells(lngIndex + 1, "U") = UserForm1.MonthView2.Value
ws.Cells(lngIndex + 1, "V") = UserForm1.TextBox10.Value
ws.Cells(lngIndex + 1, "W") = UserForm1.TextBox14.Value
ws.Cells(lngIndex + 1, "X") = UserForm1.TextBox15.Value
ws.Cells(lngIndex + 1, "Y") = UserForm1.TextBox16.Value
ws.Cells(lngIndex + 1, "Z") = UserForm1.TextBox17.Value
ws.Cells(lngIndex + 1, "AA") = UserForm1.ComboBox6.Value
ws.Cells(lngIndex + 1, "AB") = UserForm1.TextBox18.Value

Thanks Rick - I have tried this code but it isn't working correctly. The code puts data only into columns T and U and submits to the row above the listbox selected row?

 

[jacob1234]

Thanks Rick - I have tried this code but it isn't working correctly. The code puts data only into columns T and U and submits to the row above the listbox selected row?

Hi All, hoping to get this problem solved today - any more ideas?

Thanks for all the help so far!