Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    102

    Unanswered: How come my COmbo tables don't populate on startup >?

    How come my COmbo tables don't populate on startup >?

    I have 2 combo fields in one spreadsheet of a workbook in Excel. It is supposed to have approx. 20 options in each.

    But when I open the workbook, the dropdown menu has nothing.
    It only populates when i run the code in VBA.

    Is there a way to have it automatically populate the dropdown menu ?


    Also,

    anyone know of a forum that's got more traffic in it ?


    thanks in advance..

  2. #2
    Join Date
    Sep 2003
    Location
    Cincinnati, Oh USA
    Posts
    203
    To populate a vb combobox or list box in a spreadsheet with the data in from vba code try this in the "sheet1" background:

    Private Sub ComboBox1_GotFocus()
    With ComboBox1
    .AddItem "cats"
    .AddItem "dogs"
    .AddItem "horses"
    End With
    End Sub

  3. #3
    Join Date
    Sep 2003
    Posts
    102
    i see...

    i was doing it in the

    form_initialize() method..

    when does _gotfocus() get called ?

  4. #4
    Join Date
    Sep 2003
    Location
    Cincinnati, Oh USA
    Posts
    203
    This function is not 'called'. Basicly the combobox when using the got focus (or lost focus) is aware of its state and will run by itself. To experiment with this, simply place any control into a user form, for example a combo box. Then..

    Private Sub ComboBox1_GotFocus()
    MsgBox("I'm alive!!!")
    End Sub

    Just click in or out of the combo box to watch it work. Try it with the 'MoveMove' function.
    ----------
    i was doing it in the

    form_initialize() method..

    when does _gotfocus() get called ? [/SIZE][/QUOTE]
    Last edited by RickKnight; 10-01-03 at 08:31.

  5. #5
    Join Date
    Sep 2003
    Posts
    102
    So there's no way to initially populate the boxes ?
    From what I gather what you're saying, when you first open the Excel file, you'd have to click on the box before it would populate



    Originally posted by RickKnight
    This function is not 'called'. Basicly the combobox when using the got focus (or lost focus) is aware of its state and will run by itself. To experiment with this, simply place any control into a user form, for example a combo box. Then..

    Private Sub ComboBox1_GotFocus()
    MsgBox("I'm alive!!!")
    End Sub

    Just click in or out of the combo box to watch it work. Try it with the 'MoveMove' function.
    ----------
    i was doing it in the

    form_initialize() method..

    when does _gotfocus() get called ?
    [/SIZE][/QUOTE]

  6. #6
    Join Date
    Sep 2003
    Location
    Cincinnati, Oh USA
    Posts
    203
    No there is another way. Use the cell validation technique (using a list).
    But your right on your assumption. You wouldn't see the data anyway until you've clicked on it (got focus or on click).

    Originally posted by ontheDB
    So there's no way to initially populate the boxes ?
    From what I gather what you're saying, when you first open the Excel file, you'd have to click on the box before it would populate



    [/SIZE][/QUOTE]

  7. #7
    Join Date
    Sep 2003
    Posts
    102
    Originally posted by RickKnight
    No there is another way. Use the cell validation technique (using a list).
    But your right on your assumption. You wouldn't see the data anyway until you've clicked on it (got focus or on click).

    [/SIZE][/QUOTE]

    I found a way.

    By putting the code in the
    Private Sub Workbook_Open()

    End Sub

    in the "ThisWorkBook" object, you can execute it only once at startup.

  8. #8
    Join Date
    Jul 2003
    Posts
    9
    There is a property (depending on the Combo Box control)
    ListFillRange that can be defined and will be automatically updated whenever the values in the range change.
    using VB code to update will give the same result but will fix the dropdown at open time unless you continually refresh. In your example this maybe exactly what you need.
    Always best (this is for others) to put MSGBOX in the code to see whether an even it working in the Initialise, Open or whatever !!!

Posting Permissions

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