HELP: Whats Possible?

[thomas.szwed]

Hi,

Wondering if anyone can help me out here
:dunno . I have a designed a word document that has several bookmarks which are populated via a userform. Is there some code out there that would do the following;

When in Dropdown A, Option 1 is selected. It adds in the relevant information into bookmarks a,b and c on the word doc?

And then again when Option 2 is selected, different relevant information would be added into the same bookmarks?

Where would i reference all this information? The scope of this is going to be rather large as there is lots of data that will need to be referenced.

Any help would be much appreciated.

Thanks, Tom

[OTWarrior]

you could reference the data in an array, and loop through the array to put the correct data in the correct boxes depending on the criteria.

[thomas.szwed]

That sounds like a good solution. Can you tell me, is an Array defined in the code or can it be stored in something like a spreadsheet?

Thanks in Advance

[OTWarrior]

It can be defined in the code or as an external source:

code:
Data = Array("data1", "data2", "etc")

I have never used it as an external source, but if you do a search for array on this site you may be able to find something.

[macropod]

Hi thomas,

You don't need vba or bookmarks for this. Provided you configure your dropdown formfield to set a bookmark name and to 'calculate on exit', the lot can be done with field coding.

If, for example, all you want to do is to replicate the dropdown's text, you only need to insert a cross-reference to its bookmark.

OTOH, if you want to generate a different response, you could select the position in the document where you want the text to go and input a field coded like:
{IF{Ref Dropdown1}= "This String" "Have a nice day" "May your sox rot!"}
where 'Dropdown1' is the dropdown field's bookmark name and the field braces (ie '{}') are created in pairs via Ctrl-F9.

One advantage of using field coding is that its really easy to insert formatted text, multiple paragraphs in different styles, and even page breaks, if you need to. Plus there's no vba security issues or the risk of you users disabling macros that you might otherwise have to deal with.

[thomas.szwed]

Thanks for all your help. Both solutions seem feasible but the trouble is i have so much data. My example is i am designing a system to produce contracts. As you can imagine there is a huge variety of wording and layouts driven by the choices the user makes on the userform.

TaKe a look at the spreadsheet attached. Here i have all the locations where employees could work. For the Place of Work section of the contract, each site has different wording. So when the user picks the site from the dropdown i am going to need this to change the wording in the contract. Unfortunately almost dropwdown on my userform will have this effect so can you see how complex this will be? In Excel i used just row of data for every site, and in that row are all the specifics for that site.

Can you advise me futher? Thanks in Advance

[OTWarrior]

Is this the kind of thing you are after?

NB: I have only put in the code for the first 3 options, just so you can see if it is suitable.

[thomas.szwed]

Thanks for your reply. I think the array is the key here but not quite like you have demonstrated. I need the array to be sort of referenced somewhere like in a spreadsheet so it can then be loaded into memory. Basically in the current spreadsheet i have a huge data sheet that has every single site and all of its specifics.
Therefore ideally, when i choose the site in the dropdown on the userform i need all of the bookmarks to display all of the specifics of that site. There are as many as 20 for each site and I have the choice of almost 20 sites! Surely there must be an easier way of doing this rather than coding each specifc with its own IF statements and strings etc..............?

I would love to show you the current excel version, but cant as data is highly confidential.

Any guidlines for me would be appreciated........

[OTWarrior]

I don't know how to use an external source as an array, but if you were able to have an excel document as the source, then you could easily make it dynamic by referencing the cells by row and column.

Yes coding for 20 different lines of data is time consuming (which is why I gave up after 3) but if they all have to be unqiue then that can be the easiest way without writing tons of code. I do think using a data source for the values (as you have said) would be the simpliest solution...we just have to work out how to call that data in.

I have seen this thread which talks about external arrays from a file:
http://www.vbaexpress.com/forum/showthread.php?t=19230&highlight=array

[macropod]

Hi Thomas,

If you've already got all your data in a workbook, its quite possible for Word to retrieve the employee data from it, given, say, a city name. Instead of using formfields, though, you'd employ a userform, with which you could query the Excel workbook for the city name array. Even with a userform though, a large array of cities could be slow to scroll through and select from, so you might want to give your users a choice of country (eg England, Sotland, Wales, Nthn Ireland) and/or region to select from first, thus limiting the number of cities they'd have to choose from.

If you don't have the data all nicely organised, then there's probably little difference whether you assemble in in a macro or build a set of fields in your document to process each record. Just a few examples from the data in your Excel file:
{IF{REF DropDown1}= "Castleford" "Your position is based at Castleford"}{IF{REF DropDown1}= "Field Sales" "Your position is field based"}
{IF{REF DropDown1}= "Castleford" "Your normal hours are"}{IF{REF DropDown1}= "Cuddington" "Your normal hours over a full year average"}
From the examples you posted, there are very few differences in output. The real issue is that, if you've got more than 25 cities to deal with, then formfields probably aren't going to do the job, regardless of what can be done with normal fields.

[thomas.szwed]

Thanks for this reply Macropod. Pls take a look at what i have began to code. This works nicely for fields that dont have any conditional statements but the majority of fields i need to add to the userform will have.

There are only 5 sites in the list but i need to add another 20. Each site will have about 10 knock on effects to the text in the contract (which by the way isnt finished). Have a look at the code and tell me what you think?

Thanks

[macropod]

Hi Thomas,

I don't see any fields in the document, just some text and a bunch of bookmarks that get replaced by the values inserted from your userform. As I understand it, though, your 'issue' is how to deal with the conditional statements. In fact, your code already has with a bunch of conditional statements - the various IF tests.

