Excel VBA method "Value" of object 'range' failed

[himaruasuka]

Hi! I've been searching for an answer to what happen to my excel file. While doing codes, it runs properly. But the next day, opening up my pc and relaunch my file to test it again to know where I stop, now it gives me Run Time Error method "Value" of object 'range' failed.

I've been trying all the way that I found here in the forum but it still malfunctioning.

First Opening the file, and test Add Item, it gives the error
The next time i open, and test the Delete, it delete the file row, and then try the Add Item, and it runs good. but the next time I Add an Item again, error appears.
What happen to my File? its weird...

here's the code to my AddNew_Item USF

Private Sub AddNew_Bundle_Txtbox_Change()
If AddNew_Cost_Txtbox.Value = "" Then AddNew_Cost_Txtbox.Value = 1
If AddNew_Bundle_Txtbox.Value = "" Then AddNew_Bundle_Txtbox.Value = 1
AddNew_Piece_Txtbox.Value = (AddNew_Cost_Txtbox.Value / AddNew_Bundle_Txtbox.Value)
End Sub

Private Sub AddNew_Button_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Inventory")
Dim lr As Long
lr = Sheets("Inventory").Range("B" & Rows.Count).End(xlUp).Row

'' Validation ''
If Me.AddNew_Item_Txtbox.Value = "" Then
MsgBox "Please Enter Item Name", vbCritical
Exit Sub
End If

If Me.AddNew_Used_Txtbox.Value = "" Then
MsgBox "Please Enter Used For", vbCritical
Exit Sub
End If

If IsNumeric(Me.AddNew_Cost_Txtbox.Value) = False Then
MsgBox "Please Enter the correct Cost", vbCritical
Exit Sub
End If

If IsNumeric(Me.AddNew_Bundle_Txtbox.Value) = False Then
MsgBox "Please Enter the correct Bundle", vbCritical
Exit Sub
End If

If IsNumeric(Me.AddNew_Piece_Txtbox.Value) = False Then
MsgBox "Please Enter the correct Price per Piece", vbCritical
Exit Sub
End If

If Me.AddNew_Remarks_Txtbox.Value = "" Then
MsgBox "Please Enter Remarks", vbCritical
Exit Sub
End If

'' Adding data in the excel sheet ''
With sh
ActiveWorkbook.Sheets("Inventory").Protect Password:="Roshier", userinterfaceonly:=True
.Cells(lr + 1, "B").Value = Me.AddNew_Item_Txtbox.Value
.Cells(lr + 1, "C").Value = Me.AddNew_Used_Txtbox.Value
.Cells(lr + 1, "D").Value = Me.AddNew_Cost_Txtbox.Value
.Cells(lr + 1, "E").Value = Me.AddNew_Bundle_Txtbox.Value
.Cells(lr + 1, "F").Value = Me.AddNew_Piece_Txtbox.Value
.Cells(lr + 1, "H").Value = Me.AddNew_Remarks_Txtbox.Value
End With

'' Clear Data ''
Me.AddNew_Item_Txtbox.Value = ""
Me.AddNew_Used_Txtbox.Value = ""
Me.AddNew_Cost_Txtbox.Value = ""
Me.AddNew_Bundle_Txtbox.Value = ""
Me.AddNew_Piece_Txtbox.Value = ""
Me.AddNew_Remarks_Txtbox.Value = ""

MsgBox "Data has been added!", vbInformation
AddNew_Item_Txtbox.SetFocus


End Sub

Private Sub AddNew_Cost_Txtbox_Change()
If AddNew_Cost_Txtbox.Value = "" Then AddNew_Cost_Txtbox.Value = 1
If AddNew_Bundle_Txtbox.Value = "" Then AddNew_Bundle_Txtbox.Value = 1
AddNew_Piece_Txtbox.Value = (AddNew_Cost_Txtbox.Value / AddNew_Bundle_Txtbox.Value)
End Sub
Private Sub AddNew_Cancel_Click()
Unload AddNew_Item
End Sub

Private Sub AddNew_Cost_Txtbox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii > 47 And KeyAscii < 58) Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
End If
End Sub

Private Sub AddNew_Bundle_Txtbox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii > 47 And KeyAscii < 58) Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
End If
End Sub

Please, help me fix it. TYIA

[jdc2000]

What line of code do you get the error on?

[Zvoni]

Don't use "Value" for your textboxes.

As for the error: At a guess (acc. to his Error-Description) the error occurs where he wants to set the content of "Cells(lr+1, "B")" etc.

Oh, and thank you for providing the Password to your Sheet....
Sheet-Protection is superfluous as it is, since it can be removed within 20 seconds