PLEASE HELP WITH CODES

jhazziejhazz

New Member
Joined
Mar 11, 2021
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
I have this form "Inventory Data Entry Form" linked to a sheet named INVENTORYIN, However in this form, the Part NUmber dropdownlist is from other worksheet named "Product_Masterlist"

Please help me with the codes. I manage to get the Description and Cost auto populated when I click an item in the Part Number(source is from another worksheet named "product_masterlist) dropdownlist, however, I am having an error when I I try to click the add button (see codes in bold letters)


I am using these codes:

Private Sub cmbPartNo_Change()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Product_Masterlist")

Dim i As Integer

If Me.cmbPartNo.Value = i Then
Else


Me.txt_Description.Value = Application.WorksheetFunction.VLookup(Me.cmbPartNo, sh.Range("B:D"), 2, 0)
Me.txt_Cost.Value = Application.WorksheetFunction.VLookup(Me.cmbPartNo, sh.Range("B:D"), 3, 0)

End If


End Sub

and this for the add button

Private Sub cmdAdd_Click()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("INVENTORYIN")

Dim Last_Row As Long

Last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

'===================================Validation===========================================

If Me.txtRRNo.Value = "" Then
MsgBox "Please Input Receiveing Report Reference Number"
Exit Sub
End If

If Me.txtSupplierInvoice.Value = "" Then
MsgBox "Please Indicate Suppliers Invoice Reference Number"
Exit Sub
End If


'========================================================================================

sh.Range("A" & lr + 1).Value = "=ROW()-1"
sh.Range("B" & lr + 1).Value = Me.txtRRNo.Value
sh.Range("C" & lr + 1).Value = Me.txtSupplierInvoice.Value
sh.Range("D" & lr + 1).Value = Me.txtTransdate.Value
sh.Range("E" & lr + 1).Value = Me.cmbOrderType.Value
sh.Range("F" & lr + 1).Value = Me.cmbPartNo.Value
sh.Range("G" & lr + 1).Value = Me.txt_Description
sh.Range("H" & lr + 1).Value = Me.txt_Cost.Value
sh.Range("I" & lr + 1).Value = Me.txt_Qty.Value
sh.Range("J" & lr + 1).Value = Me.txt_Cost.Value * Me.txt_Qty.Value


''''''' Clear Boxes ''''''

Me.txtRRNo.Value = ""
Me.txtSupplierInvoice.Value = ""
Me.txtTransdate.Value = ""
Me.cmbOrderType.Value = ""
Me.cmbPartNo.Value = ""
Me.txt_Description = ""
Me.txt_Cost.Value = ""
Me.txt_Qty.Value = ""
Me.txt_Total.Value = ""


Call Show_Data

MsgBox "Data has been added to the database", vbInformation


End Sub



Than you so much for those who will take time to help me. You guys are all amazing.
 

Attachments

  • 1.png
    1.png
    125.7 KB · Views: 4
  • 2.png
    2.png
    139.6 KB · Views: 4

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
I am having an error when I I try to click the add button (see codes in bold letters)

The error is in cmbPartNo_Change() not cmdAdd_Click().
What is the error?
I have no problems with both lines.
 

jhazziejhazz

New Member
Joined
Mar 11, 2021
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
The error actually comes up when I try to add new data using the form, and when I click ADD button, an error will come up, and when I click the debug, it will lead me to these codes...

Me.txt_Description.Value = Application.WorksheetFunction.VLookup(Me.cmbPartNo, sh.Range("B:D"), 2, 0)
Me.txt_Cost.Value = Application.WorksheetFunction.VLookup(Me.cmbPartNo, sh.Range("B:D"), 3, 0)
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
When you clear the boxes the change-event is triggered but because the combobox is empty the vlookup gives an error.
If Me.cmbPartNo.Value = i Then ==> If Me.cmbPartNo.Value = i Or Me.cmbPartNo.Value ="" Then
 

jhazziejhazz

New Member
Joined
Mar 11, 2021
Messages
28
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

When you clear the boxes the change-event is triggered but because the combobox is empty the vlookup gives an error.
If Me.cmbPartNo.Value = i Then ==> If Me.cmbPartNo.Value = i Or Me.cmbPartNo.Value ="" Then
Sorry for the ignorance, but how should I fix it? Do i need to delete or add something?
Thanks
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Yes
Replace If Me.cmbPartNo.Value = i Then with If Me.cmbPartNo.Value = i Or Me.cmbPartNo.Value ="" Then
 
Solution

jhazziejhazz

New Member
Joined
Mar 11, 2021
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
Yes
Replace If Me.cmbPartNo.Value = i Then with If Me.cmbPartNo.Value = i Or Me.cmbPartNo.Value ="" Then
Oh god its perfect.
Thank you so much.

One more thing, when I added the data, my column heads was replaced by the new data I just added... (please see attached image)
 

Attachments

  • 3.png
    3.png
    124.9 KB · Views: 5

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
You are using two different names for last row: lr and Last_Row
VBA Code:
lr= WorksheetFunction.Max(Application.WorksheetFunction.CountA(sh.Range("A:A")), 1)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,201
Messages
5,640,819
Members
417,168
Latest member
StumpoC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top