VBA solution for asset tracker

[jhod917]

I have created a VBA form that has Asset, User and two buttons for Check in and Check out. I have these set up to create a table when entries are made and you hit one of the two buttons using the following VBA code (unique code for each button to changed the row 3 value).

Private Sub chkout_Click()
Dim emptyRow As Long
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
Cells(emptyRow, 1).Value = user.Value
Cells(emptyRow, 2).Value = asset.Value
Cells(emptyRow, 3).Value = "Checked out"
Cells(emptyRow, 4).Value = Now()
End Sub

What i want though is to have the code first look to see if the asset.value exists already in the table and replace certain fields with the new inputed ones but if not then to add new rows just as the code would. I have tried using a few different codes found online with no sucesss such as the following:

Private Sub chkin_Click()

Dim tbl As ListObject
Dim newrow As ListRow
Dim Fnd As Range
Set tbl = Sheets("Sheet1").ListObjects("Table1")
With Sheets("Sheet1")
Set Fnd = tbl.ListColumns(1).DataBodyRange.Find(.Range("asset.Value").Value)
If Fnd Is Nothing Then
Set newrow = tbl.ListRows.Add
With newrow
.Range(1) = Worksheets("Sheet1").Range(asset.Value)
.Range(2) = Worksheets("Sheet1").Range(user.Value)
.Range(3) = Worksheets("Sheet1").Range("Checked In")
.Range(4) = Worksheets("Sheet1").Range(Now())
End With
Else
Fnd.Offset(, 1) = user.Value
Fnd.Offset(, 2).Value = "Checked in"
Fnd.Offset(, 3).Value = Now()
End If
End With
End Sub

Any help would be appreciated

 

[Micron]

Some of that is puzzling. IMO, if you used a combo whose list is the assets from the table, you don't need to check if it exists. If user drops down and doesn't see an asset, then either it doesn't exist (in which case checking in/out would be pointless) or it needs to be added to the table. That should be a separate process that you take care of independently. I say that because apparently, Excel combos do not have a Not In List event, unlike Access combos. Assuming you used that approach, then all you need is to modify check in/out values and forget about adding rows (can't tell for sure since you didn't identify what is getting edited and what is getting added). I'm not real familiar with Excel tables; I suspect I have a bias towards Access rather than trying to make workbooks
into databases.

edit - forgot to say please enclose code in code tags (use vba button on posting toolbar) to maintain indentation and readability.

 

[jhod917]

Thank you for the response. After i posted i did realise the VBA button but could not seem to edit my post.. anyway.. The intent of this would be to station it at cabinet full of tools. When a tool is put into the cabinet they would check it in and checked out when taken out. But if the asset # already exists in the list we want it to just be updated with the checked in/checked out status and date, then if it does not exist a new entry would be added to start tracking it.

 

[jhod917]

Including for additional context

 

[jhod917]

bump