Adding date to survey form

[Lynda]

The ongoing saga of the exit/movement survey continues. My workmate that
collects the data from this intranet survey, that once completed is sent by
email via a submit button, would like the date the survey was sent to appear
on the completed survey.
After she has received the form she then stores them in a folder so she can
work on them later. When she eventually retrieves them she would like to be
able to see what date they were sent. I would like to be able to put a macro
or something in a cell on the sheet so the sender doesn't have to do it.
Obviously I don't want the date to automatically update when she opens the
sheet either. Thanks in advance again.

Cheers
Lynda

 

[JLatham]

Here are a couple of code segments that may help. This presumes that your
workmate opens the files when she receives them, or shortly thereafter.

First, you'll need to know her username when she's logged into her system.
This will let you find out what that is:

Put this declaration at the top of a code module, ahead of any Sub or
Function code.

' Access the GetUserNameA function in advapi32.dll and
' call the function GetUserName.
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long


then add this code to the module:

Sub GetWindowsUserName()
'this is just a sub to show how it works
MsgBox Get_Win_User_Name()

End Sub

Run that sub and it'll show you what their username is.


Leave the definition of the API call in the module and set this up as a
workbook open event. You'll need to change the worksheet name, cell address
and username to match the survey setup

Private Sub Workbook_Open()
If IsEmpty Worksheets("dateSheetName").Range("A1") And _
Get_Win_User_Name() = "workmateName" Then
Worksheets("dateSheetName").Range("A1") = Now()
End If
End Sub

Your workmate will have to remember that any time she opens a workbook with
that code in it, it's going to fill in the date. So if she's opened up the
workbook to get it ready to send to someone else, she'll need to delete the
date entry before sending it to them so that the date cell will come back to
her empty, to be refilled with the received date when she opens it after
receiving it.

If you don't know how to put code in particular locations (general code,
worksheet event, and especially workbook event handlers) post back and we'll
help you there, just be sure and tell us what version of Excel you're using.

 

[Lynda]

Thank you for your response J. I have to say I am somewhat confused. This
survey is posted on our intranet site at work and once completed is sent to a
number of different email recipients in our HR area. The lady who retrieves
the data is only one recipient. I don’t understand why I need her user name.
I know I don’t have any right to be asking questions because if I knew what I
was doing I wouldn’t need to be asking you people for help, right. She won’t
be sending the form onto anyone else, she will receive the excel (2003) form
by email, she will save the form and open it at a later date. I used the
‘submit by email’ VBE/VBA ( i don’t know what the difference is) from Debra’s
site. Could i put a macro in so that when she originally opens the form and
before she saves it that it will insert the date in, say, the bottom left
corner of the form. I hope all this makes sense.

Lynda

 

[JLatham]

Lynda, No problem, and we don't need you to be confused.


The reason I set it up as I did is that I wasn't entirely certain of how the
process worked.

What my code is doing is first saying "has anything (a date) been entered
into the cell where the date of receipt is to be recorded?" and if the
answer to that is YES, it does nothing so that the date in it is not
overwritten. But if that cell is empty, then it went on to ask "well, WHO
has just opened this file?" And if that someone is the person who's handing
them at the back end of the process (your workmate), then enter the current
date into that cell as the date received.

If you were to open the file on the intranet site, add the code, but without
the check for username match, then when anyone grabbed it and then opened it
on their system, the date would be inserted - showing when they
received/opened it, but not necessarily when it was returned or received by
yoru workmate.

Hope that explanation helps some.