Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Posts
    3

    Unanswered: Help with Listbox in Excel

    it should have been very easy..
    but after a day, i cannot find why it does not work

    The steps:
    1)I just want my VBA code in Excel to create a Listbox
    2)add content to this list box from the code
    3)tell (from VBA again) to launch a sub if one member of the box is selected.

    Easy enough?
    Well i've tried it in many different ways.
    The last way i tried is doing it with a Macro, wichi works. But when i call the Macro from the VBA code i get a '438' error .

    It seems impossible.
    If you can help. Thanks

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Can you post the exact error ? Also, can you post the code in your macro ? From what you describe, I think you are on the right track with using vba (in a macro).

  3. #3
    Join Date
    Aug 2002
    Posts
    3

    Here is a description

    Ok Let me describe my process.

    I have a Code that does Calculation on Multiple spreadsheets.
    It first delete all the spreadsheets except from the Inputs one.
    and then rebuild sheet by sheet.

    Now all the figures come out as i expect.
    My code creates a new spredsheet with some kind of average results
    and draw charts automatically.

    I output the charts as object.
    There are 5 of them per subject, so i've put them on the top of each other.

    Now i want to add a Listbox (from the vba code) that puts the graph i select in front.

    However way i have tried to do it, this does not work.
    What does not work is my list box.

    I cannot succeed from the code to do these three steps
    1) build a listbox,
    2)fill it with the names from an array and
    3)tell this list box to run a macro when clicked.

    It is step 2 that fails when i execute from my code.

    Now i have tried with a MACRO, here is the code:
    --------------------------------------------------------
    This creates the listbox, fills it with some cells from the spreadsheet "inputs" and refer the other macro WHen_Clicked when there is a click to select.
    --------------------------------------------------------
    Sub Macro3()
    ' Macro3 Macro
    ' Macro recorded 27/08/2002 by QDB
    ActiveWindow.Visible = False
    Windows("DCContsGuarantee_QVersion4.xls").Activate
    ActiveSheet.ListBoxes.Add(906, 272.25, 87.75, 129.75).Select
    With Selection
    .ListFillRange = "Inputs!$N$2:$N$7"
    .LinkedCell = ""
    .MultiSelect = xlNone
    .Display3DShading = False
    End With
    Selection.OnAction = "WHen_Clicked"
    End Sub


    If i run this Macro on its own, everything runs fine all the time.
    If i call this from my code it breaks at the .ListFillRange = "Inputs!$N$2:$N$7" returning an error:Run-Time Error '438' Object doesn't support this property or method.

    How come does this work a a macro called independently but not when i call this macro from the code ?

    Thanks for you help.

  4. #4
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109
    Make sure, when you call the macro, that there is a valid selection:

    With Selection
    .ListFillRange = "Inputs!$N$2:$N$7"
    .LinkedCell = ""
    .MultiSelect = xlNone
    .Display3DShading = False
    End With

    JefB - hope it helps

  5. #5
    Join Date
    Aug 2002
    Posts
    3
    i'm afraid it is not that. The selection exists.
    There must be a way to do this easely and so far i did not find
    a way !

Posting Permissions

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