VBA

[LarryGeorge]

I'm trying to make a device loaner check out and check back in.

My "check out" code is below

I'm not sure how to get the "check in" process to work.

To check the device back in, I want it take the information deviceassetnumber.Value and to look in column A.
If device is found anywhere in column A turn the specific G column cell to .Interior.ColorIndex = 4


Check out code is as follows:


Private Sub CommandButton1_Click()

Dim LastRow As Long, ws As Worksheet

Set ws = Sheets("High School")

LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row

ws.Range("A" & LastRow).Value = deviceassetnumber.Value
ws.Range("B" & LastRow).Value = firstname.Value
ws.Range("C" & LastRow).Value = lastname.Value
ws.Range("D" & LastRow).Value = Format(Date, "MM/DD/YYYY")
ws.Range("E" & LastRow).Value = Format(Now, "HH:MM Am/Pm")
ws.Range("F" & LastRow).Value = ComboBox1.Value
ws.Range("G" & LastRow).Interior.ColorIndex = 3

MsgBox "Asset Tag Number : " & deviceassetnumber.Value & vbNewLine & vbNewLine & "Device : " & ComboBox1.Value & vbNewLine & vbNewLine & "First Name : " & firstname.Value & vbNewLine & vbNewLine & "Last Name : " & lastname.Value & vbNewLine & vbNewLine & "Date : " & Format(Date, "MM/DD/YYYY") & vbNewLine & vbNewLine & "Time : " & Format(Now, "HH:MM Am/Pm")

deviceassetnumber.Value = ""
firstname.Value = ""
lastname.Value = ""
ComboBox1.Value = ""
checkoutscreen.Hide
MainScreen.Show

End Sub

 

[dmt32]

I overlooked another way using the Red / Green status in Column G for the filter but whatever, I have very busy schedule but will see if can find some time to have a play

Dave