Some of your IF tests could be made more efficient and easier to maintain by replacing them with a Select Case (see 'Using Select Case Statements' in Word's Help File). Candidates for this treatment include your ddlContractType.Value, and ddlLocation.Value tests.

[thomas.szwed]

Thanks again for your help. I have many IF statements because ideally i would only need one for each site. So for example.

IF user choses site: Croydon THEN
- fill boomark1 with Excel Sheet Cell C5
- fill boomark2 with Excel Sheet Cell D5
- fill boomark3 with Excel Sheet Cell E5

IF user choses site: Cuddington THEN
- fill boomark1 with Excel Sheet Cell C6
- fill boomark2 with Excel Sheet Cell D6
- fill boomark3 with Excel Sheet Cell E6

This would be much easier than having to reference all the possible combinations in the code. Also if the wording changed, then they could simply update the spreadsheet rather than have to edit code.....

Do you see my point? - What would you do?

The next challenging bit is that currently in my spreadsheet application there are further conditional statements in the data sheet. For example if the user choses a site and then choses a paticular paygrade this can change some of the wording as theyre are many IF statements built in? If you dont understand me here i can forward you a copy of it.....

Thanks in advance

[OTWarrior]

just for neatness, you can change:

If ddlContractType.Value = "Permanent" Then
strContractType = "Terms & Conditions of Employment"
End If

If ddlContractType.Value = "Temporary" Then
strContractType = "Terms & Conditions of Temporary Employment"
End If

If ddlContractType.Value = "Fixed Term" Then
strContractType = "Terms & Conditions of Temporary Employment"
End If

If ddlContractType.Value = "No Start Date" Then
strContractType = "DRAFT Terms & Conditions of Employment"
End If

to:

If ddlContractType.Value = "Permanent" Then
strContractType = "Terms & Conditions of Employment"
elseIf ddlContractType.Value = "Temporary" Then
strContractType = "Terms & Conditions of Temporary Employment"
elseIf ddlContractType.Value = "Fixed Term" Then
strContractType = "Terms & Conditions of Temporary Employment"
elseIf ddlContractType.Value = "No Start Date" Then
strContractType = "DRAFT Terms & Conditions of Employment"
End If

you could even make it into a case select statement, and group the various parts that have the same output:

Select Case ddlContractType.Value
Case "Permanent"
strContractType = "Terms & Conditions of Employment"
Case "Temporary" Or "Fixed Term"
strContractType = "Terms & Conditions of Temporary Employment"
Case "No Start Date"
strContractType = "DRAFT Terms & Conditions of Employment"
End Select

[OTWarrior]

The next challenging bit is that currently in my spreadsheet application there are further conditional statements in the data sheet. For example if the user choses a site and then choses a paticular paygrade this can change some of the wording as theyre are many IF statements built in? If you dont understand me here i can forward you a copy of it....

The only way I can think of would be to have a couple of sets of case select statements, with goto statements to move through to depending on the criteria, but that could be very messy.

[thomas.szwed]

And we think theres no possible way to reference a spreadsheet? cant really find much applicable in Google

[macropod]

Hi Thomas,

Referencing a spreadhseet is fairly simple - all you need to do is to set your code up to read the appropriate data arrays from it. See, for example:
http://www.j-walk.com/ss/excel/tips/tip82.htm
Although the macro there is intended to be run from within Excel, it could be modified to run from Word.

So far, I can see that you need an array of localities with their corresponding workplaces (I don't know what the numbers in column C are for) and an array of working hour ranges/types. Properly coded, the second array's entries can be constrained by the locality choice from the first array, if that's what you need, without having to reference your workbook. You don't need to refer to the workbook for the values in column D either - a simple if test to see whether the locality name includes the word 'Field', thus allowing you to vary the text accordingly.

[thomas.szwed]

Hi MacroPod. Thats a good link, but the main snag that hits me is that the data sheet isnt static, its dynamic. Therefore the more tweeks the user makes on the form, the more the data is manipulated on the data sheet and ultimately the final result is how it all fits together on the contract sheet. I have included a couple of screenshots here of the current excel app. You can see i am trying to replicate it in Word, because its far better for producing doucments than excel.

notice the data sheet screenshot. I have clicked on a cell which has processing on it. Look at the formula in the formula bar.

For my word application i said it would be easiest to reference a excel sheet because the data can be easily changed there rather than in the code. But if the data is constantly changing this makes the process impossible?

Any help would be appreciated?

[OTWarrior]

Just wondering, but wouldn't this be easier to do in Access? That way you can make it dynamic very easily or the values that change.

I just had another thought. If you are wanting word to be dynamic and use the excel as the source data, why not test to see if each row has a value in it?

for example, you have a set of code that runs on word load that check how many rows are in the excel document, and outputs that number of rows into a value for the code to use. Then you can use this value to bring the data in.

This way you can set this as the end value of the number of rows, and loop through the column that contains the Site name to check if it matches the value of the dropdown on the user form.

Peusdo code time:


for i = 1 to 1000
if range("B" & i).value <> null then
maxrows = maxrows + 1
else
i = 1000
end if
next i

for k = 1 to ubound(maxrows)
if word.userform.dropdown.text = excel.range("B" & k).value then
word.userform.text1 = excel.range("C" & k").value
'repeat for each column
end if
next k


The above peusdo code has alot wrong with it, but hopefully you will understand what I am trying to get at here?

[macropod]

Hi Thomas,

Since you've already developed what I assume is a quite useable input screen in Excel for assembling the employee particulars, it seems to me that all you need to do from there is to have a set of formulae in your Excel workbook to list all the variables you need for your Word document in a common location (eg a single row or column, or a block of cells, then use LINK fields from your Word document pointing to the relevant cells. No vba would be required for any of this. The only vba you might require is for a button on your Excel workbook to open the Word document and update its fields. Trivial.