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, ok I figured it out!

And after several attempts I have managed to fix the border on one of the Columns. It is just that end Column for totals not keeping the Accounting type now. If you have any ideas?

 

[dmt32]

It is just that end Column for totals not keeping the Accounting type now. If you have any ideas?

try adding the line shown after the For Next loop & see if resolves

    For Each cell In DataEntry.Cells
        i = i + 1
        myRow.Range(i) = cell.Value
    Next cell
    
     myRow.Range(i).NumberFormat = _
    "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"

Dave

 

[Excel_User_10k]

try adding the line shown after the For Next loop & see if resolves

    For Each cell In DataEntry.Cells
        i = i + 1
        myRow.Range(i) = cell.Value
    Next cell
   
     myRow.Range(i).NumberFormat = _
    "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"

Dave

Perfect! Thank you for your help with this . Query resolved.

 

[dmt32]

most welcome - appreciate feedback

Dave

 

[Excel_User_10k]

Sorry Dave, come upon another hiccup. The table that this coding will add Rows to is to be viewed, filtered and allow Sales(rows) to be removed. However, to do this I have to Protect the Sheet. And I have realised that the code doesn't work when the Sheet is protected, obviously. I have tried incorporating the code

Sheets("Submitted Sales").Unprotect Password:="Password"

Sheets("Submitted Sales").Protect Password:="Password"

Not sure if I am just not entering it into the right place or it doesn't fit well within this kind of code but it isn't liking it. Getting the Debug message. How do I fix this issue please?

 

[Excel_User_10k]

Ok, I have figured it out and it works. However, all of the selections that I allow on the Protected Sheet (i.e. To remove Rows, Filter Columns, etc) are removed or reset once the Macro Code is run. Is there a way to stop this?

 

[dmt32]

Ok, I have figured it out and it works. However, all of the selections that I allow on the Protected Sheet (i.e. To remove Rows, Filter Columns, etc) are removed or reset once the Macro Code is run. Is there a way to stop this?

specify all the worksheet protection parameters you allow for your users

example

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

Dave

 

[Excel_User_10k]

Hi Dave, so is this as well as my Unprotect/Protect code, or as well as? Because I don't see the password in this bit of code. And where does it go, at the end?

What are the DrawingObjects and Contents? Are they necessary? I don't want them to be able to draw objects. I don't think that option is even usually in the list.

Ok the option to allow rows to be deleted is selected but when I try to delete a row in a table it says the Sheet is protected. Doesn't make sense?

 

[dmt32]

Password would still be the first Parameter - I was just showing an example of some of the others you can apply.

Answers to your further questions are in the VBA helpfile :Worksheet.Protect method (Excel)

Dave

 

[Excel_User_10k]

Sorry I don't know what you mean Dave. If by the first parameter you mean just the .unprotect part and I put that code instead of the .protect part then that will unprotect the worksheet but it won't be protected again after the code is finished? If you mean I keep BOTH the .unprotect and .protect parts then where would this go?

Ok great that helps with the references. Thank you. I get what that means now. So there isn't a way to allow rows to be deleted whilst also denying the option to edit the content of Cells in a Table? Unexpected. I just thought Deleting Rows meant you could delete rows haha.