Solved: User form combo

[Tilly1998]

Hi everybody this is my first post.

My boss has asked me to change our company letterheading. At the moment it is a basic word template showing company name etc but he wants it changed to show each manager and their respective telephone number so i was wondering how to go about it really i was thinking about a user form that opens when you do Document New and you select the name from a drop down box which fills a text box with the phone number? Im new to programming, apart from macros so forgive me if this is a really easy question!

any help would be greatly appreciated
regards

[lucas]

Hi Tilly,
I would use bookmarks. See attached for an example and post back here with any questions you might have. This example does more than a combobox but I think it is well enough commented to make it clear how it works.

[Tilly1998]

Thank you for your quick reply. please dont curse me but im not sure exactly how to use it (sorry). to use your example, do i fill out the managers names and their corresponding direct dial telephone number on the table and when I select the name from the user form it will give the correct telephone number??

Again, im really sorry to sound so dim but i really like to understand something rather than just copy it blindly

Do you want me to send in a copy of the letterheading??

all the best

[lucas]

No this is a simple example of how to get data from the userform into the document. Go to insert bookmark on the main menu to see where the bookmarks are located in the document.

The code is pretty well documented and it shows how to get data from textboxes and a combobox and put that info into specific bookmarks on the page....read the comments in the code and post here again if you are still having problems. I may have made it a little too complicated to start because this is set up to handle several texboxes or comboboxes at one time for you.

[fumei]

i was thinking about a user form that opens when you do Document New and you select the name from a drop down box which fills a text box with the phone number?Yes, that is the way to do it.

The example that Steve (lucas) posted though has no data. It will put the contents of the userform into the bookmarks...but there is NO data.

Here is another version where I put some dummy data in.

The question is:

Where is your data?

[fumei]

Let me see if I can phrase it better. You mention "correct" phone number.

OK. Where is the data that makes it correct? If I understand correctly, you want to be able to select a manager, and have something fill out information (data). This can be done, and is done in my example, but you have to HAVE that data. I put in dummy data.

You select Harry Belefonte as the Manager and it puts in

Name: Harry Belefonte
Phone: 1-Jamaica
Address: heaven

Putting in data from selecting a manager name is easy, but you have to have the data.

[Tilly1998]

Thanks for your reply. I havent had a look at your example yet but what i am trying to do basically is this: the place i work has the usual info on the front - address telephone number etc. The telephone number currently showing is the switchboard.

The owner now wants to show direct telephone numbers. So when the secretary types the letter, she can (hopefully!) select her boss from the drop down list and it will automatically fill in a text field with his own direct line.
Eg: Please ask for: (cbo?) Mr Smith Or Mr Jones
Direct Dial: (txt box?) 000 000 000 111 111 1111

thank you for explaining it as well - i know it is second nature to you guys firing off this code but i am floundering in a sea of Dim this and str that:banghead:

[Tilly1998]

Hooray its done it! Thank you both so much for your help. If you dont mind me bothering you once more I just have a few questions to help me understand the code:-
1. What does wb mean?
2. how come there is a public statement
3. what is the difference between Array and If Else because when i was looking on the internet, i was trying to do it with an if else statement
4. what does (j) mean at the end of the combobox list index i.e wb "name", ManagerName(j)

best wishes

[Tilly1998]

Hi its me again - dont mean to be a pain, but i have adapted the code and for some reason, the phone number is not showing up. I dont fully understand Arrays and the last bit of code so if somebody could help i would be really grateful. I have attached the word doc.
Thanks in advance

[lucas]

Hi Tilly,
Right off I see the bookmark name in the code does not match the bookmark in the document:
Private Sub cmdOK_Click()
Dim j As Long
j = cboFeeEarnerName.ListIndex
wb "bmkFeeEarnerName", FeeEarnerName(j)
wb "bmkFeeEarnerPhone", FeeEarnerPhone(j)
wb "address", ManagerAddress(j)
'wb "combo", cmbFeeEarner
Unload Me
End Sub

[Tilly1998]

Oh god how stupid can you get! I feel very embarrassed. I had a good look at my code as well and you found it within 5 mins! sorry:doh: :doh:

[lucas]

Don't let it bother you Tilly. The fact that I was able to spot it should tell you how many times I've made similar mistakes...

[fumei]

You should also be getting an error on this:wb "address", ManagerAddress(j) as there is no bookmark of that name.

In the bookmark filling procedure, you may want to change:Debug.Print "Bookmark not found: " & BName
to this:MsgBox "Bookmark not found: " & BName
That way the message will be displayed to the user. Debug.Print is useful when developing, but not useful at all in production.

Also, for clarity, I would change the Sub name.

"wb" is not very informative. Perhaps,

Sub FillBookmark(ByVal BName As String, ByVal inhalt As String)


