Field codes to automate document - may need VBA

[NIA2]

Hi everyone,

I'm trying to automate a proposal document by using field codes. I'm basically using the Fillin field, eg. {FILLIN "ENTER THE NAME OF THE PROPOSAL" \* MERGEFORMAT} to prompt the user for information. If this information is used elsewhere in the document, I've inserted {REF Name_of_Proposal } at each particular point. I also bookmarked the name of the fields that would be populating the other fields as described above, in order to get it working.

The problem is that there's a page in the document where there's a table with two columns - milestone and date. As part of the automation process, I need to create a fillin box for both the milestone and date cells. This is no problem as it'd just work like all the other fillins and the information would populate the cell under "milestone" and the cell under "date". The thing is that there may be more than one row in the table, ie. a necessity to create more than one milestone and date pair. I thought about creating separate fillins labelled milestone1, date1, then milestone2, date2, but I don't know in advance how many of these rows there will be - in fact it will vary for each proposal. So I wondered if someone could tell me if there's any field codes I could use to allow the user to enter all the information needed in the one fillin box but have the ability to somehow separate the different milestone/date pairs that would then get populated consecutively into different table rows. Is this beyond the capabilities of field codes or would I have to use VBA to accomplish this? If VBA is required can someone tell me where I could get some code that would do this, as my knowledge of VBA is very limited.

My other issue is that I'd like a message box to pop-up before a particular fillin dialogue appears. This is because there's a section in the proposal where helpful instructions need to be given to the user before filling in the dialogue. Is there a way to incorporate such a pop-up and customise it so that it appears in the correct place? Would VBA be needed for this also?

Appreciate any help offered.

 

[macropod]

Hi gwh2,

This is probably best done with a document using formfields protected for forms. The immediate benefit this gives you is that any formfields that are filled in can be set to automatically update the other fields that cross-reference them. The second benefit is that formfields can be set to run a macro on entry, or on exit. Such a amcro could be coded to create a table with the required number of rows (or to add/delete rows), along with whatever fields you might also need in the cells. This can be fairly complicated, but is certainly do-able. It can't be done with field coding alone.

The potential downside is that documents protected for forms limit users' abilities to make a wide range of edits to the document - even in Sections that aren't protected.

Cheers

[MS MVP - Word]

 

[NIA2]

Thank for the reply,

I think the people I'm creating the document for want some sort of dialogue box where they can enter the information. I thought that using a multi-page VBA Userform might accomplish this. Is it possible to solve my table problem as described, using this userform? If so, can you briefly outline how I'd go about it, what code I'd need etc.

Thanks for any continued help.

 

[macropod]

Hi gwh2,

Yes, you could use a UserForm instead of formfields. For some help with userforms, see:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

Cheers

[MS MVP - Word]

 

[NIA2]

Thanks again for the reply and link,

I've tried to get my userform started, but unfortunately as this is my first try at VBA coding I'm getting a couple of run-time errors when I test my code. To begin with, I've basically just set up a multi-page form with two input text boxes on the first page and about 7 of the boxes on the second page. I'm trying to see if I can get the information into the Word document via the bookmarks, then once I get it right I'll tackle the bigger problem of inserting the information into a table, so I wondered if you could help me troubleshoot at this stage. I receive the following error when I test and try and click the 'clear' button:

Runtime error '424' object required

...and I'm getting this error when I try to press the "Ok" button:

Run-time error '5941' The requested member of the collection does not exist.

Can you see any obvious errors in my code that would cause these problems?

Private Sub cmdClear_Click()
     txtClient_Company_Name.Value = Null
     txtClient_Contact_Name.Value = Null
     txtClient_Position.Value = Null
     txtClient_Title_Description.Value = Null
     txtCompany_Description.Value = Null
     txtCompany_PM_Name.Value = Null
     txtCompany_Title_Description.Value = Null
     txtName_of_Proposal.Value = Null
      txtCustomer_Name.Value = Null
End Sub



Private Sub cmdOk_Click()
    Application.ScreenUpdating = False
    With ActiveDocument
            .Bookmarks("Client_Company_Name").Range.Text = txtClient_Company_Name.Value
            .Bookmarks("Client_Contact_Name").Range.Text = txtClient_Contact_Name.Value
            .Bookmarks("Client_Position").Range.Text = txtClient_Position.Value
            .Bookmarks("Client_Title_Description").Range.Text = txtClient_Title_Description.Value
            .Bookmarks("Company_Description").Range.Text = txtCompany_Description.Value
            .Bookmarks("Company_PM_Name").Range.Text = txtCompany_PM_Name.Value
            .Bookmarks("Energetics_Title_Description").Range.Text = txtEnergetics_Title_Description.Value
            .Bookmarks("Name_of_Proposal").Range.Text = txtName_of_Proposal.Value
            .Bookmarks("Customer_Name").Range.Text = txtCustomer_Name.Value
    End With
    Application.ScreenUpdating = True
    Unload Me
End Sub

Private Sub cmdCancel_Click()
    Unload Me
    ActiveDocument.Close SaveChanges:=False
End Sub

 

[fumei]

1. Are you using Option Explicit? If no, do so.

2. The errors mean exactly what they say. For the Clear procedure I suspect you have made an incorrect spelling for one of the textboxes. Again - use Option Explicit. If there is even the slightest difference, then an instruction will error out as...there is no object by that name.

