Templates verses Forms/Document

[Merkava]

I am sorry that my terminology is off.

I have created a template ( why, I don't know). I want people to do the following

1) open it.
2)fill out the fields
3) Press a command button, that will save it to a different location (after the form has been checked to see if all the required fields are completed)
4) Close the original.
5) not change the original.

Please advise as to the best way about this. I am a VB programmer trying set up a word form.

Cheers,
Aaron

[TonyJollans]

A Word Template (a .dot file) is the correct 'tool'. Double clicking on a template then causes a new document based on the template (essentially a copy of the template) to be created and leaves the template itself unchanged.

Do you have the Form set up or do you need any specific help with it?

[Merkava]

Hi Tony,

I have made a form, but there are several problems:

1) populating Comboboxes, my VB training says "use an array when the form loads you populate the combobox. This does not seem appropriate here. I'm not using a DB. My main goal is that there be little "intervention" from the user.

2) When I save the form to a new location (without changing the template), I want to use VBA to use the NETSEND command to notify the appropriate person that they have a form to tend to.

I think that these to "hurdles" are large enough for now.

TIA

Cheers,
Aaron

[TonyJollans]

Hi Aaron,

There are two types of controls you can use in Word.

If you want to have what is called a Word Form (I'm not sure it really has a proper name) which 'works' when the document is 'protected for Forms' you should use controls from the Forms Toolbar. This has a dropdown control but it is limited to a maximum of 25 entries.

You can also use ActiveX controls from the Control Toolbox. These don't sit very well with Forms Controls, they are harder to control but, ultimately I suppose, more powerful.

I would recommend the Forms controls for what I imagine you are doing. It doesn't have a command button but you can have code which traps the Save or SaveAs commands to do what you want. Create a macro called FileSave and/or one called FileSaveAs and they will run instead of the builtin commands when the menu options are chosen. Alternatively you could add your own toolbar - or your own button on an existing toolbar - to run the code you want to.

[Merkava]

Is there anywhere I could see an example of what you are referring to? I am aware of the Form controls, but am even more at a loss how Read them from code and work with them.

Cheers,
Aaron

[fumei]

ActiveX comboboxes require code to populate them. Normally these would be done using the Document_Open event (although you certainly could use an other event), so the combobox is populated from the start. Something like:Private Sub Document_Open()
ComboBox1.AddItem "First item"
ComboBox1.AddItem "Second item"
ComboBox1.AddItem "Third item"
ComboBox1.ListIndex = 0
End SubIf an ActiveX combobox is not populated by code, it remains empty. Although there is an interesting aspect to this. Run the above code, not by opening the document, but by simply running the procedure. The items are placed into the combobox. Now, comment out the Document_Open procedure. The items are still in the combobox.

Save the file, and open it - the Document_Open event is commented out, so the combobox is NOT populated. And this is true. There will be nothing in the list...EXCEPT the combobox WILL still show "First item" in it. You can drop down the list and it IS empty, but it will show "First item" in it. Apparently, the text of .ListIndex as a property persists, regardless of whether ListCount = 0. This seems strange to me. In any case, it is always a good idea when populating a combobox to use .Clear to explicitly empty it anyways.

FormField comboboxes (actually called DropDown fields) are normally populated manually. That is, the FormField is selected, and with a right click selecting Properties. Each item is then entering into the list.

FormFields can also be populated by code. Sub PopulateDropDown()
With ActiveDocument.FormFields("Dropdown1").DropDown.ListEntries
.Clear
.Add "First item"
.Add "Second item"
.Add "Third item"
' etc
End With
End SubI am not quite following what you are doing. If you are using formfields, there is no intervention by the users. Just populate the formfield.

[Merkava]

Hi,

Here is what is supposed to happen, by the numbers:

1) user opens the template/form
2) user fills in form by choosing predetermined options from combo boxes, and filling in several text boxes.
3) user presses a "Save" button, the required fields are then "checked" to see if they have been filled in.
4) if everything is verified the form is save to a different location, a system command(Netsend) will notifiy the recepient,and the template is closed unchanged.
5) if the required fields are not filled in there will be a message will apear to instruct user to complete the form and steps 4 and 5 will be repeated.

I hope that this clarifies what I am trying to do.

Cheers,
Aaron

[Merkava]

Hi,

Here is the code that I am using

