Word 2007 UserForm (FORM2 recall value from FORM1)?

[RP1America]

I am creating multiple userforms which run in succession of which the entries will populate one letter.

My third userform needs to call on a value chosen from the second userform. On top of that, I need that value to be Dim'd as String in the third userform. Any ideas?

Portion of current code that does not work. I got the idea of Form.FormName.cboBox from someone, yet it doesn't seem to work for me. What do I have wrong?

annuitantinfo = userform
annuitantsal = combo box
__________________________
Dim strsex2 As String
If form.annuitantinfo.annuitantsal = "Mr." Then strsex2 = "his"
If form.annuitantinfo.annuitantsal = "Mrs." Then strsex2 = "her"
If form.annuitantinfo.annuitantsal = "Ms." Then strsex2 = "her"

Thanks for the help!
Ryan

 

[kjv1611]

Try posting over here first:
forum707

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Whoops!


--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

I thought I was reading in another forum.. my my my - now where's that edit/delete thread button when you need it ?!


Now to the question..

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

annuitantinfo = userform
annuitantsal = combo box
__________________________
    Dim strsex2 As String
    If form.annuitantinfo.annuitantsal = "Mr." Then strsex2 = "his"
    If form.annuitantinfo.annuitantsal = "Mrs." Then strsex2 = "her"
    If form.annuitantinfo.annuitantsal = "Ms." Then strsex2 = "her"

So is this your actual full code, or are you leaving something out for the posting?

I just wanted to make sure I'm not missing something.

--

"If to err is human, then I must be some kind of human!" -Me

 

[RP1America]

Geez...what are you? Some kind of human?

;-)

 

[kjv1611]

Actually, no. I'm an AI robot that actually types, drinks coffee, and wishes he had hair. Why do you ask?

--

"If to err is human, then I must be some kind of human!" -Me

 

[RP1America]

Let me ATTEMPT to clarify...

In prior form Initialize:

With annuitantsal
.AddItem "Mr."
.AddItem "Mrs."
.AddItem "Ms."
End With

In latter form ok_click event:

Dim strsex2 As String
If form.annuitantinfo.annuitantsal = "Mr." Then strsex2 = "his"
If form.annuitantinfo.annuitantsal = "Mrs." Then strsex2 = "her"
If form.annuitantinfo.annuitantsal = "Ms." Then strsex2 = "her"

If annoption = "Life" Then
.Bookmarks("annoptionspecs").Range.Text = " " + strsex2 + " " + "lifetime"
End If


Hope this helps some. (BTW, how do I post code on this forum like you did in the nifty lil' "code" box?)

Thanks!

 

[fumei]

This appears to be a matter of Scope. Are your variables declared within the Scope of their use? Even your object names (a combobox, textbox, whatever) have to be in Scope (or be fully qualified) to use them ).

Do you really need multiple userforms? Have you considered using Multipages instead?

Check the Process TGML link on your response page to see options for posting (like using colors, highlights, the code window tags, etc.)



"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[kjv1611]

First, to your "how do I post code..." question:

Like this:
[ignore]

Sub Howdy()
  MsgBox "Hello World!"
End Sub

[/IGNORE]

Which would turn to this:

Sub Howdy()
  MsgBox "Hello World!"
End Sub

The TGML code is not case sensitive, I just try to always use ALL CAPS for that, for easier reading on my part if it needs editing before posting.

You can click the [blue]Process TGML[/blue] link at the bottom of the message box where you type your forum post for a long list of what does work. And there are a few not listed, as I suppose they've been added over time. The preview button is always helpful to help prevent making embarrassing mistakes... if you use it. Emoticons usage is detailed under the Emoticons/Smileys link, to the left of Process TGML.

Now to the thread subject:

In this piece:

    If form.annuitantinfo.annuitantsal = "Mr." Then strsex2 = "his"
    If form.annuitantinfo.annuitantsal = "Mrs." Then strsex2 = "her"
    If form.annuitantinfo.annuitantsal = "Ms." Then strsex2 = "her"

It looks to me like you are on the 2nd form, wanting to refer to the 1st form, but yet actually referring to the active (2nd) form. Is this the case, or is it really all one form after all?

If this IS the case, and you are wanting to refer to the other form, you'll need to refer to the other form by name.

This is just a made up example showing the idea:

Created 2 forms:
UserForm1
UserForm2

On each form, created 1 text box:
UserForm1 - txtForm1TextBox
UserForm2 - txtForm2TextBox

Then put in the following code:

[green]'In UserForm1[/green]
Option Explicit

Private Sub txtForm1TextBox_AfterUpdate()
    UserForm1.Hide
    UserForm2.Show
