locked
VBA code needed to make combox selection automatically fill in 2 textboxes with text. RRS feed

  • Question

  • I want the selection from a combobox on the userform to trigger the automatic population of 2 textboxes.  For example: 

    If  "Maximum Investment" is selected from the combobox, then txtRatio.Value ="100% Fixed Income" AND txtDescrp.Value = "an annual return of
    between +10.0% and -1.3% two-thirds of the time."

    My code:  Case is = "Maximum Investment": txtRatio.Value = "100% Fixed Income"

                   Case is = "Maximum Investment": txtDescrip.Value = "an annual return of between +10.0% and -1.3% two-thirds of the time."

    This fills the first textbox but not the second.  Please help.

    Thanks!

    Tuesday, March 12, 2013 1:18 AM

All replies

  • You haven't given nearly enough information about possible permutations but as a general approach adapt somehtin like this

    Dim s1 as string, s2 as string
    Select case myTextBoxValee
    Case "Maximum Investment": ":
       s1 =  "100% Fixed Income"
       s2 =  "an annual return of between  etc..."
    Case "abc"
      s1 = "abc ratio text"
      s2 = "abc description text "
    ' etc
    End Select
      txtRatio.Value  = s1
     txtDescrip.Value = s2

    Peter Thornton

    Tuesday, March 12, 2013 5:55 PM
  • Thanks for trying to help me. 

    I have a combobox named cmbAlloc of which Maximum Investment is one of the items.  I have 2 textboxes, txtRatio and txtDescrip.

    I'm having trouble with your suggested line of code where it has "Maximum Investment": followed by  ":

    The program isn't liking that line (turns red).

    Select Case cmbAlloc.Value
    Case "Maximum Investment": ":

          strRatio = "100% Fixed Income"
      
          strDescrip = "an annual return of between  etc..."
      Case  "Stable Income":
          strRatio = "90% Fixed Income"
          strDescrip = "90% description text "
    ' etc

    End Select
      txtRatio.Value = strRatio
     txtDescrip.Value = strDescrip

    What am I doing wrong?

    Thanks again.

    Thursday, March 14, 2013 8:56 PM
  • Does the combobox say "Maximum Investment" (exactly as written)? Colons can be used in VBA to put two lines of code on the same line, so it's very important to put them in the right place when they are needed. Change the two Case statements by deleting everything after the second quotation mark, like so:

    Case "Maximum Investment"

    etc.

    Case  "Stable Income"

    Thursday, March 14, 2013 9:47 PM
  • > Case "Maximum Investment": ":
    Not sure how the extra colon- quote-colon crept in there, that would certainly fail to compile and give a red line. It should have been simply -

    Case "Maximum Investment"

    But the main point is, whichever way to are checking your conditions (If..ElseIf or Select Case) in each condition check determine the appropriate pair of results. In the example I wrote the results to a pair of variables then from the variables to the textboxes as the final step.

    Peter Thornton

    Friday, March 15, 2013 10:47 AM
  • Thank you, that got rid of the error, however, the 2 textboxes on the userform aren't filling in with the variable text.

    When I select "Maximum Investment" from the combobox cmbAlloc, I want txtRatio to display "100% Fixed Income" and the second textbox, txtDescrip, to display "an annual return of
    between +10.0% and -1.3% two-thirds of the time.".

    When I select "Stable Income" from the combobox cmbAlloc I want txtRatio to display " 90% Fixed Income" and the second textbox, txtDescrip to display "an annual return of .....".

    There are 10 possible choices in the combobox.  There are 3 bookmarks in the active document:

    bkmAlloc, bkmRatio and bkmDescrip.  Hopefully the extra detail will help you instruct me how to write the code to get the textboxes to populate, and the bookmarks to display the correct information.

    Thanks so much.

    Friday, March 15, 2013 2:28 PM
  • It took a bit of trial and error on my part, but I finally got it to work. However, I would still like to know how to change the code so that after the bookmarks are filled, the user will be viewing the Active Document window, and not be in the VB editor.  Thanks.
     
    Here's my code: :)
     
    Private Sub cmdClear_Click()
    cmbAlloc = Null
    txtRatio = Null
    txtDescrip = Null
    End Sub
    Private Sub cmbAlloc_Change()
    With Me.cmbAlloc
        Select Case .ListIndex
        Case 1
         Me.txtRatio.Text = "100% fixed income"
         Me.txtDescrip.Text = "an annual return of between  +10.0% and -1.3%..."
        Case 2
         Me.txtRatio.Text = "90% fixed income"
         Me.txtDescrip.Text = "an annual return of between  +10.6% and -0.3%..."
        Case 3
         Me.txtRatio.Text = "80% fixed income"
         Me.txtDescrip.Text = "an annual return of between  +11.6% and -0.2%..."
        Case 4
         Me.txtRatio.Text = "70% fixed income"
         Me.txtDescrip.Text = "an annual return of between +13.9% and -0.9%..."
    End Select
    End With
    End Sub
    Private Sub CmdOK_Click()
    With ActiveDocument
       .Bookmarks("Allocation").Range.Text = cmbAlloc.Value
       .Bookmarks("Ratio").Range.Text = txtRatio.Value
       .Bookmarks("Description").Range.Text = txtDescrip.Value
       Application.ScreenUpdating = True
       Unload Me
    End With
    With ActiveDocument.ActiveWindow.View
    .Type = wdPrintView
    End With
    End Sub
    Private Sub UserForm_Initialize()
    With Me.cmbAlloc
       .AddItem " "
       .AddItem "Maximum Income"
       .AddItem "Stable Income"
       .AddItem "Conservative"
       .AddItem "Moderate Income"
       .ListIndex = 0
      
    End With
    End Sub
    Wednesday, March 20, 2013 6:37 PM
  • There must be something else going on with your setup that simply what you've described. I replicated your form and ran it from the VBE, the form appeared in the application window not the VBE as expected.

    In passing, why the empty .AddItem " ". You don't need that but start your select case with "Case 0", the first item's index is zero.

    Peter Thornton

    Wednesday, March 20, 2013 7:37 PM