VBA to Add a row and fill data as part of 'Submitting an Order' from another Sheet

[Excel_User_10k]

Private Sub cmd_Submit_Click()
    Dim myRow As ListRow
    Dim intRows As Integer
    
    intRows = ActiveWorkbook.Worksheets("Sales MTD").ListObjects("Submitted_Sales").ListRows.Count
    Set myRow = ActiveWorkbook.Worksheets("Sales MTD").ListObjects("Submitted_Sales").ListRows.Add(intRows)
    
    myRow.Range(1) = Range("C10")
    myRow.Range(2) = Range("C3")
    myRow.Range(3) = Range("C4")
    myRow.Range(4) = Range("D4")
    myRow.Range(5) = Range("E4")
    myRow.Range(6) = Range("C5")
    myRow.Range(7) = Range("F4")
    myRow.Range(8) = Range("F5")
    myRow.Range(9) = Range("H9")
    myRow.Range(10) = Range("C6")
    myRow.Range(11) = Range("F6")
    myRow.Range(12) = Range("D3")
    myRow.Range(13) = Range("C7")
    myRow.Range(14) = Range("F7")
    myRow.Range(15) = Range("E3")
    myRow.Range(15) = Range("C8")
    myRow.Range(16) = Range("F8")
    myRow.Range(17) = Range("F3")
    myRow.Range(18) = Range("C9")
    myRow.Range(19) = Range("F9")
    myRow.Range(20) = Range("F10")
    
End Sub

I have currently got an "Order Form" on one Sheet to Submit an entry for a Sale that has a variety of Extras, Insurances, Type of Sale, etc. I have an ActiveX button that I am using with the above code so that once all of the selections are made with the help of VLOOKUP from the catalogue on a 2nd Sheet, it adds a row to a Table on the 3rd Sheet Where it automatically fills in each column with the relevant criteria in relation to the selections made in the Order Form.

Firstly, the Formula is currently working. However, I have come across two issues...

1) For some reason it is not adding a row to the bottom of the Table, but to the 2nd to bottom. (I would also be filtering through the data at times so whether that has any effect on this too I am not sure - If for instance, a row was added when all filters haven't been cleared or they aren't in the original order, will it still add a row to the bottom of the table?)
2) On the new Row that is added, the side border is cleared/missing on some of the cells randomly. This doesn't affect the functionality but it is annoying haha.

Any ideas on why it is doing this? I saw someone else use the same code (but not with as many Columns) and it added it at the bottom/end for them.

 

[Excel_User_10k]

Hi Dave, I noticed you liked it but did not reply so I am not sure if you just didn't have time to reply or only read the last part and thought it was resolved.

I am still confused over that VBA coding. I understand entering the code to specify what is allowed, but I still don't know where it goes in relation to the rest of the code and the sheet.unprotect/,protect lines.

I was wondering if this code could be written to also add a row to a Google Sheets Table of the same layout?

 

[dmt32]

Hi Dave, I noticed you liked it but did not reply so I am not sure if you just didn't have time to reply or only read the last part and thought it was resolved.

both I have family over from USA & thought you resolved

I am still confused over that VBA coding. I understand entering the code to specify what is allowed, but I still don't know where it goes in relation to the rest of the code and the sheet.unprotect/,protect lines.

Place at end of your code once processing completed

Worksheets("Submitted Sales").Protect Password:="Password", DrawingObjects:=True, Contents:=True, _
                                      Scenarios:=True, AllowDeletingRows:=True, _
                                      AllowFiltering:=True

I was wondering if this code could be written to also add a row to a Google Sheets Table of the same layout?

Possible but not much time at moment - maybe post as a new question to forum.

Dave

 

[Excel_User_10k]

both I have family over from USA & thought you resolved


Place at end of your code once processing completed

Worksheets("Submitted Sales").Protect Password:="Password", DrawingObjects:=True, Contents:=True, _
                                      Scenarios:=True, AllowDeletingRows:=True, _
                                      AllowFiltering:=True

Possible but not much time at moment - maybe post as a new question to forum.

Dave

I thought so haha. Ah right. Don't worry, that is of course more important!

Ok brilliant. It works. Thank you. The option to delete Rows seems pointless as, even when I unlock all of the Cells in the Table, it still says there are locked cells. How can you possibly unlock all cells on the row and on every row that can be deleted. And if you do that, what is the point of protecting it in the first place haha?? MS logic there haha.

Ok sure. Just thought it would be better for you to tweak as you wrote the code originally. Will do.

 

[Excel_User_10k]

If I don't get a response to a new thread after a few days, should I re-post and reword it?

 

[dmt32]

If I don't get a response to a new thread after a few days, should I re-post and reword it?

you bump an unanswered thread - see Rule #12 forum rules.