User Form: Timestamp and Buttons

[randomhause]

Hello!

I'm new to vba in excel and understanding the language & i need help with my user form

I have created a form that acts like a Log Form to track who goes into a room. In my form I have two command buttons that allow users to Submit Entry & Log Off. The Submit Entry button acts like a log-in button and Log Off button does just that, log them off.

Now I need my form to track the time & date the user entered a "Submit Entry" and when the user has "Log Out".

In my form I send the data from the User Form to a sheet in the same workbook. So I want this time & date to be recorded in this sheet.

As well, I need my sheet to only record those items (with date & time) that have been selected in the ListBox and keep all other entries listed. Currently, my form if I have to entries in my ListBox once I hit Log Out with a selected entry it will record all entries. So I need it to only record the one that is selected.

In addition, once I log out twice from my form all entries that where listed disappear from ListBox. I need the entries to stay in the listbox until it is selected and then the User click log out.

THANK YOU IN ADVANCE FOR YOUR HELP! Also, if you have any cool video tutorials on vba you can reference me to that would be great!

'Form has function that when 
'click object in excel it will show form

Sub RoundedRectangle2_Click()
frmLog.Show
End Sub
'This works properly

'frmLog(Code)
 
Private Sub cmdSubmit_Click()    'Adding txtbox data to listbox
    With lstList
        .AddItem txtVName.Value
        .List(.ListCount - 1, 1) = txtCName.Value
        .List(.ListCount - 1, 2) = txtPurpose.Value
    End With
    'Clearing txtbox after Submit Button clicked
    Me.txtVName.Text = ""
    Me.txtCName.Text = ""
    Me.txtPurpose.Text = ""
    'Set Focus on NameTextBox
    txtVName.SetFocus
End Sub


Private Sub cmdLogOut_Click()
    'To send data from User Form to Results Sheet
     Dim ws As Worksheet
    Set ws = Sheets("Results")

    Dim nextAvailableRow As Long
    Dim i As Long
    For i = 0 To lstList.ListCount - 1
        nextAvailableRow = ws.Range("A" & Rows.count).End(xlUp).Row + 1
        ws.Range("A" & nextAvailableRow) = lstList.Column(0, i)
        ws.Range("B" & nextAvailableRow) = lstList.Column(1, i)
        ws.Range("C" & nextAvailableRow) = lstList.Column(2, i)
    Next i
    Me.Hide
    
    'Clearing selected item in List Entry
   If lstList.ListIndex >= 0 Then
   lstList.RemoveItem lstList.ListIndex
   End If
   
End Sub

 

[Norie]

You don't say where you want the time stamp but for the second part try this.

    For i = 0 To lstList.ListCount - 1
        If lstList.Selected(I) Then
            nextAvailableRow = ws.Range("A" & Rows.count).End(xlUp).Row + 1
            ws.Range("A" & nextAvailableRow) = lstList.Column(0, i)
            ws.Range("B" & nextAvailableRow) = lstList.Column(1, i)
            ws.Range("C" & nextAvailableRow) = lstList.Column(2, i)
        End If
    Next i

By the way, what are you trying to do here?

    'Clearing selected item in List Entry
   If lstList.ListIndex >= 0 Then
   lstList.RemoveItem lstList.ListIndex
   End If

 

[randomhause]

Hello Norie! Thanks for Answering

I need the time stamp to show up in Column A of the Results worksheet in line with their corresponding entry, Sorry about that

I tried your code, and it worked! ray:

However, Now when I click the "X" button on my form twice the entries still disappear. I just want to make sure that no matter what the user clicks either the Log out or "X" on the form that the entries not selected still stay in the ListBox.

You don't say where you want the time stamp but for the second part try this.

    For i = 0 To lstList.ListCount - 1
        If lstList.Selected(I) Then
            nextAvailableRow = ws.Range("A" & Rows.count).End(xlUp).Row + 1
            ws.Range("A" & nextAvailableRow) = lstList.Column(0, i)
            ws.Range("B" & nextAvailableRow) = lstList.Column(1, i)
            ws.Range("C" & nextAvailableRow) = lstList.Column(2, i)
        End If
    Next i

Also, on this part It clears only the selected entry from the ListBox

By the way, what are you trying to do here?

    'Clearing selected item in List Entry
   If lstList.ListIndex >= 0 Then
   lstList.RemoveItem lstList.ListIndex
   End If

 

[Norie]

I'm a little confused, you want the time stamp in column A but the code you posted puts the value from the first column of the listbox in column A.

Do you actually want the values from the listbox in columns B to D?

Also, what values, if any, do you want removed from the listbox?

PS The code I posted doesn't remove any values from the listbox.

 

[randomhause]

Sorry for the confusion! But I do want the timestamp to be on Column A - I can change my code to put the data in the next columns. & my code already removes the data in the listbox (once they hit log out). now I only need to record the time and date for each entry once they submit entry & then again once they log out.