Private Sub cmdFinancial_Click()
ActiveDocument.SaveAs "u:\Miscellaneous\Escalations\Callbacks - SV review\Financial\test.doc", wdFormatText
Shell ("c:\windows\system32\net.exe send alonb ""Alon, a new Financial escalation has arrived.""")
Shell ("c:\windows\system32\net.exe send badery ""Bader, a new Financial escalation has arrived.""")
Shell ("c:\windows\system32\net.exe send garyn ""Gary a new Financial escalation has arrived.""")
Shell ("c:\windows\system32\net.exe send yaacovp ""Jacob, a new Financial escalation has arrived.""")
Shell ("c:\windows\system32\net.exe send martynf ""Martyn, a new Financial escalation has arrived.""")
ActiveDocument.Close
End Sub

Private Sub cmdSpiral_Click()
ActiveDocument.SaveAs "u:\Miscellaneous\Escalations\Callbacks - SV review\Spiral\test.doc", wdFormatText
Shell ("c:\windows\system32\net.exe send alonb ""Alon, a new Spiral escalation has arrived.""")
Shell ("c:\windows\system32\net.exe send badery ""Bader, a new Spiral escalation has arrived.""")
Shell ("c:\windows\system32\net.exe send garyn ""Gary a new Spiral escalation has arrived.""")
Shell ("c:\windows\system32\net.exe send yaacovp ""Jacob, a new Spiral escalation has arrived.""")
Shell ("c:\windows\system32\net.exe send martynf ""Martyn, a new Spiral escalation has arrived.""")
ActiveDocument.Close
End Sub

Private Sub cmdTechnical_Click()
ActiveDocument.SaveAs "u:\Miscellaneous\Escalations\Callbacks - SV review\Technical\test.doc", wdFormatText
Shell ("c:\windows\system32\net.exe send alonb ""Alon, a new Technical escalation has arrived.""")
Shell ("c:\windows\system32\net.exe send badery ""Bader, a new Technical escalation has arrived.""")
Shell ("c:\windows\system32\net.exe send garyn ""Gary a new Technical escalation has arrived.""")
Shell ("c:\windows\system32\net.exe send yaacovp ""Jacob, a new Technical escalation has arrived.""")
Shell ("c:\windows\system32\net.exe send martynf ""Martyn, a new Technical escalation has arrived.""")
ActiveDocument.Close
End Sub



It ran once then stopped.

When I click on the template it opens the new document, but the code is not included.

I am totally bewildered here.:mkay

Cheers,
Aaron

[fumei]

I am sorry, but you have to really get the details to us.


When I click on the template it opens the new document, but the code is not included.What does click on the template mean? Right now, this phrase means nothing to me. You click on it in Windows Explorer? You click on, as a icon on the desktop?

What do you mean the code is not included?

Look, a template (properly used) is cloned. That is, a COPY of it is made into a new document. This copy will include all code that is in the original template.

You mentioned that the template is closed unchanged. If this is what you are doing, then this is not a template being used properly. If a template is used correctly, the template file itself is NEVER OPENED, and therefore is NEVER changed.

You must tell us, again, what you meaning by "form". Normally things with procedures using cmdWhatver, means a userform. That is, a dialog created in VBA that is displayed to the user.

Now, it IS possible to insert commandbuttons (ActiveX commandbuttons) right in the document itself. If this is what you are doing - then please say so.

However, your Step 3 about clicking a Save button certainly makes me think you are displaying a userform. You have not stated this, and I wish you would say so, one way or the other.

Again, properly used, a template file (.DOT) makes a clone of itself, including any code. The original is not touched.

[fumei]

Oh, and could you please use the underscore character (_) in your code? This makes the display in the code window here easier to read.

[Merkava]

Hi,

I have created a template, whatever.dot, in this template I have a table with several controls on it such as command buttons. When I look at the Icon in windows explorer I click on the template icon that represents the template that I have created. A document is then opened.

I then press a command button that I have added to the template, and nothing happens.

When I was working on the template, I ran the "template" and the buttons worked.

If it would help I am quite willing to proved the template.

T.I.A.

Cheers,
Aaron

[fumei]

Sure, post the document.

OK, you have cleared things up a bit. You have a .DOT file with an ActiveX commandbutton in it. Correct? That is, the commandbutton was placed in the document using the Controls toolbox.

You right clicked the commandbutton, and put whatever code you wanted.

You saved the file as a .DOT file.

You double click the .DOT file, in Windows Explorer, and you get a new document.

Now....you are saying that the commandbutton does NOT work. Correct?

I can not duplicate this. I just did all of the above steps...and the commandbutton works in the new document.

[Merkava]

Hi,

I have attached the dot in the zip file. Any assistance would be greatly appreciated. Like I've said thios is my first venture into the wonderful world of Word programming. It worked once then basically died.

I am probably doing something stupid

Cheers,
Aaron

[fumei]