As for the OK, I suspect you had this happen the second time you tried it. Your code, as it stands, will indeed put the text at the bookmark, but it deletes the bookmark. Therefore, if you try it again, the bookmark does not exist. Either that, or again, you have a mis-spelling.

faq219-2884

Gerry
My paintings and sculpture

 

[NIA2]

Thanks for the reply,

I've inserted Option Explicit at the top of the module as you suggested. I tested again, entering some information into the text boxes, then pressed clear. This time I'm getting the following error:

Compile Error
Variable not defined

...and then it highlights this line in the code:

Private Sub cmdClear_Click()

I get the same error when I press the ok button but it highlights this code:

Private Sub cmdOK_Click()

I checked that all my text boxes were named correctly and they are.

Do you know what might be happening?

 

[NIA2]

This is my current code if it helps at all to help troubleshoot:

Option Explicit

Private Sub cmdClear_Click()
     txtClient_Company_Name.Value = Null
     txtClient_Contact_Name.Value = Null
     txtClient_Title_Description.Value = Null
     txtEnergetics_Title_Description.Value = Null
     txtEnergetics_PM_Name.Value = Null
     txtName_of_Proposal.Value = Null
      txtCustomer_Name.Value = Null
End Sub



Private Sub cmdOK_Click()
    Application.ScreenUpdating = False
    With ActiveDocument
            .Bookmarks("Client_Company_Name").Range.Text = txtClient_Company_Name.Value
            .Bookmarks("Client_Contact_Name").Range.Text = txtClient_Contact_Name.Value
            .Bookmarks("Client_Title_Description").Range.Text = txtClient_Title_Description.Value
            .Bookmarks("Energetics_PM_Name").Range.Text = txtEnergetics_PM_Name.Value
            .Bookmarks("Energetics_Title_Description").Range.Text = txtEnergetics_Title_Description.Value
            .Bookmarks("Name_of_Proposal").Range.Text = txtName_of_Proposal.Value
            .Bookmarks("Customer_Name").Range.Text = txtCustomer_Name.Value
        End With
    Application.ScreenUpdating = True
    Unload Me
End Sub

Private Sub cmdCancel_Click()
    Unload Me
    ActiveDocument.Close SaveChanges:=False
End Sub

 

[NIA2]

Sorry, also forgot to mention that at the moment there are two pages in the multi-page form and I've put the "clear", "OK" and "Cancel" buttons on the second page (they'll eventually go on whichever is the last page when I add more pages), so I wondered if this had anything to do with the error.

 

[WalkerEvans]

You stated

I don't know in advance how many of these rows there will be - in fact it will vary for each proposal

This might not be a real problem at all, if the users know. If they do all you need to do is add another input where they can enter this information, and then use that data as a variable for the number of table rows to create.


For whatever its worth, I agree with Macropod that doing all of this via Formfields in a Protected document would likely be the best way to handle this. If you set up informational text immediately before each field your users probably wouldn't complain - they probably wouldn't even know the difference! Once the document was protected "for forms" they would have the text to read (but not change) and the formfield right there in which to enter data. Doing this would give you something like this:

"Pleae enter the Number of Widgets
for this order, and the date on
which this order was received"
Number: [highlight][ ][/highlight] Date: [highlight][ ][/highlight]

This should work at least as well as using dialogue boxes, and as a side benefit it cuts down on the number of chances for input error.

----------------------------------------------------------------------------------
[small]"Did you hear about the guy who refused to pay his exorcist?[/small]
He was repossessed."

 

[NIA2]

Yeah you're right about the user knowing, but I was still hoping to use userforms as I've already told the people I'm setting it up for that's what I was going to do.

I just really need help troubleshooting the code I currently have, as detailed in my previous post.

Anyone have any idea what's going on?

 

[BrianLW]

Are your buttons named cmdOK and cmdClear? Sorry to sound stupid, but I have made that mistake in the past.

-Brian

 

[BrianLW]

Also the CTRL+Space (Intellisense) shortcut works in VBA. That might be worth a try.

 

[NIA2]

Yeah I've just double checked on the buttons and the names are correct.

Not sure what the CTRL+Space shortcut is - how can it help?

 

[BrianLW]

It is the intellisense short cut. You place the cursor in the name and press CTRL+Space and the correct name will show up if the incorrect name was used. Also, try vbNull instead of Null.

 

[NIA2]

I'm working on a mac computer so I don't think the intellisense works on this platform.

I also just tried vbNull, then when I tested via the compile project command under debug, I got the error again:

Compile error: variable not defined

...then it highlighted this code:

    txtClient_Company_Name.Value = vbNull

I don't know what's going on.

 

[BrianLW]

What about using .text instead of .value?

 

[PHV]

What happens if you replace this:
txtClient_Company_Name.Value = Null
with this ?
txtClient_Company_Name.Text = ""

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NIA2]

I just tried both suggestions and I'm still getting the same compile error, however I should have mentioned that it's highlight only this part of the code:

     txtClient_Company_Name.

not:

     txtClient_Company_Name.Value = Null

If the error is: Compile error: variable not defined - I don't understand why it's highlighting that particular bit of code. I mean I know for a fact that I have a text box with that name.

 

[PHV]

And this ?
Me.txtClient_Company_Name.Text = ""

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886