List Box - Application.run Macros

[dj44]

Hi folks,


I made a list box.

I am trying to run a macro when the item is selected

Column 1 list box list index

Column 2 macro name to run

eg

0 Macro1

1 Macro2

2 Macro 3

it is populated from the worksheet list

Private Sub ListBox100_Change()



Dim ws                 As Worksheet
Dim i                   As Integer


    Set ws = Worksheets("Macros")

    For i = 3 To 7

    If ListBox100.ListIndex = ws.Cells(i, 1).Value Then    'Listindex

    Application.Run ws.Cells(i, 2).Value    ' Macro name to run

    'Call ws.Cells(i, 5).Value

End If
Next i
End Sub

it was at some point working but now the error says

on the applicaiton.run line


i have set the row source to column 1


any ideas what it could be

[mdmackillop]

It's easier if you attach your workbook, then we can see returned values etc.

[dj44]

Hi M,

Problems Noted


if no Macro in workbook gives error

if numbers used in macro name or 2 short macro name gives error

Is there any way to have error resume next ie only run macro if i select the item from the userform list box?
I thought that was the idea run macro only if item is selected from the list box


this has vexxed me no end since yesterday my list box malfunctioning

[mdmackillop]

Remove the RowSource and populate the listbox on activation. This helps ensure compatibility; better thought to use Find & Offset in case data is changed and the Row is not correct.

Private Sub UserForm_Activate()Dim r As Range
With Sheets("Macros")
Set r = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
Me.ListBox100.List = r.Value
End Sub

You can avoid the loop in the code

Private Sub ListBox100_Change()    ' or click the item
    Dim ws As Worksheet
    Dim i As Integer
    Set ws = Worksheets("Macros")
    i = ListBox100
    Application.Run ws.Cells(i + 2, 2).Value
End Sub

[dj44]

Thank you M,
let me do some investigating - Im sure too find some new self inflicted bug i create

[Aflatoon]

Why not just load the macro names into the listbox and run them directly from there?

[dj44]

Hello A,

pardon me , but im not sure how i would do that .

[mdmackillop]

pardon me , but im not sure how i would do that

Fill the listbox with Macro names

Private Sub ListBox100_Change() ' or click the item
    Application.Run Listbox100 
End Sub

[dj44]

Hi M,

oh yes that would work too, Greg helped me a while back on the column target for a macro

I could do this too

Private Sub ListBox100_Click()
Application.Run ListBox100.Column(2)
End Sub

I will test this later