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 :grinhalo:

[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 :yes