Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / August 2006

Tip: Looking for answers? Try searching our database.

Approval VBA Macro

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Randy - 09 Aug 2006 22:18 GMT
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 - 10 Aug 2006 00:01 GMT
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

> We have documents that need to go through a formal approval process.
> Without
[quoted text clipped - 20 lines]
>
> Randy
Randy - 10 Aug 2006 13:43 GMT
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

> 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
[quoted text clipped - 95 lines]
> >
> > Randy
Ed - 10 Aug 2006 15:14 GMT
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

> Ed,
>
[quoted text clipped - 116 lines]
>> >
>> > Randy
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.