Using a list box to call VBA function or Macro

[RichardP1978]

http://www.access-programmers.co.uk/forums/showthread.php?t=123538

Following on from that thread, I would really like to tidy up other areas of my front end. I am using the idea that Roy suggested and it works very well.

At the moment, I have a form, that has buttons on it that people click when they want to generate specific sheets from excel using data from access using VBA on the form. These work fine, however, now that the database is going live and all features need to be added, I think it would be nice to have one small form, with a list box.

I have converted a copy of the code on the form and placed in a module by declaring then functions instead of private subs.

At this stage, I tried doing it with macros running the code by using a series of runcode. I then created a table that has the macro name stored in it along with a decriptive bit of text detailing the excel chart that gets produced to make it easy for the user.

The list box populates itself correctly, but the problem I am having is I do not know how to make the 'go' button look at the list box, pull the hidden macro/function name from the list, then go and run the macro/function.

I have tried looking about, but have not found anyhthing.

 

[Bat17]

I think you are after the 'Eval()' method

Peter

 

[RichardP1978]

I cant fathom this out to save my life.

In an effort to try and make it clear what I have in case there is misunderstanding.

1 Table, tblMonitoringLogsList with 2 fields, FunctionName, FunctionNameOnList

List box, has 2 columns assigned to it, with the FunctionName field hidden, so only FunctionNameOnList appears.

User selects a item in the list, and clicks a button which then runs the function, well thats the hope.

I dont know really how to get the Eval function in, and in what way for it to work out the FunctionName depending on what FunctionNameOnList item has been selected.

 

[Bat17]

Debug.Print Eval(Me.List0.Column(1))

Column will be 0 or 1 depending which is hidden and obviously you need to change the list name.

I think the functions need to be in a real module not a Form one.
the function names in the table should have their () with them but if not then you can probably add them in with code

HTH

Peter

 

[RichardP1978]

That helped no end Peter! Thanks!

I had already put things over in to an actual module as I do now want to tidy things up and can now put alot of 'report generating' code in to one place so its less agro knowing where things are.

The code ended up being for reference

Debug.Print Eval(Me.lstMonitoring.Column(0))

The Column(1) ended up trying to run the first word of the descriptive text, so tried it on 0 and it works.

Thanks, I can now apply that to so many other things I think.