Finally, you must make sure the array count are equal (or do some other processing).

FeeEarnerName has 7 elements
FeeEarnerPhone has 7 elements
ManagerAddress has 4 elements

So say you select the sixth element from the FeeEarner dropddown, this make the dropdown ListIndex = 5 (it is 0 based). Therefore j = 5.

However, ManagerAddress(j) - or ManagerAddress(5) - is "subscript out of range". There is no ManagerAddress(5), it only goes to 4.

To answer your direct questions:
1. What does wb mean?

It is the name of the procedure being called. That is why I suggest renaming it. wb is not meaningful.

2. how come there is a public statement

Public variables can be used anywhere. This is a matter of scope, which is a very very important concept. Public variables may not be declared in object modules. A userform is an object module, therefore the public variables must be declared in a standard module. Once declared they can be set and used anywhere - that is what "Public" means.

3. what is the difference between Array and If Else because when i was looking on the internet, i was trying to do it with an if else statement

They are two utterly different things. If..Else is a logic statement.

IF something is True Then
do something
Else (ie. that something is False)
do something else
End If

If..Else statements are Boolean logic statement always based on the initial parameter being True (or not).

Arrays are not logic statements. They are variables. They are holders of data elements.

4. what does (j) mean at the end of the combobox list index i.e wb "name", ManagerName(j)

j is a numeric variable. It is made to equal the value of the combobox listindex. That is, the listed number of the selected item in the combobox. Combobox are 0 based, so if you select the sixth element, the ListIndex = 5. Note that it is NOT the text value of the selected item, it is the numeric value within the list.

You have a number of the selected item.

You have an array. If, as I mention above, the arrays match, then the numeric values should match.

The combobox is populated from an array, FeeEarnerName. So if you select the sixth item, essentially that is:

FeeEarnerName(5).

So the code gets the 5 (by making j = ListIndex), and uses it with another array, ManagerAddress.

ManagerAddress(5)

or, FeeEarnerPhone(5)

Do you follow? You select the sixth item of FeeEarnerName (ListIndex = 5), and use the number to get the values of another array.

[Tilly1998]

Okaaay... i think it is gonna take a few goes at this explanation for it to work its way into my brain! but your way of putting it is far far easier to read than my visual basic books. i hate to copy stuff blindly without understanding what it is im doing so thank you for taking the time out to explain it to me.
best wishes
:)

[fumei]

I like that you prefer undestanding to blind copying. So, ask away. What is the hardest thing to understand?Hopefully you DO understand the difference between If..Else statements and arrays.

The first is a Boolean (Trie or False) logic statement.
The second holds data.

As for the realtionship between arrays, is is simply a question of the index number.

Again, if you have six elements (each!) in three arrays, using the index number of one array can be useful to get the value of another [i]if they match.

Array 1
FirstName(0) = "Bob"
FirstName(1) = "Harry"
FirstName(2) = "George"
FirstName(3) = "Frank"
FirstName(4) = "Susan"
FirstName(5) = "Melissa"

Array 2
LastName(0) = "McDonald"
LastName(1) = "Wittier"
LastName(2) = "Kent"
LastName(3) = "Bzaiack"
LastName(4) = "Johnson"
LastName(5) = "Nurr"


So say you select from Frank from FirstName. You can get the index number. 3. Take the index number and pass it to the other array, so you get LastName(3), or Bzaiack.

This is only helpful if, as I mention, the arrays match with meaningful information.

So you can use the number of Manager(4) to get ManagerAddress(4) IF the array elements match.

That is: ManagerAddress(4) does indeed hold the correct data for Manager(4).

The point being is that if they DO match you can take ONE number and get meaningful information easily.

Manager(4) - taking the 4 - can easily get the data( information) out of....say....

ManagerAddress(4)
ManagerPhone(4)
ManagerTitle(4)
etc.

The j is the number of the selected item in the array - in my examples above, 4.

Dim j As Long
' set the variable to the index number of the
' selected item in the combobox
j = cboFeeEarnerName.ListIndex
' say.....4
' now USE that number to get the VALUE of the
' other arrays

wb "bmkFeeEarnerName", FeeEarnerName(4)
wb "bmkFeeEarnerPhone", FeeEarnerPhone(4)
wb "bmkaddress", ManagerAddress(j)

[Tilly1998]

Hmm, it sort of makes sense. its kind of like a database primary key where you get your information from your tables but why did you pick j? forgive the stupid question. and here's another one - (sorry) isnt it a logic statement using if else because IF the manager's name is fred blogs THEN his phone number is going to be 000 (i said it was a stupid question). :) thanks in advance

[fumei]

j is simply an old convention for declaring a Long variable.

Like i was used for declaring an Integer.

It could be anything. lngBhahBlahYadda.

"isnt it a logic statement using if else because IF the manager's name is fred blogs THEN his phone number is going to be 000"