1. Uh...did you really think that:Private Sub cmdTechnical_Click()
ActiveDocument.SaveAs "u:\Miscellaneous\Escalations\Callbacks - " & _
"SV review\Technical\test.doc", wdFormatText
' etc etc
would work for me. I don't have u:\blah blah blah on my machine.

Next time, think about this and clear out, or comment things that you know are not applicable.

So. I changed:Private Sub cmdTechnical_Click()
ActiveDocument.SaveAs "u:\Miscellaneous\Escalations\Callbacks - " & _
"SV review\Technical\test.doc", wdFormatText
Shell ("c:\windows\system32\net.exe send alonb _
""Alon, a new Technical escalation has arrived.""")
Shell ("c:\windows\system32\net.exe send badery _
""Bader, a new Technical escalation has arrived.""")
Shell ("c:\windows\system32\net.exe send garyn _
""Gary a new Technical escalation has arrived.""")
Shell ("c:\windows\system32\net.exe send _
yaacovp ""Jacob, a new Technical escalation has arrived.""")
Shell ("c:\windows\system32\net.exe send _
martynf ""Martyn, a new Technical escalation has arrived.""")

ActiveDocument.Close
End SubtoPrivate Sub cmdTechnical_Click()
MsgBox "cmdTechnical works."
End Suband...guess what? They all work.

I unzipped your file .DOT. Double clicked it in Explorer. It created a new document - and once I changed the instructions that will not work on my machine - I don't HAVE a u: drive - the commandbuttons work.

What does that tell you?? Hmmmm?

Maybe YOU should try the same thing? Comment out your net.exe send lines. Have a message box like mine - or something else if you prefer. Try that. I am willing to bet that, as I have said all along...your commandbuttons are working. If it is NOT doing what you think it should...it is not the commandbuttons fault.

[fumei]

PS...please note my use of the underscore character to make the code lines shorter.

[Merkava]

Hi,

I realize that you probably don't have that particular drive setup. I also can have a message box pop up whenever I need one to, but that is not what I am trying to do.

I left the entire code in so that maybe you could possibly see if the VB code that I was using was causing some kind of problem. And trust me I was never blaming the buttons, I have always blamed the code that I was using.

Cheers,
Aaron

[fumei]

Not blaming the buttons? Hmmm, I do not think that is accurate.

OK. Look at your code and please tell us what IS happening, because you ARE stating that nothing is happening...and I don't believe you.

Your code does THREE things.

1. perform a SaveAs
2. do a bunch of net.exe send stuff
3. close the document

This is a Word VBA forum. So - you tell me...

1. Is the document SavedAs? If yes, then, #1 worked.


If your net.exe is not working correctly...then it is net.exe making your problem isn't it? If nothing at all is happening - which i doubt - then there is a parsing error as VBA tries to parse the code. The reason I doubt it is that.....

wait a second....you DO have Option Explicit in your code module, right?

Anyway, sorry, but this is not - as far as I can see - a Word, or a VBA problem. Your Shell statements are likely incorrect.

Your original post, regarding filling out a document etc etc etc, are very do-able. If you want to get back to that, then fine. But issue sure does not seem to be anything to do with a template, or no code being in the new document, or the commandbuttons.

Check your syntax for net.exe. I can't help you there. I have no idea what the syntax might be.

[Merkava]

Hi,

What I will do is start again and go step by step, once each step works I will go to the next step. I will keep you informed of the progress of this.

When it is finished, I will want to protect the template. Do I use:

1)tracked changes
2)comments
3)Forms

Cheers,
Aaron

[TonyJollans]

As Gerry has already said there is nothing wrong with your code - as far as it goes. It is difficult to advise without knowing a bit more about the final process. How do you intend to populate the combo boxes, for example?

ActiveX controls don't lend themselves to document protection - quite the reverse in fact. If you want protection, use Form controls - textboxes are straightforward, dropdowns are limited to 25 entries, and yiou don't have buttons; you can, however, create your own toolbar which will serve the same purpose.

I have very quickly taken your template and changed it to a Word Form with a toolbar and attached it here - see if it helps.

[Merkava]

Thanks Tony,

This is great, if I am using form controls I can populate the controls the in advance (just as you have done here). This actually makes the form "lighter" since I don't need arrays and all the additional code. The question is, how do I "read" the controls to see what was chosen. For example; On the 'approved by' drop down I chose Margaret Thatcher. When the user presses one of the buttons on the toolbar, how do I retrieve the name 'Margaret Thatcher' and put it into a variable in the code. So I could do something like this:

strname= 'approved by drop down'
Shell ("c:\windows\system32\net.exe send" & strname & ""a new Financial form has arrived.""")

Basically the entire proccess can be narrowed down to the following:
User End
=================================
1) User fills in form
2) User clicks on button to "send" the form

