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 / December 2005

Tip: Looking for answers? Try searching our database.

Create signature lines based off list box selections

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
John G. - 28 Dec 2005 15:10 GMT
I have a user form for a policy template that asks for information that
it places into the header. One of the list boxes on this form is a "To
be approved by:" box that identifies who needs to sign the official
policy. Is there a way that some VBA can create a signature line for
each of the items seelcted in the list box? I would need the selected
list box text to appear by the signature line.

Thanks for you help.
Jean-Guy Marcil - 28 Dec 2005 16:53 GMT
John G. was telling us:
John G. nous racontait que :

> I have a user form for a policy template that asks for information
> that it places into the header. One of the list boxes on this form is
> a "To be approved by:" box that identifies who needs to sign the
> official policy. Is there a way that some VBA can create a signature
> line for each of the items seelcted in the list box? I would need the
> selected list box text to appear by the signature line.

Something like:

Dim MyInsertRange As Range

Set MyInsertRange = ActiveDocument.Sections(1) _
   .Headers(wdHeaderFooterPrimary).Range.Paragraphs(1).Range

With MyInsertRange
   .Collapse wdCollapseEnd
   .InsertAfter "To be approved by: "
   .InsertAfter Me.ListBox1.Value
End With

Or, if you have an Autotext for the signature block:

Dim MyInsertRange As Range

Set MyInsertRange = ActiveDocument.Sections(1) _
   .Headers(wdHeaderFooterPrimary).Range.Paragraphs(1).Range

With MyInsertRange
   .Collapse wdCollapseEnd
   .InsertAfter "Name_of_AutoText"
   .InsertAutoText
   .InsertAfter Me.ListBox1.Value
End With

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org 

John G. - 28 Dec 2005 17:49 GMT
Thanks for taking the time to reply. I dont have a signature block
setup, and since Im very new to all this Im unsure how to apply this to
my code.

I have inserted the code to my form, and perhaps you can see how it is
running. I dont want anything inserted into the header, I basically
just want the selections from the listbox "To be approved by:" to have
a signature line created for each one independantly. So if I select 3
or 4 list box entries, the code will create the appropriate # of
signature lines requiring those people to sign the document. The lines
can appear at the end of the document. Im not sure how to create a
signature block.

Take a look at this, and let me know what you think, and how I can
proceed. I really need to get this done. :)

<CODE>
Private Sub cmdOK_Click()
Dim Departments As String, Approvals As String, OrigDepartment As
String

Application.ScreenUpdating = True

With Me
If IsDate(txtEffectiveDate.Text) Then
 .txtEffectiveDate.Text = Format(.txtEffectiveDate.Text, "mm/dd/yyyy")
Else
MsgBox .txtEffectiveDate.Text & " is not a valid Effective Date
format."
 With .txtEffectiveDate
     .SetFocus
     .SelStart = 0
     .SelLength = Len(.Text)
 End With
 Exit Sub
 End If
End With

'With Me
'If IsDate(txtReplaceDate.Text) Then
'  .txtReplaceDate.Text = Format(.txtReplaceDate.Text, "mm/dd/yyyy")
'Else
' MsgBox .txtReplaceDate.Text & " is not a valid Replacement Date
format."
'  With .txtReplaceDate
'      .SetFocus
'      .SelStart = 0
'      .SelLength = Len(.Text)
'  End With
'  Exit Sub
'  End If
'End With

Departments = ""
Approvals = ""
OrigDepartment = ""

For x = 0 To lstDepartments.ListCount - 1
   If lstDepartments.Selected(x) Then
       Departments = Departments & ", " & lstDepartments.List(x)
   End If
Next
For x = 0 To lstApprovals.ListCount - 1
   If lstApprovals.Selected(x) Then
       Approvals = Approvals & ", " & lstApprovals.List(x)
   End If
Next
For x = 0 To lstOrigDepartment.ListCount - 1
   If lstOrigDepartment.Selected(x) Then
       OrigDepartment = OrigDepartment & ", " &
lstOrigDepartment.List(x)
   End If
Next

Departments = Mid(Departments, 3) 'Strip the initial comma and space
Approvals = Mid(Approvals, 3)
OrigDepartment = Mid(OrigDepartment, 3)

UpdateBookmark "bmAffectedDepartments", Departments
UpdateBookmark "bmPolicyDesc", txtPolicyDesc
UpdateBookmark "bmEffectiveDate", txtEffectiveDate
UpdateBookmark "bmApproved", Approvals
UpdateBookmark "bmReplaceDate", txtReplaceDate
UpdateBookmark "bmDepartments", OrigDepartment

