Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Location
    Oxford, Oxfordshire, England
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forms (Excel 2000)

    Hello to all. Can anybody help. I am only a novice when is comes to using VB. I have created a form which is working fine the problem that I have is that I have a series of combo boxes which contain lists. I am trying to write some code so that when a user selects an item from a list in combo box 1 it will only allow them to view and choose a particular list for combo box 2 and then in turn combo box 3. Thanks

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Forms (Excel 2000)

    You will have to write code in the AfterUpdate event of the first combo box to change the list in the second combo box depending on the selected item. You can do this by setting the RowSource property of the second combo box to the address of the appropriate range in a worksheet, or by clearing the combo box and creating its list anew, either by repeated use of AddItem, or by setting the List property to an array.

    Example of the first approach (to be adapted to your situation, of course)

    Private Sub ComboBox1_AfterUpdate()
    Me.ComboBox2.RowSource = Range(Cells(1, Me.ComboBox1 + 2), Cells(3, Me.ComboBox1 + 2)).Address
    End Sub

    Example of the second approach:

    Private Sub ComboBox1_AfterUpdate()
    With Me.ComboBox2
    .Clear
    Select Case Me.ComboBox1.ListIndex
    Case 0
    .AddItem "Kansas"
    .AddItem "Kentucky"
    Case 1
    .AddItem "Kildare"
    .AddItem "Kilkenny"
    End Select
    End With
    End Sub

  4. #3
    New Lounger
    Join Date
    Jul 2003
    Location
    Oxford, Oxfordshire, England
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms (Excel 2000)

    Cheers Hans. This has helped a lot. It has opened many avenues to improve the form, so I am sure I will be back.

  5. #4
    New Lounger
    Join Date
    Jul 2003
    Location
    Oxford, Oxfordshire, England
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms (Excel 2000)

    Hello, Me and my form again. I am wanting to mask an inputbox so that it will only accept a calendar entry. I also have a combobox with a list of dates which are generated by formulas, the problem being is that when you drop the values they are shown in American format, can I re-format them? Can anybody help?

  6. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Forms (Excel 2000)

    1. UserForm controls have no Format or Mask property you can set. You can check the contents of the text box in its Before Update event - something like

    Private Sub txtDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If IsDate(Me.txtDate) = False Then
    MsgBox "Enter a valid date, please"
    Cancel = True
    End If
    End Sub

    or use a specialised control such as the Date Time Picker or Calendar control (right click the Toolbox and select Additional Controls)

    2. How do you populate the combo box? If you set the Row Source, dates work OK. If you use AddItem, you must format the items explicitly:

    Me.ComboBox1.AddItem Format(..., "dd/mm/yy")

Posting Permissions

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