Form
=================================
Once button is pressed
1) required fields are validated **
2) form is saved to new location
3) message is sent to receipient
4) form is closed

**If not validated the form won't be sent

Cheers,
Aaron

[TonyJollans]

Hi Aaron,

For a dropdown you should be able to use
ActiveDocument.FormFields("DropdownBookmarkName").Result
to get the string, or
ActiveDocument.FormFields("DropdownBookmarkName").Dropdown.Value
to get the number in the list, which you can use as an index into
ActiveDocument.FormFields("DropdownBookmarkName").Dropdown.ListEntries

For a text field, for some odd reason, you are better off using
ActiveDocument.Bookmarks("TextFieldBookmarkName").Fields(1).Result

[fumei]

1. Validation is a good thing. You will have to build up solid logic for it, since you have so many fields.

2. To help with that, I would strongly recommend explicitly naming your formfields.

DropDown8 - really tells you nothing
ApprovedBy - does.

What I am saying is:ActiveDocument.FormFields("ApprovedBy").Result is much easier to follow through your code, than:ActiveDocument.FormFields("DropDown8").Result

Again, since you are talking about validation, having explicit names will likely make it easier.

As another suggestion, make a FormField object of the document formfield collection. You will have to type a lot less. Right now you have to type:ActiveDocument.FormFields("DropDown8").ResultIf you make a FormField object, it will contain all the formfields in the document. Then you can reference them using the object, like this:Dim oFF As Word.FormFields
Dim strApprovedBy As String
Set oFF = ActiveDocument.FormFields
strApprovedBy = oFF("DropDown8").Resultwhich would give you a string of the selected item in DropDown8 - which again would be better off explicitly named.

[TonyJollans]

To be fair, it was me who left the default names in the sample I knocked up. You are, of course, right that meaningful names should be used in any proper solution.

[fumei]

Tony, could you elaborate your comment re: text formfields, it is better using Fields(1)?

I get an error.

ActiveDocument.FormFields("Text1").Resultreturns the result.

ActiveDocument.Bookmarks("Text1").Fields(1).Resultreturns an compiling error - "Member not found".

[TonyJollans]

Oops! Sorry, that should be:
ActiveDocument.Bookmarks("Text1").Range.Fields(1).Result
I have to confess that I don't know all the details but there are some situations where referencing text formfields doesn't work properly. One that immediately springs to mind is setting the value to a string longer than 255 characters. Also formatting can sometimes be applied to the field rather than the formfield. As referencing the field always works it is generally a safer recommendation.

[Merkava]

Hi,

First of all, I would like to say thank you for all your assistance. It really helped me. :bow: :bow: :bow:

I would like to ask one more question with regards to the tool bar that was added.

To add or remove buttons, where can I see how this is done?

T.I.A.

Cheers,
Aaron

[TonyJollans]

I can't remember exactly what I did but here is some sample code which should get you on the right road. Drop this in a module in your template

Sub ToolbarSetUp()
Dim myCB As CommandBar
Dim myCBCombo As CommandBarComboBox
Dim myCBButton As CommandBarButton

CustomizationContext = MacroContainer

On Error Resume Next
CommandBars("Toolbar Name").Delete
On Error GoTo 0

Set myCB = CommandBars.Add("Toolbar Name", msoBarTop)
With myCB
Set myCBButton = .Controls.Add(msoControlButton)
With myCBButton
.Style = msoButtonCaption
.Caption = "Button 1 Caption"
.OnAction = "Button1Code"
End With
Set myCBButton = .Controls.Add(msoControlButton)
With myCBButton
.Style = msoButtonCaption
.BeginGroup = True
.Caption = "Button 2 Caption"
.OnAction = "Button2Code"
End With
Set myCBButton = .Controls.Add(msoControlButton)
With myCBButton
.Style = msoButtonCaption
.Caption = "Button 3 Caption"
.OnAction = "Button3Code"
End With
.Enabled = True
.Visible = True
End With
end sub

[Merkava]

Hi,

Actually, it was not done in code.

Cheers,
Aaron

[TonyJollans]

Hi Aaron,

We're obviously talking about different things. The toolbar in my sample was created in code with something similar to what I last posted. What are you referring to?

[Merkava]

I did not see any code that created a toolbar in the example you sent me.

Cheers
Aaron

[TonyJollans]

The code was a one-time thing. Once it had been run, the toolbar was there and the code was not needed in the template any more. As I said, I can't remember exctly what I did but it would have been similar to the code I just posted.

You can create toolbars manually via Tools > Customize. Create the toolbar from the Toolbars tab and add the buttons from the Commands tab.