UserForm1.Hide
Unload Me
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClear_Click()
UpdateBookmark "bmApproved", ""
UpdateBookmark "bmAffectedDepartments", ""
UpdateBookmark "bmDepartments", ""
UpdateBookmark "bmPolicyDesc", ""
UpdateBookmark "bmEffectiveDate", ""
UpdateBookmark "bmReplaceDate", ""
For x = 0 To lstDepartments.ListCount - 1
   lstDepartments.Selected(x) = False
Next
For x = 0 To lstApprovals.ListCount - 1
   lstApprovals.Selected(x) = False
Next
For x = 0 To lstOrigDepartment.ListCount - 1
   lstOrigDepartment.Selected(x) = False
Next

txtPolicyDesc.Value = Null
txtEffectiveDate.Value = Null

ScreenUpdating = True

End Sub
Private Sub UserForm_Initialize()
lstDepartments.List = Array("All Departments", "Accounts Payable",
"Accounts Receivable", "Administration", "ICG Admin", "ICG
Engineering", "ICG Field OPs", "ICG Proj. Mgmt.", "Operations",
"Rentals", "Service", "Warehouse")
lstApprovals.List = Array("President", "Director of Finance", "Director
of ICG", "Director of Sales-PAV", "Director of Sales-SAS", "Accounting
Mgr.", "Engineering Mgr.", "Field OPs.", "HR Mgr.", "Inside Sales
Mgr.", "IS Mgr.", "Marketing Mgr.", "Operations Mgr.", "Rental & Tech.
Services Mgr.", "Warehouse Mgr.")
lstOrigDepartment.List = Array("Accounts Payable", "Accounts
Receivable", "Administration", "ICG Admin", "ICG Engineering", "ICG
Field OPs", "ICG Proj. Mgmt.", "Operations", "Rentals", "Service",
"Warehouse")
End Sub
Sub UpdateBookmark(BookmarkToUpdate As String, TextToUse As String)
   Dim BMRange As Range
   Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
   BMRange.Text = TextToUse
   ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange
End Sub
</CODE>
John G. - 28 Dec 2005 18:14 GMT
By the way you were referring to signature blocks, and I have no idea
how to create this. I had another post going in the userform group, and
if you could further explain signature blocks, and how I could apply
this to my document, I'd appreciate it.

Thanks
Jean-Guy Marcil - 28 Dec 2005 19:39 GMT
John G. was telling us:
John G. nous racontait que :

> By the way you were referring to signature blocks, and I have no idea
> how to create this. I had another post going in the userform group,
> and if you could further explain signature blocks, and how I could
> apply this to my document, I'd appreciate it.

I just meant a block of text containing the text you required for the
signature. I thought you would have as many text blocks as you had
signatures.

Now it sems you are meaning this:

To be approved by:  _____________________
                               _____________________
                               _____________________
if you have three names selected in the list box, is that right? or do you
mean:

To be approved by:  _____________________
                                   John Smith
                               _____________________
                                   Bob Black
                               _____________________
                                   Lucy Looping

It is not entirely clear what you are after. So before I further suggest
anything, can you clarify what the content of the userform control is, what
the user does with it and what you want to appear in the document.

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org 

John G. - 28 Dec 2005 23:18 GMT
Sorry about that, I'll try to articulate my request a little better.

In the code above (that is behin a userform) I have a listbox that is
being populated when the form initializes as shown below:
lstApprovals.List = Array("President", "Director of Finance", "Director

of ICG", "Director of Sales-PAV", "Director of Sales-SAS", "Accounting
Mgr.", "Engineering Mgr.", "Field OPs.", "HR Mgr.", "Inside Sales
Mgr.", "IS Mgr.", "Marketing Mgr.", "Operations Mgr.", "Rental & Tech.
Services Mgr.", "Warehouse Mgr.")

Any number of these items can be selected at once, and the results are
posted within a bookmark separated by a comma inside the header. I
would like code to create a signature block for each item selected from
the list box.

For example if I select Field Ops, Marketing Mgr., HR Mgr. I would like
to see a signature space for these three selections

Approved by:  _____________________          _________________________
     ________________________
                           Field Ops
  Marketing Mgr.                              HR Mgr.

If only one person was selected such as Field Ops, I would like to only
have one signature space for Field Ops.

Thanks for your help with this isse.

As a side topic:

This whole project is in an effort to make consistent policies. Im also
trying to find a way to limit who can "officially" create policies. The
way this is setup is it has the word "draft" as a watermark in the
background, and with NTFS permission I'll allow only the policy
coordinator to save the final document to the network drives. Of course
they'll remove the "draft" watermark before & create a pdf to make it
"official".

In your opinion is there a better way to do this within Word? The
ultimate would be to avoid printing the hard copy and having physical
signatures. It would be great to have an electronic version. Any ideas
or ways to faciliate this?
Jean-Guy Marcil - 29 Dec 2005 06:39 GMT
John G. was telling us:
John G. nous racontait que :

> Sorry about that, I'll try to articulate my request a little better.
>
[quoted text clipped - 12 lines]
> would like code to create a signature block for each item selected
> from the list box.

In the header as well?

> For example if I select Field Ops, Marketing Mgr., HR Mgr. I would
> like to see a signature space for these three selections
[quoted text clipped - 3 lines]
>                            Field Ops
>   Marketing Mgr.                              HR Mgr.

How about layout... what if 6 titles are selected? This will not fit on one
line...

> If only one person was selected such as Field Ops, I would like to
> only have one signature space for Field Ops.
[quoted text clipped - 10 lines]
> drives. Of course they'll remove the "draft" watermark before &
> create a pdf to make it "official".

Not sure how you can connect NTFS permission with Word content...
Probably need more than VBA for this...
Try posting in a VB group.

> In your opinion is there a better way to do this within Word? The
> ultimate would be to avoid printing the hard copy and having physical
> signatures. It would be great to have an electronic version. Any ideas
> or ways to faciliate this?

Remember that Word is not designed for real document security.
Have you seen my reply to your post in the other group for some ideas?

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org 

John G. - 29 Dec 2005 15:31 GMT
No the signature lines should only appear at the bottom of the page. If
the signature spaces require two lines to fit, this would be ok. I just
need it to have spaces for each title selected from the listbox, so
that when printed people know where to sign their names.

Yes I read the other post, and appreciate your comments. The problem is
that I dont know the code to accomplish this goal. Im sure there must
be a way to do it.

Thanks again.
Jean-Guy Marcil - 29 Dec 2005 18:06 GMT
John G. was telling us:
John G. nous racontait que :

> No the signature lines should only appear at the bottom of the page.
> If the signature spaces require two lines to fit, this would be ok. I
> just need it to have spaces for each title selected from the listbox,
> so that when printed people know where to sign their names.

This can be complicated.... What do you mean by "bottom of the page"?
Is this a multiple-page document or a one-page document? (Bottom of page one
or bottom of the last page?)

Do you mean always at the same location (regardless of the amount of text
preceding?) or just next to the last paragraph?

Is this a locked-down form?

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org 

John Guderian - 29 Dec 2005 19:25 GMT
It could be multi-page document, depending on how long the policy/procedure
is. This document contains a header that is created on each new page. There
is also a table that has been defined for the text of the policy/procedure
to be entered within. This table continues to exten with each carriage
return. A press of the tab button will create a new rom. There shouldnt be
any text outside the table.

Based on that, I would say the signature should be below the table that
people are entering text into.

No this isnt a locked down form. Im not really familiar whith the protection
options, so at this point its not locked down at all.

Thanks.

> John G. was telling us:
> John G. nous racontait que :
[quoted text clipped - 12 lines]
>
> Is this a locked-down form?
Jean-Guy Marcil - 31 Dec 2005 15:55 GMT
John Guderian was telling us:
John Guderian nous racontait que :

> It could be multi-page document, depending on how long the
> policy/procedure is. This document contains a header that is created
[quoted text clipped - 5 lines]
> Based on that, I would say the signature should be below the table
> that people are entering text into.

OK, this is going to take some work on your part to figure out exactly where
to place the lines, the length these line should have and to create a loop
or a counter that will insert as many line as needed. Here is some code to
get you going. Repetitive stuff should be in a loop based on the number of
selections in the listbox. Every time you get to a multiple of three, add a
paragraph mark to create a new line of three signature.  Also complicated is
to make sure that all this stuff stays together. You may want to turn on
Keep with next while you add the tab stops.

Another alternative would be to add an autotext (See my previous post for
the code) that has the lay out of the first three signature in a table form
(4 column and two rows)Then, underline the second, third or fourth cell of
the first row (depending on how many signature you need) and then add the
appropriate name in the second, third or fourth cell of the second row. If
needed (more than 4 or 6 signatures, add pairs of rows to this table as
needed.

Either way, you have some work to do!

Dim myRange As Range

Set myRange = ActiveDocument.Tables(1).Range

With myRange
   .Collapse wdCollapseEnd

   With .Paragraphs(1).TabStops
       .ClearAll
       .Add InchesToPoints(2), wdAlignTabCenter
       .Add InchesToPoints(4), wdAlignTabCenter
       .Add InchesToPoints(6), wdAlignTabCenter
   End With

   .InsertAfter "To be Approuved by: "
   .InsertAfter vbTab
   .InsertAfter "_______________"
   .InsertAfter vbTab
   .InsertAfter "_______________"
   .InsertAfter vbTab
   .InsertAfter "_______________"
   .InsertParagraphAfter
   .InsertAfter vbTab
   .InsertAfter "John Smith"
   .InsertAfter vbTab
   .InsertAfter "Mary Marty"
   .InsertAfter vbTab
   .InsertAfter "Bill Boone"
End With

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org 

 
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.