PDA

View Full Version : Log date time and user



Juriemagic
06-23-2015, 03:32 AM
Hi good and clever people!..

I am working on a maintenance program for hotels and would like VBA to log certain info. There are also other functionality I require, which is:

1). When sheet "Update rooms" is activated, and NO changes to this sheet is made, (Maybe just viewing), the user must be able to close the sheet at any time
2). If changes are made, even just one change, this sheet cannot be exited unless a button "Submit", had been clicked.
3). Upon clicking submit, The date, time and username must be logged to a protected sheet, called "Logs".
4). The logging starts in A3:C3
5). Any future logging will log underneath the last entry.

I am sure this might be a lengthy one, but I am really in a mud pool with this one, as I do not have a single clue on how to remotely even achieve this. All and any help will be accepted with the utmost of appreciation and respect. Thank you all kindly..

SamT
06-23-2015, 08:48 AM
Dim Changed As Boolean
Dim Submitted As Boolean 'Set true by Submit Button

Private Sub Worksheet_Change(ByVal Target As Range)
Changed = True
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Changed And Not Submitted Then
Sht.Activate
Else
Changed = False
Submitted = False
End If
End Sub



Sub Log()
NextRow = Sheets("Log").Cells(RowsCount, 1).End(xlUp).Row + 1
If NextRow > 3 Then NextRow = 3
With Sheets("Log").NextRow
Cells(1) = Environ("USERNAME")
Cells(2) = Format(Now, "DDD MMM, dd,yyyy hh:mm")
End With
End Sub

Juriemagic
06-23-2015, 11:18 PM
Hi SamT

Just to test separately, I have opened a new workbook, pasted the code into the sheet module (sheet1), renamed sheet2 to "LOG", and then made a change on sheet1. Upon closing the only thing that happened was excel prompting to save or not. Upon saving the book closes..So point 2 of post #1 is not working?

SamT
06-24-2015, 06:11 AM
Point 2 was exit the Sheet, not the workbook. You'll need more code for that issue.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Saved = False Then MsgBox "Changes have been made"
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel as Boolean)
If Me.Saved = False Then MsgBox "Changes have been made"
End Sub

Were you able to view another sheet after the change was made?

Does the Submit button code call Sub Log and set submitted to True

Sub Log should be in a standard Module (ie: Module1.)

What happens if the User makes a change, then decides to cancel it?

Juriemagic
06-24-2015, 07:33 AM
I'm confused..I think I need to take a break. My brain even pains... SamT, first, I did not mean close the workbook in post #3, although that's what I wrote, sorry man, I meant when I closed (hid) the sheet, I could do it, without submitting. Anyways, SamT, I think I'm taking on more than I can handle. The setup I have is much more involved as what I have described, I have 8 sheets working together, almost all of them are filled to the brim in codes, and I think I get situations where the codes clash. I'm just going to forget about this thread, and take a break. I do appreciate your help, really I do. You have helped me in the past a good couple of times, I will never forget that. Till later my friend..

SamT
06-24-2015, 10:37 AM
That is called "Organic Growth" and it is the bane of program maintainers.

till later.

Juriemagic
08-31-2015, 12:10 AM
Please ignore..