MS Office Forum / Word / Programming / December 2005
Create signature lines based off list box selections
|
|
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
|
|
|