Approval VBA Macro

[Randy]

We have documents that need to go through a formal approval process. Without
getting into workflow, etc..., I just need a simple macro that will grab the
currently logged on userid along with the date/time stamp. I'm a novice vba
user and have done some vba stuff in Excel. Actually, I was able to do this
in Excel by creating a control box and when the user clicked either Yes or
No, their username and the date/time stamp appeared next to the check box.
Oh, in Excel I have two checkboxes, one for yes and one for no. If they
change their mind and uncheck the box then their username and date/time
disappear. I need this same functionality in Word as well as to have these
"approval" fields locked after it is accepted or rejected (for auditing
purposes). I have found some Word code but it makes no sense to me because
unlike Excel there are no cell, sheet, etc... references. I would appreciate
any help I can get on this.

Thanks!

Randy

 

[Ed]

Hi, Randy. Here's one solution. I used code from the Word MVP site -
http://www.word.mvps.org/FAQs/MacrosVBA/GetCurUserName.htm - provided by
Astrid Zeelenberg. The function code must be in a regular module. I called
it from a UserForm with a label and two command buttons.

Here's the module code:

Option Explicit

'Declare for call to mpr.dll.
Declare Function WNetGetUser Lib "mpr.dll" _
Alias "WNetGetUserA" (ByVal lpName As String, _
ByVal lpUserName As String, lpnLength As Long) As Long

Const NoError = 0 'The Function call was successful


Function GetUserName() As String

'Buffer size for the return string.
Const lpnLength As Long = 255

'Get return buffer space.
Dim status As Integer

'For getting user information.
Dim lpName, lpUserName As String

'Assign the buffer size constant to lpUserName.
lpUserName = Space$(lpnLength + 1)

'Get the log-on name of the person using product.
status = WNetGetUser(lpName, lpUserName, lpnLength)

'See whether error occurred.
If status = NoError Then
'This line removes the null character. Strings in C are null-
'terminated. Strings in Visual Basic are not null-terminated.
'The null character must be removed from the C strings to be used
'cleanly in Visual Basic.
lpUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
End If

'Display the name of the person logged on to the machine.
GetUserName = lpUserName

End Function

Sub ZZ_TimeStamp()
UserForm3.Show vbModeless
End Sub

Here's the UserForm code:

Dim strStamp As String
Dim strUser As String
Dim strDate As String

Private Sub CommandButton1_Click()
Selection.Range.Text = strStamp
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
strUser = GetUserName
strDate = Format(Date, "dd mmm yyyy")
strStamp = strUser & " - " & strDate
Label1.Caption = strStamp
End Sub

HTH
Ed

 

[Randy]

Ed,

Thanks for the quick response. I'm not familiar with userforms and buttons
in Word but I was able to figure it out in Excel so I'll give it a shot.
Also, any idea how to lock down the checkbox and user information once a box
has been checked? We need it for auditing purposes. I was thinking one way
may be to ask them "are you sure?" when checking a box and then somehow have
that information locked so that it cannot be changed.

Randy

 

[Ed]

Randy,

UserForms and buttons work the same in Word as they do in Excel. See
http://word.mvps.org/FAQs/Userforms/index.htm

The macro set-up I presented inserts the UserName from the computer station
and the current date at the insertion point in the Word document that is
currently open. If you want to make this a permanent part of the document,
you might consider writing this to a Custom Document Variable. You should
be able then to put a Field somewhere that displays this information.
Anything you put in the document text - including a field - can be deleted;
with a field, though, the information itself is still there as part of the
metadata.

HTH
Ed