none
Excel form creation using vb RRS feed

  • Question

  • I'm trying to create a excel form for computer asset tracking using visual basic code. 

    Private Sub Checkout_Btn_Click()
       Dim N As Long
       N = Sheet3.Cells(Rows.Count, "A").End(xlUp).Row + 1
       Sheet3.Cells(N, "A").Value = ListBox1.Value
       Sheet3.Cells(N, "B").Value = User_fld.Value
       Sheet3.Cells(N, "C").Value = Date
    End Sub

    In my combobox I have a list of checkout computers. The other field will have the name of the user getting the computer then a button named Checkout. 

    When I choose the item from the listbox and add the user in the field when I click the checkout button that data shows on Sheet3. 

    I would like the text that I choose from the listbox to either become hidden or disabled so it cannot be chosen again until it is checked back in using another button so on an so forth for each value in the list. After those values are checked in how could I get the date to reference back to which computer name was checked out when it is checked back into show on sheet3.cells(N, "D").value = Date?


    Thursday, January 12, 2017 6:47 PM

All replies

  • How would i get the checkin_btn_click() code to reference what is in cells (N, "A") .value on sheet3 to make sure what i select is the correct computer name then add the current date in column D on the same row instead of the checked in/out info?

    Private Sub Checkout_Btn_Click()
       Dim N As Long
       N = Sheet3.Cells(Rows.Count, "A").End(xlUp).Row + 1
       Sheet3.Cells(N, "A").Value = ListBox1.Value
       Sheet3.Cells(N, "B").Value = User_fld.Value
       Sheet3.Cells(N, "C").Value = Date
       Sheet3.Cells(N, "D").Value = "Checked Out"
       End Sub
     
    Private Sub Checkin_Btn_Click()
    Dim N As Long
       N = Sheet3.Cells(Rows.Count, "A").End(xlUp).Row + 1
       Sheet3.Cells(N, "A").Value = ListBox1.Value
       Sheet3.Cells(N, "B").Value = User_fld.Value
       Sheet3.Cells(N, "C").Value = Date
       Sheet3.Cells(N, "D").Value = "Checked In"
    End Sub


    • Edited by eorosz Friday, January 13, 2017 9:20 PM
    Friday, January 13, 2017 9:20 PM
  • Hi,

    You could directly check if it is correct computer name and then set the value in column D.

            If (Sheet3.Cells(N, "A").Value = "CorrectComputerName") Then

                Sheet3.Cells(N, "D").Value = "Checked Out" & Date

            End If

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 16, 2017 7:46 AM
    Moderator
  • Here's what i'm using on sheet 1 and sheet 3Sheet 1 Sheet 3

    I tried the line of code you gave but it doesn't seem to work with the checkin button. Is there a way to get the the computer name from the list box to hide itself after selection 

    • Edited by eorosz Monday, January 16, 2017 7:59 PM
    Monday, January 16, 2017 7:46 PM
  • I modded the code for the checkin button 
    Private Sub Checkin_Btn_Click()
    Dim N As String
       N = Sheet3.Cells(Rows.Count, "A").End(xlUp).Row + 1
           If (Sheet3.Cells(N, "A").Value = "") Then
                Sheet3.Cells(N, "E").Value = "Checked In" & Date
                
                End If        
    End Sub
    However when the checkin button is clicked it will add Checkout & date to row below instead of next to checkout data on the same row. 
    Monday, January 16, 2017 9:18 PM
  • I think you want to achieve:

    Click Checkout, the L-BOSCHECKOUT1 and the date are added into the sheet and the item is removed from the listbox.

    Click the Checkin button, the list is recovered.

    If I misunderstand, please let me know what exactly you want. An example or a sample file would be more helpful.

    >>Is there a way to get the the computer name from the list box to hide itself after selection

    There is no hide or visible property for listbox items. I suggest you remove it in Checkout button and then add again in Checkin button.

    >>However when the checkin button is clicked it will add Checkout & date to row below instead of next to checkout data on the same row.

    Because your N is the last new row. So after CheckOut, it has added one row, if you need to insert the value into the last used row, just use N = Sheet3.Cells(Rows.Count, "A").End(xlUp).Row


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 17, 2017 9:22 AM
    Moderator
  • Celeste, you are correct I want the other options in the list to hide when I click the checkout button then come back with the checkin button. 

    Then with adding checkin & date on the same Row I also will need a vlookup, Match statement to verify which one I checked out like for example I check out BOSCHECKOUT1 & BOSCHECKOUT4 but only 1 came back then next day. The checkout and date need to be put on the correct row. 

    • Edited by eorosz Tuesday, January 17, 2017 5:39 PM
    Tuesday, January 17, 2017 5:27 PM
  • Hi,

    There is no hide or visible property for listbox items, so you need to remove it and then add again. If you have any issue about this, please let us know and share your code with us.

    If you don't know how to use Vlookup/Match function in your project, please post a new thread, so that we could focus on the specific issue. I think it would be more helpful if you could share your sample file or project.

    Regards,

    Celeste 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 19, 2017 6:51 AM
    Moderator
  • We would like to get the first listbox to filter out the computername that is tied with a serial number when the Checkout button is pressed and is added to listbox2. 

    For the second listbox we would like to filter out the computername with a certain assigned serial number when the checkin button is clicked so it no longer shows in the list.  

    Private Sub CheckIN_btn_Click()
        Dim Rw As Long
        Rw = Sheet1.Cells("11", "B").Value
        '=HLOOKUP(A11,Log!A:F,MATCH(Equipment!A2,Log!E:E,0),FALSE)'
        Sheet1.Cells("12", "B").Value = Rw
        Sheet3.Cells(Rw, "D").Value = Date
        Sheet3.Cells(Rw, "E").Value = ""
    End Sub
    
    Private Sub Checkout_Btn_Click()
       Dim N As Long
       N = Sheet3.Cells(Rows.Count, "A").End(xlUp).Row + 1
       Sheet3.Cells(N, "A").Value = Equipment_Fld.Value
       Sheet3.Cells(N, "B").Value = User_fld.Value
       Sheet3.Cells(N, "C").Value = Date
       Sheet3.Cells(N, "F").Value = N
       Sheet3.Cells(N, "E").Value = CLng(Date) & N & "01"
    End Sub


    Office Equipment Checkout





    Friday, January 20, 2017 7:44 PM
  • Hi,

    All the controls in Check IN & OUT sheet of the file you shared differ from the picture. I suggest you share us the same file, or do you want me to achieve the same result?

    If you want me to achieve the same result, please see the file: https://1drv.ms/x/s!AkcxDWH1nFmJpRlABT6aGxHf-6I5

    It could add the item in the listbox2 and remove it from listbox1 when clicking CheckOut button. When clicking CheckIn button, it adds the date.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 23, 2017 11:46 AM
    Moderator