USERFORM SAVE COMMAND CLICK - UPDATE COMMAND

haseeb · Jan 6th 2022

Hi,

I need a help regarding little issues in the vba coding. I am working since last many days on file and have done all in my strength from Youtube and vba forums to make this file without having knowledge of vba coding.

The file has a sheet name Purchases having a structured table " Purchasing" and a user form link to that table. I am still unable to make it perfect since days while trying to complete it.

it is completed from my angle any how I don't know unless the issues will comes later on

Delete key and code working FIne

Search key working fine

List box double click working fine and record on user form and search text box are sync with each others.

Problem

Save Command on click error sometimes.

1) I am unable to handle this. when i click save its goes to runtime error and yellow lines come on first line of transfer of data i.e EntryNo.value to S.no in table. but when i click to continue form its came back to userform and entry has been saved in table but the format of font is bit small and different alignment when saved cleanly.

.2) If during data earning i found new item . I have to close the form and add new item in table "itemsMaster" and if from there i Run form . and enter data when click to save subscript out of range error comes while if i open user form while my "Purchases" SHEET is active this error never comes. Is it possible to remove this sensitivity?

3) Update command is working perfectly but its taking almost 10 to 15 seconds. and calculating threads

4) I try to find solution for list box highlitened when saved complete but didnt found solution or may be I am nill in vba i dont understand if its easy. list box highlight row never moves when serach button clik to display desired entry No. on userform.04-01-22.xlsm 04-01-22.xlsm

I will be grateful to all champs here for their kind consideration on my file which is really big. But right now I need help the issues written above.

As I am keen to complete this home project.

Thanking you.

Please find attached file.

04-01-22.xlsm

**royUK** · Jan 6th 2022

You are using a Table but your code is not written for use with a Table.Presumably your code needs to save to a new row in the Table.

**royUK** · Jan 6th 2022

Try this code, complete the lines required to add the data, I've put a few in for you.

Code

Private Sub cmdsave_Click()

Dim oTbl As ListObject
Dim oNewRw As ListRow
Set oTbl = ActiveSheet.ListObjects(1)
Set oNewRw = oTbl.ListRows.Add(AlwaysInsert:=True)

With oNewRw
.Range(1).Value = EntryNo.Value
           .Range(2).Value = DocRef.Value
           .Range(3).Value = Tdate.Value
           ''///etc
'           rng.Parent.Cells(LastRow + 1, 4).Value = SuppInv.Value
'           rng.Parent.Cells(LastRow + 1, 5).Value = SupplierName.Value
'           rng.Parent.Cells(LastRow + 1, 6).Value = ComboBox1.Value
'           rng.Parent.Cells(LastRow + 1, 7).Value = ItemCode.Value
'           rng.Parent.Cells(LastRow + 1, 8).Value = ItemType.Value
'           rng.Parent.Cells(LastRow + 1, 9).Value = Classification.Value
'           rng.Parent.Cells(LastRow + 1, 10).Value = UnitofMeasurement.Value
'           rng.Parent.Cells(LastRow + 1, 11).Value = CostPerUnit.Value
'           rng.Parent.Cells(LastRow + 1, 12).Value = Upurchase.Value
'           rng.Parent.Cells(LastRow + 1, 13).Value = ItemPrice.Value
'           rng.Parent.Cells(LastRow + 1, 14).Value = TaxCode.Value
'           rng.Parent.Cells(LastRow + 1, 15).Value = TaxRate.Value
'           rng.Parent.Cells(LastRow + 1, 16).Value = TaxAmount.Value
'           rng.Parent.Cells(LastRow + 1, 17).Value = TaxExclusivePrice.Value
'           rng.Parent.Cells(LastRow + 1, 18).Value = InvoicePrice.Value
'           rng.Parent.Cells(LastRow + 1, 19).Value = TotalTax.Value
'           rng.Parent.Cells(LastRow + 1, 20).Value = Rounding.Value
'           rng.Parent.Cells(LastRow + 1, 21).Value = TotalInvoice.Value
           End With
     EntryNo = Application.WorksheetFunction.Max(oTbl.ListColumns(1)) + 1
    End Sub

Display More

haseeb · Jan 6th 2022

I have done this before by defining table name with some other code available on internet it was working but had some errors in middle then i had to go back and resize my table and error gone.

But your code is bit different and is working well and I have done some dummy entries and that are saving nicely. Can you guide me what is the reason of calculating threads for my knowledge as SOME Offset command in my supplier name list has made so much trouble for this calculating thread and was taking more than 1 minute so I REMOVE THAT SUPPLIER LIST RANGE BY "TABLE".

One issue is that 32 line of code is giving "Run time error 1004 " Unable to get the Max Property of Worksheet Function class .

I didn't know how to write this code with condition but an increment was required after save so that another entry with same supplier can be entered without filling same previous text boxes.

I will be thankful to you if you suggest the solution for this and is there any thing can be easily done when the command save click the list box highlight the added line rather came to 1st entry.

Thanks Roy

**royUK** · Jan 7th 2022

Quote

have done this before by defining table name with some other code available on internet it was working but had some errors in middle then i had to go back and resize my table and error gone.

If you needed to resize the table then it hadn't been formatted as Table.

Quote

One issue is that 32 line of code is giving "Run time error 1004 " Unable to get the Max Property of Worksheet Function class

You are calculating the next number after saving, but should do the calculation first. Like this

Code

With oNewRw
.Range(1).Value = Application.WorksheetFunction.Max(oTbl.ListColumns(1).DataBodyRange) + 1

I don't really understand the rest of your question. I've had a quick look at the rest of your code but it could all be better written. I'm not sure where you are getting the code from but there are more efficient ways to code it.

haseeb · Jan 7th 2022

Hi ROY

Thanks for your reply

I have made new file with relative sheets only.

But dont know why save code is not working.

Method 'Add' of object 'Listrows' failed

**royUK** · Mar 20th 2022

I've just found this. The save code works perfectly with no errors

USERFORM SAVE COMMAND CLICK - UPDATE COMMAND

royUK Mar 20th 2022

Participate now!

Share

Tags