End Sub

[green]'In UserForm2[/green]
Option Explicit

Private Sub UserForm_Activate()
    If UserForm1.txtForm1TextBox = "Test" Then
        MsgBox "I said test it!", vbCritical, "Test!"
        UserForm2.txtForm2TextBox = UserForm1.txtForm1TextBox
    End If
End Sub

To test, run UserForm1, then enter Test (exactly as typed here) in the text box, and close the form. You'll then get a message box, followed by the Form2 opening with Test in the text box.

I think that'll get you where you need to be.


--

"If to err is human, then I must be some kind of human!" -Me

 

[RP1America]

I think I understand what you are saying...however, I believe I wasn't clear enough in my intentions.

What you have showing is UserForm2 pulling a value from UserForm1 valuing a textbox in UserForm2.

What I am attempting to do is to value a bookmark in the actual document with a String based on a value in UserForm1, yet the code is to placed in UserForm2 because of addition options within UserForm2.

Here is an example of how the letter goes:

If "Life" is chosen from annoption in UserForm2
text reads "...chose a Life annuity and therefore will receive payments for her lifetime."

If "Fixed Period" is chosen from annoption in UserForm2
text reads "...chose a 20 Year Fixed Period annuity and therefore will receive payments for 20 years."

The sex of the annuitant is determined by annuitantsal, in which the user can choose Mr., Mrs., or Ms. This is done on UserForm1

There are two major variables here 1) the type of annuity "Life", or "Fixed Period" and 2) the sex of the annuitant.

If Fixed Period is chosen, sex does not come into play. However, if Life is chosen, "his" or "her" must be entered into the String.

So if I can Dim a variable from UserForm1 within UserForm2, this would work.

Does that make more sense?

 

[fumei]

Yup, this is an issue of Scope. So....
quote]So if I can Dim a variable from UserForm1 within UserForm2, this would work.[/quote]you either do that (fully qualified), or you can use a Public variable.

Either way, it is a matter of Scope.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[fumei]

Assuming strSex is properly in Scope.....

Select Case Userform1.annuitantsal.Text
   Case "Mr."
      strsex2 = "his"
   Case "Mrs."
      strsex2 = "her"
    Case "Ms."
      strsex2 = "her"
End Select

can be run from Userform2.

I still think a MultiPage may be better.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[RP1America]

Thank you very much for the information!

I will try this out.

Unfortunately I am very new to VBA. I don't even know what the difference between multiple userforms and multipages is.

Thanks again!!

 

[RP1America]

UPDATE:

IT WORKS!!

Both your Case code and mine (below) worked!! Either one was good. What I had wrong is that I was coding Unload Me in the Ok_Click event rather than Me.Hide. DUH!! When I was Unloading the form, the previous values where then gone. When hidden, they remained! w00t!!

    Dim strsex2 As String
    If annuitantinfo.annuitantsal.Value = "Mr." Then strsex2 = "his"
    If annuitantinfo.annuitantsal.Value = "Mrs." Then strsex2 = "her"
    If annuitantinfo.annuitantsal.Value = "Ms." Then strsex2 = "her"

Thanks for all of your help!

Ryan

 

[fumei]

Both your Case code and mine (below) worked!! Either one was good. "

Ummm, yes both "work", but let me explain.

Select Case Userform1.annuitantsal.Text
   Case "Mr."
      strsex2 = "his"
   Case "Mrs."
      strsex2 = "her"
    Case "Ms."
      strsex2 = "her"
End Select

is ONE instruction. In other words, if annuitantsal.Text is "Mrs", the other actions for "Mr." and "Ms." never happen. In your case:

    If annuitantinfo.annuitantsal.Value = "Mr." Then strsex2 = "his"
    If annuitantinfo.annuitantsal.Value = "Mrs." Then strsex2 = "her"
    If annuitantinfo.annuitantsal.Value = "Ms." Then strsex2 = "her"

the instructions for ALL THREE are executed, regardless of what annuitantsal.Value is.

So if annuitantsal.Value is "Mrs.", the IF instruction for "Mr." is executed, AND the IF instruction for "Ms." is also executed. There is no logic connection between them. VBA looks at them, actions them, as three separate and independent instructions.

This is the difference between multiple If...Then statements, and Select Case. That is the point of using Select Case. If it is "Mrs."...then THAT Case is actioned. The actions/instructions for "Mr." or "Ms." are ignored.


"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[fumei]

Multipages are those things we often see that have multiple tabs. Clicking on a tab displays the contents "under" that tab. It is a convenient way to have many many more controls (textboxes etc.) on ONE userform.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry