Create signature lines based off list box selections

[John G.]

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]

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


--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[John G.]

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.]

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]

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.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[John G.]

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]

John G. was telling us:
John G. nous racontait que :

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.

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

Approved by: _____________________ _________________________
________________________
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.

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".

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?

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[John G.]

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]

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?

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[John Guderian]

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.

 

[Jean-Guy Marcil]

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
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.

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



--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org