Help turning a VBA Function/formula into a macro

[paularmstrong]

Hi,

Have this function code which sorts the contents of a single cell:

Please Login or Register  to view this content.

Which then works by using this formula:

Please Login or Register  to view this content.

Is there a way to have a macro instead run this so that I don't need the excel formula. I'm using a different code to allow a data validation list to select more than one option at once but would like it to also sort the entries too. Thank you!

[jomili]

If you'll include a sample showing both before and after, in other words, what you want to achieve, it will help a lot in driving towards a solution.

[paularmstrong]

If you'll include a sample showing both before and after, in other words, what you want to achieve, it will help a lot in driving towards a solution.

How do I include a sample, I cant find an upload button on here

[jomili]

Yeah, the forum's had issues with that. Click into Advanced View then go down below and click on Manage Attachments. Then you can attach it.

[paularmstrong]

Its not letting me attach the file but here is the original cell:

Please Login or Register  to view this content.

This is the formula looking at that cell which uses the VBA function shown in original post:

Please Login or Register  to view this content.

And the outcome:

Please Login or Register  to view this content.

Rather than use this formula and the function VBA code I need a macro which updates the original cell.

[paularmstrong]

Hopefully its attached to this message

[jomili]

Try this:

Please Login or Register  to view this content.

Almost forgot the function it works off of

Please Login or Register  to view this content.

[paularmstrong]

This works perfectly thank you! I'm sorry one extra question on this, if I wanted to run the Sortwithincell code as part of the code I have attached to a worksheet change procedure, how can I attach it to that, I've listed below the private sub code normally attached to the tab code (rather than in a module). I tried just typing out Sorthwithincell but I get an error when it runs it.

Please Login or Register  to view this content.

[paularmstrong]

Attached updated demo file with cell A1 with the data validation drop down, built in code which allows multiple selection and your code. Anyway to get the macro to run when the drop down code is selected and the change code is run.

Thanks!

[jomili]

To call a macro from within a macro, and if both are in the same workbook, or in your PERSONAL.xlsb, all you need to do is put the macro name. So, in your massive Worksheet Change macro, you would slip in the name "SortWithinCell" wherever you wanted it to kick in. However since the "SortWithinCell" works by selection, you probably don't want to call the full macro. Probably delete these lines:

Please Login or Register  to view this content.

And change "For Each cell In rng" to "With Target", and change "next cell" to "End With".

[paularmstrong]

Very sorry don't worry, got it to work. I had to put the link to your macro within lines turning off enable events otherwise it was causing an endless loop of it sorting and rerunning the change event macro. My fault.

Thank you though Jomili so much this is a massive help for me.

[jomili]

Glad I could help. If this fixes your issue don't forget to mark the thread "Solved" by using the Thread Tools at the top.