Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Box- If statement (Word 97- VBA)

    Hi there, i am in need of help in relation to If..Then Statements. I have a userform with a series of combox boxes for the user to pick an option from. I need to set the form to display certain results in other combo boxes, depending on the choice made in the first box.
    for example. if i choose ice-cream from the first combo box, i want the second to automatically default to chocolate sauce.
    any ideas how to do this?
    thanks
    Andrea

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Combo Box- If statement (Word 97- VBA)

    In the code module for your user form, you can create an event procedure for each combo box where you need a change to occur based on what the user chooses. This probably is the ComboBox1_Change event, but there are other events that you might also find useful. Inside the procedure, you'd probably use a If...Then...Else or Select...Case...End

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box- If statement (Word 97- VBA)

    The idea of using Case statements, as suggested by Jefferson, is a good simple and standard approach. In cases where there are lots of combo boxes and/or lots of date items, I've found it convenient to store the combo items in a multidimensional array, one dimension for each box. This makes for rather more condensed and succinct code generally. I'm sure I can dig out an example if you want.

    Alan

  4. #4
    New Lounger
    Join Date
    Sep 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box- If statement (Word 97- VBA)

    HI Alan, i would LOVE an example of some code.
    I am not sure how to construct the code. I am not sure what to do with the choices in the combo box...do i refer to them as text or a list item?
    Any examples of such If statements would be very helpful...an example of such a combo box with text choices, even better.
    Cheers
    Andrea

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box- If statement (Word 97- VBA)

    You didn't say the application in which you intend to use the combo boxes. This may have a bearing on how you find it most convenient to store the values - in Word, in custom document variables, in Access a table etc. Here's an approach I've used in Excel, where the values are stored in (surprise) a spreadsheet. My reference to a multidimensioned array represents a more general, but probably more clumsy/ cumbersome method, exclusive to the VBA code itself. I tend towards using whatever structured containers the application kindly provides <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    This example shows how to deal with duplicates and hides combo boxes if they don't have any values to show (try select Mineral in Box1 - Box 3 has no entries, so remains hidden). No doubt you'll want to modify this to your own needs. Hope it's at least a start.

    Alan

  6. #6
    New Lounger
    Join Date
    Sep 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box- If statement (Word 97- VBA)

    Thanks Alan, My userform is in WOrd 97.
    struggling along!
    cheers, and thanks for the example
    Andrea

  7. #7
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Combo Box- If statement (Word 97- VBA)

    This may give you a different way of looking at the problem. The following assumes you have a UserForm with two controls named ctlOne and ctlTwo. I have included two subroutines which show two different ways of populating a combo box where the information comes from the VBA itself.
    <UL>Sub UserForm_Initialize()
    Dim arg As Variant
    Dim aOneItem As Variant
    aOneItem = Array("Fax", "Letter", "Memorandum")
    'Fill list items
    For Each arg In aOneItem
    Me.ctlOne.AddItem arg
    Next arg
    End Sub
    Sub ctlOne_Change()
    'empty the list before adding new items
    Do While Me.ctlTwo.ListCount > 0
    Me.ctlTwo.RemoveItem 0
    Loop
    'now add the new items
    Select Case Me.ctlOne
    Case "Fax"
    Me.ctlTwo.AddItem "Fax suboption 1"
    Me.ctlTwo.AddItem "Fax suboption 2"
    Me.ctlTwo.AddItem "Fax suboption 3"
    Case "Letter"
    Me.ctlTwo.AddItem "Letter suboption 1"
    Me.ctlTwo.AddItem "Letter suboption 2"
    Me.ctlTwo.AddItem "Letter suboption 3"
    Case Else
    Me.ctlTwo.AddItem "Memorandum suboption"
    End Select
    End Sub
    [/list]
    Andrew Lockton, Chrysalis Design, Melbourne Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •