Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 08-30-2017, 07:36 AM
strodden strodden is offline Windows 10 Office 2010 32bit
Novice
 
Join Date: Aug 2017
Posts: 10
strodden is on a distinguished road
Default Customizing form fill docs with dropdowns

Hello,


I am creating a quoting document for our sales team and would like the document to display different pages depending on which of our company's divisions salespeople select.

For example, perhaps a dropdown containing "Division 1" and "Division 2." If "Division 1" is selected, form fill pages would appear to collect data only required for that division. I.e., pages would remain hidden unless they were needed.

I would prefer that this document is locked for editing once finished if that makes a difference.

Is this possible?


Best,
Seth
Reply With Quote
  #2  
Old 08-31-2017, 05:25 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,300
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

You would do better to use a separate template for each Division.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 09-01-2017, 08:14 AM
strodden strodden is offline Windows 10 Office 2010 32bit
Novice
 
Join Date: Aug 2017
Posts: 10
strodden is on a distinguished road
Default Deleting Pages with macro on protected sheet

My boss wants it this way, though, so that's what I'm exploring!

I have a protected word doc with form fields. Some of the pages will only be necessary under certain conditions, and I would like to make a button which removes unneeded pages depending on the need.

I am BRAND new to VBA and have found this code online:

Sub Clearpages()
Dim rgePages As Range
Dim PageCount As Integer
PageCount = ActiveDocument.ComputeStatistics(wdStatisticPages)
Selection.GoTo What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=2
Set rgePages = Selection.Range
Selection.GoTo What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=PageCount
rgePages.End = Selection.Bookmarks("\Page").Range.End
rgePages.Delete
End Sub

This works until I protect the doc, then stops working. I read on another page that I need to insert the code "Sheetname.Unprotect Password:="password"/"Sheetname.Protect Password:="password"" at the beginning and end of this code, but I can't make that work either.

What am I doing WRONG????


Best,
Seth
Reply With Quote
  #4  
Old 09-01-2017, 12:28 PM
strodden strodden is offline Windows 10 Office 2010 32bit
Novice
 
Join Date: Aug 2017
Posts: 10
strodden is on a distinguished road
Default Trouble getting this to work

I want to do something very similar to the person who started this thread http://www.msofficeforums.com/word-v...own-lists.html. I downloaded the example sheet which worked fine. I tried to copy both of the fields into my document but they won't work.

I assumed this was because the macro had not been copied over with the content controls. When I went to find the macro I don't see where it's located in the example sheet. I click "Developer/Macros" and the list is blank. I opened up the Visual Basic tab but could not interpret what I'm seeing there.

I now get a message "Compile Error: Invalid Outside Procedure" when I try to use the dropdown on the original example sheet, so it appears I've broken it somehow in all this poking around.
Reply With Quote
  #5  
Old 09-01-2017, 03:12 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,300
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

I suggest you download the document again from http://www.msofficeforums.com/word-v...html#post46903 and start over. That said, the technique described there has nothing to do with
Quote:
Originally Posted by strodden View Post
the document to display different pages depending on which of our company's divisions salespeople select.
It is only for varying a small amount of text (no graphics, etc.) on a single page.

PS: I've merged all your posts on this topic into the initial thread: we don't really need to have three current threads on the same topic...
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #6  
Old 09-13-2017, 10:51 AM
strodden strodden is offline Windows 10 Office 2010 32bit
Novice
 
Join Date: Aug 2017
Posts: 10
strodden is on a distinguished road
Default Still working on this

Hi again,
I've picked back up on this and am continuing to try and populate fields based on dropdown content.

I am using the example template posted at the beginning of this thread. I have done the following:

1. On the example template, edited the dropdown content to display my content needed via the Developer tab's "properties=>display name/value" menu.
2. Verified that my changes worked on the example template.
3. Copied the dropdown from the example sheet to my sheet (I believe there was a comment saying this should be possible?)
4. Copied the text field from the example sheet to my sheet.
5. Accessed the Macro code in the example sheet via Alt+F11.
6. Copied macro code.
7. Opened VBA on my sheet via Alt+F11
8. Copied macro code into my sheet

After these steps I selected one of my dropdown values and it did not change the content of the text field as it had before. I tried to trouble shoot by comparing the how the macro looked in the example template vs. mine. As far as I can tell they are identical except for the "Option Explicit" header on the example screen shot, which I don't understand (screen shots attached).

The attachment "Form Screen Cap" shows what I want to do. The dropdown under "Sales Representative" should populate the info in the three circled categories.
Attached Files
File Type: docx Screen shots.docx (349.0 KB, 1 views)
Reply With Quote
  #7  
Old 09-13-2017, 03:09 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,300
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Without actually seeing the problem document, it can be difficult for anyone to diagnose the issue. Screen-shots of your document's content are of no use for this purpose. Can you attach a document to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #8  
Old 09-15-2017, 11:35 AM
strodden strodden is offline Windows 10 Office 2010 32bit
Novice
 
Join Date: Aug 2017
Posts: 10
strodden is on a distinguished road
Default

No problem - here's what I'm working with!
Attached Files
File Type: docm Proposal Sheet sample for MS forum.docm (63.4 KB, 2 views)
Reply With Quote
  #9  
Old 09-15-2017, 05:04 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,300
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

It's not working because you put the ContentControlOnExit macro into an ordinary code module instead of into the document's 'ThisDocument' code module.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #10  
Old 09-18-2017, 05:42 AM
strodden strodden is offline Windows 10 Office 2010 32bit
Novice
 
Join Date: Aug 2017
Posts: 10
strodden is on a distinguished road
Default

Thanks for diagnosing. How should I fix this? I see that I have two "ThisDocument" areas in the management tree, one under the bold "Normal" heading and another under the heading "Project (document name)." I attempted to clink on both of them and insert a module in the correct place using the "Insert" tab - no luck. VBA novice here eager to learn.


Best,
Seth
Reply With Quote
  #11  
Old 09-18-2017, 01:14 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,300
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

You put the code into the 'ThisDocument' code module of the document containing the content controls, just as it is in the demo you downloaded.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #12  
Old 09-20-2017, 06:03 AM
strodden strodden is offline Windows 10 Office 2010 32bit
Novice
 
Join Date: Aug 2017
Posts: 10
strodden is on a distinguished road
Default

Wow - I was making that way too hard - thanks.

I think my last question is: how difficult would it be to cause my dropdown list to populate three separate fields? I.e., one for phone number, one for fax and one for email. The issue now is that since all of my info is listed in one "Value" box, the text wrapping causes the text to flow down, rather than to the side - just looks bad.

An acceptable workaround would also be to somehow modify the way this one "ClientDetails" box wraps, so I get one long skinny text line extending to the right rather than a block of text going down.


Best,
Seth
Reply With Quote
  #13  
Old 09-20-2017, 03:14 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,300
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Using the original example from: http://www.msofficeforums.com/word-v...own-lists.html
you might add separate content controls named 'Phone', 'Fax' & 'Email', then use code like:
Code:
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim i As Long
With ContentControl
  If .Title = "Client" Then
    For i = 1 To .DropdownListEntries.Count
      If .DropdownListEntries(i).Text = .Range.Text Then
        ActiveDocument.SelectContentControlsByTitle("Phone")(1).Range.Text = Split(.DropdownListEntries(i).Value, "|")(0)
        ActiveDocument.SelectContentControlsByTitle("Fax")(1).Range.Text = Split(.DropdownListEntries(i).Value, "|")(1)
        ActiveDocument.SelectContentControlsByTitle("Email")(1).Range.Text = Split(.DropdownListEntries(i).Value, "|")(2)
        Exit For
      End If
    Next
  End If
End With
End Sub
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #14  
Old 09-22-2017, 06:35 AM
strodden strodden is offline Windows 10 Office 2010 32bit
Novice
 
Join Date: Aug 2017
Posts: 10
strodden is on a distinguished road
Default

Thanks - this worked perfectly! Looking forward to getting better at this so I can contribute rather than just take from this forum
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to populate dependent dropdowns and auto-fill text fields simultaneously? vera Word VBA 1 10-07-2016 07:41 PM
Fill MS-Word docs/forms with name address data from external source yajnaval Word 2 02-14-2015 10:48 PM
Problem with customizing outlook 2013 contact form aroshlakshan Outlook 0 02-17-2013 02:58 AM
Help with fill in form RetiredFD Word 1 03-18-2012 12:52 AM
Customizing the default contact form in Outlook 2010 Jackissimus Outlook 5 03-24-2011 02:33 PM


All times are GMT -7. The time now is 07:03 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft