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.

 

[dmt32]

Hi,
quick glance at your code you are posting two cells to same range in your table

 myRow.Range(15) = Range("E3")
    myRow.Range(15) = Range("C8")

Not tested but see if this update to your code resolves your issue

Private Sub cmd_Submit_Click()
    Dim myRow       As ListRow
    Dim cell        As Range, DataEntry As Range
    Dim  i          As Long
   
    With ThisWorkbook.Worksheets("Sales MTD").ListObjects("Submitted_Sales")
        Set myRow = .ListRows.Add(AlwaysInsert:=True)
    End With
    Set DataEntry = Me.Range("C10,C3:C4,D4,E4,C5,F4:F5,H9,C6,F6,D3,C7,F7,E3,C8,F8,F3,C9,F9,F10")
    For Each cell In DataEntry.Cells
        i = i + 1
        myRow.Range(i) = cell.Value
    Next cell
End Sub

Dave

 

[Excel_User_10k]

Hi,
quick glance at your code you are posting two cells to same range in your table

 myRow.Range(15) = Range("E3")
    myRow.Range(15) = Range("C8")

Not tested but see if this update to your code resolves your issue

Private Sub cmd_Submit_Click()
    Dim myRow       As ListRow
    Dim cell        As Range, DataEntry As Range
    Dim  i          As Long
 
    With ThisWorkbook.Worksheets("Sales MTD").ListObjects("Submitted_Sales")
        Set myRow = .ListRows.Add(AlwaysInsert:=True)
    End With
    Set DataEntry = Me.Range("C10,C3:C4,D4,E4,C5,F4:F5,H9,C6,F6,D3,C7,F7,E3,C8,F8,F3,C9,F9,F10")
    For Each cell In DataEntry.Cells
        i = i + 1
        myRow.Range(i) = cell.Value
    Next cell
End Sub

Dave

Hi Dave,

Oh yeah I had fixed that. I had inserted a new Column and had to update the sequence in the VBA. I didn't realise that was AFTER I posted this thread.

That works brilliantly! Even works if I leave a filter on. Doesn't show at the bottom of the page once unfiltered of course but it is a much better result than the previous code which just caused an error in that scenario.

However, it is still removes random Side Borders on some of the Cells in the Row it creates. Any clue on why it does it and how to fix please?

 

[Excel_User_10k]

Update:

Actually since this new code it isn't random anymore, it is specific. It does it EVERY time on the right border of Columns 7, 12, 16, 17, 18 (as referred to in the VBA code).

One other little thing is that I have the Final Cell (21/W) as 'Accounting' type. With the previous code it would enter the value no problem and keep this type. With this code it keeps changing it to 'Currency'. I prefer the space from the £ and the value with it being the Total. Any clue why it keeps changing it? Thank You.

 

[dmt32]

this code it keeps changing it to 'Currency'. I prefer the space from the £ and the value with it being the Total. Any clue why it keeps changing it? Thank You.

try changing this line

 myRow.Range(i) = cell.Value

for this

 myRow.Range(i) = cell.Text

and see if this resolves issue

Dave

 

[Excel_User_10k]

try changing this line

 myRow.Range(i) = cell.Value

for this

 myRow.Range(i) = cell.Text

and see if this resolves issue

Dave

Still the same I'm afraid.

Also, I decided to move one of the Columns to a more logical place, and thought it would be as simple as changing the order of the Cell references in the Coding so it puts them in the new order. Apparently not. It still does it in the same order as original so now it isn't aligning with the relative Columns. I REALLY don't understood the logic of that and why that didn't work haha.

So the new order should be:

"(C10,C4,D4,E4,C3,C5,F4:F5,I7,C6,F6,D3,C7,F7,E3,C8,F8,F3,C9,F9,F10")

 

[dmt32]

you have the quote mark in wrong place, should be

("C10,C4,D4,E4,C3,C5,F4:F5,I7,C6,F6,D3,C7,F7,E3,C8,F8,F3,C9,F9,F10")

if still have issues can only suggest you post copy of your worksheets with dummy data using MrExcel Addin XL2BB - Excel Range to BBCode or better, place a copy on a file sharing site like dropbox & provide a link to it

Dave

 

[Excel_User_10k]

you have the quote mark in wrong place, should be

("C10,C4,D4,E4,C3,C5,F4:F5,I7,C6,F6,D3,C7,F7,E3,C8,F8,F3,C9,F9,F10")

if still have issues can only suggest you post copy of your worksheets with dummy data using MrExcel Addin XL2BB - Excel Range to BBCode or better, place a copy on a file sharing site like dropbox & provide a link to it

Dave

Haha sorry, the quotation marks weren't part of the code, it was just to show that it was a snippet of the code ><. So they are in the place you suggested. I only moved the cell references around - I didn't touch anything else.

I have tried to get XL2BB to work for me on other projects without success. I can share a link on Dropbox though...

Sales Tracker.xlsx

Shared with Dropbox

www.dropbox.com

Whilst you are on there, perhaps you can take a look at Column G as you can see the last few rows it has no border (which looks white) on the right side) And on row W with the Accounting type changing.

Thank you Dave

 

[dmt32]

you have provided an xlsx file (no code) & worksheets do not match with the code you have been posting

ThisWorkbook.Worksheets("Sales MTD").ListObjects("Submitted_Sales")

 

[Excel_User_10k]

you have provided an xlsx file (no code) & worksheets do not match with the code you have been posting

ThisWorkbook.Worksheets("Sales MTD").ListObjects("Submitted_Sales")

Apologies. That was a preliminary version.

This is the current one:

Sales Tracker.xlsm

Shared with Dropbox

www.dropbox.com