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.

[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

[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

[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?