I'm a little confused, you want the time stamp in column A but the code you posted puts the value from the first column of the listbox in column A.

Do you actually want the values from the listbox in columns B to D?

Also, what values, if any, do you want removed from the listbox?

PS The code I posted doesn't remove any values from the listbox.

 

[Norie]

I think I'm even more confused now, , probably my fault though

What exactly do you want this userform to do/be used for?

In the first code I posted I assumed you had a multiselect listbox and you wanted to record the selections made in the listbox on a worksheet.

Any chance you could upload an example workbook to a file sharing site like Box.net?

PS Probably won't be able to have a proper look at it until Sunday, it's the weekend.

 

[randomhause]

No worries! This is also my first time posting

So as of right now, all I need for my form to record the time & date a user Submits a new entry and then record again the time and date when the user Logs-Out. I would like this to be recorded in Column A & B. To be clear, in my form when a User Submits a new entry it only shows up on the Listbox and only when the User Log-Out it will record the entry into the Results sheet - I just need to make sure that both times & dates get recorded in the results sheet. {all other issues have been resolved - I have attached the code I have now}.

Also, this form is going to help record what user goes in into the room at what time - for security reasons I need to record all of this.

I think I'm even more confused now, probably my fault though

What exactly do you want this userform to do/be used for?

In the first code I posted I assumed you had a multiselect listbox and you wanted to record the selections made in the listbox on a worksheet.

Any chance you could upload an example workbook to a file sharing site like Box.net?

PS Probably won't be able to have a proper look at it until Sunday, it's the weekend.

Here is the code I have now!

Private Sub cmdSubmit_Click()
    'Adding txtbox data to listbox
    With lstList
        .AddItem txtVName.Value
        .List(.ListCount - 1, 1) = txtCName.Value
        .List(.ListCount - 1, 2) = txtPurpose.Value
    End With
    'Clearing txtbox after Submit Button clicked
    Me.txtVName.Text = ""
    Me.txtCName.Text = ""
    Me.txtPurpose.Text = ""
    'Set Focus on NameTextBox
    txtVName.SetFocus
End Sub


Private Sub cmdLogOut_Click()
    'To send data from User Form to Results Sheet
     Dim ws As Worksheet
    ' create a results sheets if you do not already have one
    Set ws = Sheets("Results")


    Dim nextAvailableRow As Long
    Dim i As Long
     For i = 0 To lstList.ListCount - 1
        If lstList.Selected(i) Then
            nextAvailableRow = ws.Range("C" & Rows.count).End(xlUp).Row + 1
            ws.Range("C" & nextAvailableRow) = lstList.Column(0, i)
            ws.Range("D" & nextAvailableRow) = lstList.Column(1, i)
            ws.Range("E" & nextAvailableRow) = lstList.Column(2, i)
        End If
        
    Next i
    Me.Hide
    
    'Clearing selected item in List Entry
   If lstList.ListIndex >= 0 Then
   lstList.RemoveItem lstList.ListIndex
   End If
   
End Sub

 

[randomhause]

Above Issues Solved!

Description: This form helps gather and track user (without user_name/password) log-in & log-out descriptive information.

[FONT=Verdana]'Form has function that when 
[/FONT]'click object in excel it will show form

Sub RoundedRectangle2_Click()
frmLog.Show
End Sub

Private Sub cmdSubmit_Click()
    'Adding txtbox data to listbox
    With lstList
        .AddItem txtVName.Value
        .List(.ListCount - 1, 1) = txtCName.Value
        .List(.ListCount - 1, 2) = txtPurpose.Value
        .List(.ListCount - 1, 3) = Date + Time
    End With
    'Clearing txtbox after Submit Button clicked
    Me.txtVName.Text = ""
    Me.txtCName.Text = ""
    Me.txtPurpose.Text = ""
    'Set Focus on NameTextBox
    txtVName.SetFocus
End Sub
'--
Private Sub cmdLogOut_Click()
    'To send data from User Form to Results Sheet
     Dim ws As Worksheet
    ' create a results sheets if you do not already have one
    Set ws = Sheets("Results")


    Dim nextAvailableRow As Long
    Dim i As Long
     For i = 0 To lstList.ListCount - 1
        If lstList.Selected(i) Then
            nextAvailableRow = ws.Range("A" & Rows.count).End(xlUp).Row + 1
            ws.Range("C" & nextAvailableRow) = lstList.Column(0, i)
            ws.Range("D" & nextAvailableRow) = lstList.Column(1, i)
            ws.Range("E" & nextAvailableRow) = lstList.Column(2, i)
            ws.Range("A" & nextAvailableRow) = lstList.Column(3, i)
            ws.Range("B" & nextAvailableRow) = Date + Time
              
        End If
        
    Next i
    Me.Hide
      
    'Clearing selected item in List Entry
   If lstList.ListIndex >= 0 Then
   lstList.RemoveItem lstList.ListIndex
   End If
      
End Sub
'--
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please do not close the form. When ready, Log-Out."
  End If
End Sub