ABSOLUTELY NOT! No, no, no.

If..Else statement do one thing, and one thing ONLY.

It evaluates whether the parameter is True, or False. period. Nothing else. It is a Boolean logic statement. The parameter is either True, or False. The human concept of meaning - "fred blogs" - is NOT part of an If..Else. Unless that is what is being tested for True...or False.

IF something is True THEN
' do something
ELSE
' do something
END IF

IF Textbox1.Text = "Fred Blogs" THEN
' do something
ELSE
' do something
END IF

The only thing, the ONLY thing, that is being tested is whether Textbox1.Text is "Fred Blogs". It is either True, or False.

What happens if it is true (or false) is totally up to the programmer. The "do something".

IF Textbox1.Text = "Fred Blogs" THEN
Msgbox "Fred loves chicken."
ELSE
' do something
END IF

is exactly, from the If...Else logic point of view, the same as:

IF Textbox1.Text = "Fred Blogs" THEN
ActiveDocument.Formfields("Yahoo").Result = _
"Fred hates watermelon."
ELSE
' do something
END IF

The logic statement itself does not consider, AT ALL, what happens if the parameter is True, or False. It only checks to see if it is True...or False. That is the only thing an IF...ELSE does. The VBA parser checks to see that the actions resulting from True, or False, are valid. But the IF...ELSE itself only checks to see if the given parameters is True...or False.

This is a very good segue to using Select Case.

Select Case can be much better than If...Else, in some situations. Specifically...for what it is designed for.

Select Case is used to test multiple values of ONE variable.

If...Else is used to test the Boolean state of given statements. It only answers True, or False.

Select Case answers with whatever is the value of the given variable.
Select Case Textbox1.Text
Case "Fred Blogs"
' do something
Case "Frank Sinatra"
' do something
End SelectThis checks the value of Textbox1.Text. If it is this....do that. If it is That, do something else.

This is NOT the same as:

If textbox1.Text = "Fred Blogs" Then

That tests if textbox1.text = Fred Blogs. It will either be True, or it will be False. It is Frank Sinatra then the given statement is False. Period. It will execute any code you like. Anything you put into the Else part of the instruction. But it does not, in itself, know that Textbox1.Text = Frank Sinatra. It ONLY knows that Textbox1.Text = Fred Blogs is False.

Select Case evaluates the VALUE given it.

In your case you need neither of these. IF your arrays match, then you simply are using the number of the index of the selected item in the dropdown.

I will try again.

IF the arrays match, that is if the fourth item listed (Fred Blogs) is matched - and this must be done by you - to the fourth item in the Phone array, then you can use the number. That is, if the fourth item in the Phone array is Fred Blogs phone, and the fourth item in the Address array is Fred Blogs's address, then getting the index number of ONE array will match to the index number of another array.

In other words...Fred Blogs data is always the fourth item in ANY array you build. If this is true, then:

AnyDataArray(4) will always have Fred's data, be it Phone, or Address, or FavRestaurant, or anything else you have as an array.

That is why, the code picks up the value of the ListIndex of the FeeEarnerName. This gives the value of the array used to populate the combobox. The ListIndex, and the originating array are both 0 based.

It sets the variable j as that value. That can be used in any array.

Original array used to populate the combobox:

Harry D (0)
greatful Dead (1)
James II (2)
George martin (3)
Fred Blogs (4)
me (5)
YOU (6)

This means if you select Fred Blogs from the dropdown, the the ListIndex = 4

So we grab that number, as j.

Now we can use it - as 4 - in other arrays.

harry Address (0)
greatful Dead Address (1)
James II Address (2)
George martin Address (3)
Fred Blogs Address (4)
me Address (5)
YOU Address (6)

harry Phone (0)
greatful Dead Phone (1)
James II Phone (2)
George martin Phone (3)
Fred Blogs Phone (4)
me Phone (5)
YOU Phone (6)

harry Title (0)
greatful Dead Title (1)
James II Title (2)
George martin Title (3)
Fred Blogs Title (4)
me Title (5)
YOU Title (6)

harry Salary (0)
greatful Dead Salary (1)
James II Salary (2)
George martin Salary (3)
Fred Blogs Salary (4)
me Salary (5)
YOU Salary (6)
Dim j As Long
j = cboFeeEarnerName.ListIndex
Msgbox names(j) & Address(j) & Phone(j) & Title(j) _
Salary(j)all use the value - say 4 - to get the contents of other arrays. The contents that are in index 4.

[Tilly1998]

Yes I understand now. i wasnt seeing the first part of the picture, which is that in my case i am using variables rather than boolean which just goes to show how c**p i am at this. i will have a good old study of your very patient explanations. sorry if you have been pulling your hair out

all the best

[fumei]

Not a problem. It takes a while, but I am sure you will get it.