User form with list box results displayin on form

[cdrunner]

I have a user form that displays on start up that have a few input boxes that when OK button it click the info pushs to bookmarks on the form. I have this working.
I have added a list box to the user form that is populating with two choices. Now when clicking ok I need a specific value to show in a bookmark on the form. For example, If A is selected show Y on the form, if B is selected show Z on the form. I have no idea how to get my the value to the form. I am not very savy at VBA so I need simply code to copy. : pray2: Thanks for your help.

Here is my code for the button.

Private Sub CommandButton1_Click()
'move responses on form to the bookmarks
With ActiveDocument
.Bookmarks("PremiumFin").Range _
.InsertBefore txtboxFinanceName
.Bookmarks("Address1").Range _
.InsertBefore txtBoxAddress1
.Bookmarks("city").Range _
.InsertBefore txboxCity
.Bookmarks("state").Range _
.InsertBefore txboxState
.Bookmarks("zip").Range _
.InsertBefore txboxZip
UserForm1.Hide
End With
End Sub

Here is the code for my form and list box.

Private Sub UserForm_Initialize()
With Listbox1
.AddItem "A"
.AddItem "B"
End With

End Sub

[macropod]

Hi cdrunner,

What do you want to do with the selected item, which you can retrieve with code like:
Private Sub ListBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
MsgBox ListBox1.Value
End Sub
As for your CommandButton1_Click sub, it doesn't actually update the bookmarks. If you try to update them a second time, you'll find the new entries being appended to the previous ones. Also, it appears you're referrring to the form controls, rather than their contents. You need code like:
Private Sub CommandButton1_Click()
'move responses on form to the bookmarks
With ActiveDocument
Call UpdateBookmark("PremiumFin", txtboxFinanceName.Text)
Call UpdateBookmark("Address1", txtBoxAddress1.Text)
Call UpdateBookmark("city", txboxCity.Text)
Call UpdateBookmark("state", txboxState.Text)
Call UpdateBookmark("zip", txboxZip.Text)
UserForm1.Hide
End With
End Sub

Sub UpdateBookmark(BmkNm As String, NewTxt As String)
Dim BmkRng As Range
With ActiveDocument
If .Bookmarks.Exists(BmkNm) Then
Set BmkRng = .Bookmarks(BmkNm).Range
BmkRng.Text = NewTxt
.Bookmarks.Add BmkNm, BmkRng
End If
End With
Set BmkRng = Nothing
End Sub
PS: When posting code, please use the VBA tags.

[cdrunner]

Paul, thanks for your response and your code for the button. I used that and it works great.
For the list box, there are two choices and I want a phone number to display in the document via a bookmark based on the selection. On the user form, if A is selected show 1-800-555-5555 in the document. If B is selected, show 1-877-555-5555 in the document.
Your code to retreive from the list box, would that go with the code for the command button? Thanks again for your help.

[macropod]

Hi cdrunner,

For that, you could use code like:
If ListBox1.Value = "A" Then
Call UpdateBookmark("Phone", "1-800-555-5555")
ElseIf ListBox1.Value = "B" Then
Call UpdateBookmark("Phone", "1-877-555-5555")
End If
This code could go in either the 'ListBox1_Exit' sub or the 'CommandButton1_Click' sub.

[cdrunner]

That worked great. Thanks for keeping your explanation simple so a novice like myself can follow.

So my forms works now but if a user need to complete more than one form at a given time they have to close and reopen to get the userform to display again. Any simple way to have a reset button on the form that would clear everything and start over? The button would need to NOT print. Thanks.

[fumei]

Just have your commandbutton do the actions and nothing else. They select something else in the listbox and click the commandbutton again. That would however overwrite what they did before. That seems odd.

When you say "clear everything", do you mean empty the bookmark? that is not needed as performing the actions will overwrite the bookmark content.

When you say the button is not to print...you are printing the userform itself??? You need to be in the VBE to do this.

[cdrunner]

Sorry I was not clear enough. My user form opens at start up and has the person enter some information. Upon clicking ok the info on the user form pushs to the word document and the user form hides. Person now finishes the document by tabbing to and typing into a few additional form fields. Doc is locked. Person email, faxes or forwards for mailing. Now, the problem happens when person needs to complete a second document. The user form is hide. They have to close the document and reopen to get the user form to show. It would be great to have a button on the document that opens the user form so person can repeat the process but that button would need to not show for the email or faxed version. Hope that makes a little more sense. :bug: Thanks.

[fumei]

Now, the problem happens when person needs to complete a second document. The user form is hide. They have to close the document and reopen to get the user form to show. It would be great to have a button on the document that opens the user form so person can repeat the process but that button would need to not show for the email or faxed version.OK, let's cover a number of points.

First of all "They have to close the document and reopen to get the user form to sh ow." HUH??? They have to close (and save) the document to save the data they just put in. Otherwise, as I stated, any new data will overwrite. Yes?

So what happens? Are you saying they will do a SaveAs, and then use the same document again?

If this is the case, this is a BAD way of working with Word. Use templates, not repeating documents.

What do mean the doc is locked? And if it IS locked, what are you doing to get a "second" document?

I am not a fan of using Hide. However if you insist, you could easily get it to show again by writing a procedure to Show it. Or use the one you currently use. Can you put a button in the document to execute it. Of course. or use a shortcut key. Or a menu item. Or an icon on the toolbar (depending on your version).

So. If I understand correctly - and no, you are not being clear - you have a DOCUMENT that you use over and over again. The userform opens (Shows) when the document is opened. You enter stuff on the userform, which is put in the document, and then you email/fax it. If you forward it you must save it...which comes back to the save issue.

Please describe exactly the steps involved.

[cdrunner]

Ok, the CSR (customer service rep) will be opening the .doc from a server they only have read access to. Doc opens and CSR is presented with a userform asking for "mail to" information and a list box choice. The list box choice controls which phone number displays on the form. CSR completes user form and clicks ok. User form hides, info pushes to the doc. CSR tabs to additional form fields to complete the doc. (Since this have form fields it is a locked document) CSR selects file, print and chooses a print driver that email or faxes. CSR completes that process and is done with this letter. But, in Word, the letter they just completed is still open. Now, if they need to complete a 2nd letter what do they do? The userform is not displaying so they can't enter new address information for the 2nd letter. So currently they would have to just close this doc (they don't need to save it anywhere) and go back to the original .doc on the server and start the process again for the 2nd letter.

I would like to put a button on the doc, "reset" or "next letter" that opens the user form again so they can repeat the process. If I go that route the button would should not show when printed/emailed/faxed. I am open to suggestions of something other than a button on the doc but we have > 100 CSR's and it has to be simple and obvious. Thanks.

[fumei]

Ah, so you ARE using a document, rather than a template.

A button in the document will print. What version of Word? A "New Mail/Fax" icon would probably be the most obvious. Or, as I mentioned, a shortcut key.

Another possibility is to not hide the userform, but make it non-modal. That way they could just move it out of the way, do they stuff, and when they are ready to do another, grab the userform and do it again.