Run VBA Module from different workstations from Excel Menu 2

[WalterHeisenberg]

Hello everyone,

I have a VBA module that sorts worksheets in Alphabetical order. I created a new menu option that has a link to the a macro that is created when I import the sheet sorting module.

I would like to give the users the ability to use this custom menu to sort any workbook but right now I have to link that menu shortcut to the macro each time.

Is there a way to put an exported VBA module on a network location and allow users to have this custom function on every workbook without having to set it up each time?

Thanks in advance.

-A

 

[Welshbird]

Have a look at saving as an excel add-in.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

Hi,

Give your MODULE a unique name.

SAVE

EXPORT your module to a network folder accessible by all.

Your users can...

IMPORT your module into their PERSONAL.xlsb workbook and SAVE.

Now your code is accessible to the user.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[WalterHeisenberg]

Thanks for the tips so far I'm almost there. The challenge left is my code shorts the sheets in ABC order using ThisWorkbook everywhere. How do I pass in the workbook name the user is clicking on to the function in personal.xls?

I have a sub called:

 Sub abc(ByVal oWorkBook As workBook)

but haven't been able to figure out how I can pass the workbook name to that function. Any help would be appreciated. Thanks!

-A

 

[SkipVought]

hi,

Please tell me what your process is. What is the user doing, that needs the workbook name? Do not use VBAese, just plain words.

Please be clear, concise and complete.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[WalterHeisenberg]

Hi Skip,

Each month 6 accounting users have a monthly log of their work and by the end of the month each workbook contains around 200-400 worksheets.

So I created a VBA module to sort the workbooks in ABC order. I am setting up manually on 6 users workstations every month (adding the module to the workbook and updating a menu so it points at the local macro). The user clicks on the custom menu and it launches the macro.

The macro itself uses this.workbook in many places to do its thing. I figure if I can pass in the workbook name then I only have to make a few modifications to the existing code and it will work everywhere.

I am hoping this is possible but I haven't done anything along these lines and google didn't offer much help. Thanks for your time.

 

[SkipVought]

Well you have not helped yourself very much with the meager information thus provided. Your explanation is confusing!!!

First you state, "by the end of the month each workbook contains around 200-400 worksheets.

So I created a VBA module to sort the workbooks in ABC order."

You have a module with unstated purpose and process, "so it points at the local macro," ALSO with unstated purpose and process.

You refer to "this.workbook" with reference to what, from what?

It may be perfectly clear in your mind, but THAT is as far as the light is shining.

If you want to help yourself out, you must do a much better job of providing "clear, concise and complete" information, in order for any of us to assist.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[glenntb]

Instead of using "thisworkbook" use "activeworkbook"

-Glenn

 

[SkipVought]

Glenn,

Nice [red]GUESS[/red]!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[WalterHeisenberg]

Skip: Sorry my description was not clear. I thought it made sense as I wrote it.

Glen: Thank you for your tip. It solved the problem.

Thanks gentlemen.

-A