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]

Hi,
untested but see if this update to your code does what you want

Private Sub CommandButton1_Click()
    Dim DeviceNo    As Variant, m As Variant
    Dim LastRow     As Long, ws As Worksheet
   
    DeviceNo = deviceassetnumber.Value
    With DeviceNo
        If Len(.Value) = 0 Then Exit Sub Else If IsNumeric(.Value) Then .Value = Val(.Value)
    End With
   
    Set ws = Sheets("High School")
   
    m = Application.Match(DeviceNo, ws.Columns(1), 0)
   
    LastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1
   
    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 = IIf(IsError(m), 3, 4)
   
    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"), 64, IIf(IsError(m), "Check Out", "Check In")
   
    deviceassetnumber.Value = ""
    firstname.Value = ""
    lastname.Value = ""
    ComboBox1.Value = ""
    checkoutscreen.Hide
    MainScreen.Show
   
End Sub

Dave

 

[LarryGeorge]

Hi Dave,

Thanks for the attempt, but I'm performing the "check in" from a different screen, not within the same screen

I wish there was a way to attach our excel file to these so you can see my actual application

 

[dmt32]

Hi,
not sure I fully follow you - code should search column A of your worksheet & if match (no error) change value from 3 to 4

You can share a copy of your workbook with dummy data with the Forum by placing on a file sharing site like dropbox & provide a link to it.

Dave

 

[LarryGeorge]

Technology Loaner - Larry Edits.xlsm

Shared with Dropbox

www.dropbox.com

Larry

 

[dmt32]

Apologies did something rather silly in my last post



This code

DeviceNo = deviceassetnumber.Value

With DeviceNo

If Len(.Value) = 0 Then Exit Sub Else If IsNumeric(.Value) Then .Value = Val(.Value)

End With

Should be this

DeviceNo = deviceassetnumber.Value

If Len(DeviceNo) = 0 Then Exit Sub Else If IsNumeric(DeviceNo) Then DeviceNo = Val(DeviceNo)

I see you have created Two userforms for each operation (checkin / checkout)

You only need one userform which for each operation.

My time is limited but will, unless another here steps in, take a look for you

Dave

 

[dmt32]

Hi,

Having a cursory glance at your project & just need to understand if the Asset Number entry is unique or if it is used each time a device is issued?

Dave

Asset Number	First Name	Last Name	Device	Date Out	Time Out		Date In	Time In
48393	John	Smith	ChromeBook	18/03/2022	12:45 PM
78945	Joe	Slick	Charger	18/03/2022	12:46 PM
9635	Larry	George	ChromeBook	18/03/2022	12:46 PM

 

[LarryGeorge]

Hi Dave,

The asset number is unique to the device. It’s usually a 6 digit number assigned to the device.
My plan is, if the asset number appears anywhere in column A and the box in G on the same row is red then a message pops up that the device is already checked out.

If the asset number appears in Row A and the box in G is green, then a new entry happens and the device can be checked out.

A device can be checked in and out several times a day in the school.

 

[dmt32]

Hi Dave,

The asset number is unique to the device. It’s usually a 6 digit number assigned to the device.

Hi,
probably did not ask question in right way - I understand the number is unique to the device so would I be correct in saying that it could appear multiple times in column A when issued to other users? If so, you either will need a table with Student names & unique Student ID to find the correct device in the table or, archive the record when device returned to another table.

Perhaps you can let me know your thinking

Dave

 

[LarryGeorge]

It can be archived to another column.
Before the kids go how for the day. I want the secretary to be able to find which devices are still out and who has them, as they need to be checked back in before they are released for the day