MrExcel.com - Your One Stop for Excel Tips & Solutions

Need help with Access form and Stamping date/time/user id

Posted by Kevin Mac on December 13, 2001 1:25 PM

I have the following code on a worksheet and will be using a Microsoft Access "form" to fill in the worksheet. If I use the "user form" to input the data, it does not perform the code functions of data/time stamp and username. If I manually populate the cells, it works fine? Can anyone suggest a change to get the date/time/username stamps to work, when the data is input with the user form? I am also looking for a way to force the form to automatically appear, instead of the worksheet the data will be sent to, for the personell that will be entering data into the form and would like an idea of how to "force" the form to the users, and only myself or another designated admin can access the worksheet and database.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
;Dim Chged As Range

;For Each Chged In Target
;If (Chged.Column = 1) Then
;If (Chged.Row > 1) Then
;If (Chged = "") Then
;Chged.Offset(, 1) = ""
;Else
;Chged.Offset(, 20) = Application.UserName
;Chged.Offset(, 21) = XlParamaterDataType & " " & Format(Now(), "hh:mm AM/PM")
;Chged.Offset(, 22) = XlParamaterDataType & " " & Format(Now(), "m/d/yyyy")
;End If
;End If
;End If
;Next
;End Sub


Posted by Grumio on December 14, 2001 4:03 AM


Re your first question, try :-
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

You will need to change your code a bit to define what worksheet "Chged" is on, so that the procedure is only triggered on the required worksheet.

Also, it would be better to add as the first line :-
Application.EnableEvents = False

and add a last line :-
Application.EnableEvents = True

Not too sure about your second question without more info. Presumably the "form" could be activated by a workbook_open procedure in a workbook other than the one containing the database. The same procedure could include code to open the database workbook as hidden. Or alternatively, just have one workbook but keep the database worksheet hidden with protected access.


Posted by Kevin Mac on December 14, 2001 11:29 AM

Very nice, didn't consider some of that, I appreciate the help, I am still somewhat of a newbie to some of this, and probably always will be, considering the constantly changing technology around this field of business. Thanks for the assist again.