PDA

View Full Version : Hidden sheet in vba form excel vlookup



raiken20
11-19-2015, 07:50 AM
I am trying to create a log in log out form using excel vba.

1. If I hide the DATA sheet, and I enter the employee ID which is available. its giving me employee not available.
2. If I arrange the position of the sheets (put tracker first then data), its giving me error message.
3. I also want to that each time the employee click submit, it will ask for a password. if the password is correct, it will then do time stamp for log out or log in.
4. Also, In the tracker sheet. I put a column for the total number of hours. (I want this automatic if log in and log out time is filled in).

Please help me.

Bob Phillips
11-19-2015, 09:49 AM
Use


Set myrange = Worksheets("DATA").Range("A:C")

rather than just


Set myrange = Range("A:C")

raiken20
11-19-2015, 05:31 PM
So, if i use that way. I can hide and change the position of the sheet? It wont give me error anymore?

Bob Phillips
11-20-2015, 02:02 AM
It was something clearly wrong that jumped out at me, it might solve all problems, but it may highlight another. Why don't you try it and see.

raiken20
11-20-2015, 11:20 AM
Its working now, Thanks. I have another question and I think this would be the last. Saving the data is now working. But what gives me headache right now is, for the log out. What I wants is after they logged in, and I enter again the employee id. It should find the last transaction made and will time stamp for log out.

hope you can help me with this.

Thank you in advance

SamT
11-20-2015, 09:16 PM
In the ThisWorkbook Code Page
Option Explicit

Dim TransActionTime As Date


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
TransActionTime = Now
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("DATA").Range("Whatever") = TransActionTime
Me.Save
'Or
'Me.SaveAs
End Sub

'AND... OR
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets("DATA").Range("Whatever") = TransActionTime
End Sub