none
Select Listbox Item - Run Macro RRS feed

  • Question

  • Hi friends,

    I have a worksheet named ListBoxMacro

    Column A - the Listbox index Column B the macro to Call

    In the ListBox1 -  I have put the RowSource  A3:B7

    ListBoxIndex (A)      Call Macro (B)
    0                               oWksht_calc
    1                               oWksht_data
    2                               oWksht_mainsheet

    So when I select and item from the list box it will run the corresponding macro.


    This is what I created

     Private Sub ListBox1_Change()

    Dim oWksht                As Worksheet
    Dim i                          As Integer

        Set oWksht = Worksheets("ListBoxMacros")
        For i = 3 To 7

        If ListBox1.ListIndex = oWksht.Cells(i, 1).Value Then  

        Call oWksht.Cells(i, 2).Value
        End If

    Next i
    End Sub


    It seems like it should work  - but the error I received is object does not support property.

    Am i missing something

    thank you for any help

    pw

    Wednesday, June 29, 2016 2:50 PM

Answers

  • Try replacing

        Call oWksht.Cells(i, 2).Value

    with

        Application.Run oWksht.Cells(i, 2).Value


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Dan_CS Wednesday, June 29, 2016 3:40 PM
    Wednesday, June 29, 2016 3:28 PM

All replies

  • Try replacing

        Call oWksht.Cells(i, 2).Value

    with

        Application.Run oWksht.Cells(i, 2).Value


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Dan_CS Wednesday, June 29, 2016 3:40 PM
    Wednesday, June 29, 2016 3:28 PM
  • Thank you my friend,

    its all in the details, that worked nicely!

    :)

    Wednesday, June 29, 2016 3:40 PM