Have a macro (end user) question 1

[4281967]

I have to send a report out daily that is created with a very procedural process - so I thought, this would be a perfect opportunity to use a macro.
I wrote it - and it works exactly as needed - it saves me about 15-20 minutes a day.
I created it in my personal workbook. My reasoning was this:
1. I wanted it to be available on demand
2. I didnt want to send it with the workbook, as this report goes to many users - most of which are sr. management types and not very computer savvy.

For some reason - #2 didnt work. The end users are now getting the warning about macros and have to disable it before they can open it. Any ideas where I went wrong?

 

[SkipVought]

Hi,

So your have a separate workbook for the report that is NOT your PERSONAL.xls?

If your macro is in PERSONAL (and is not a user-defined-function) and NO MACRO IS IN THE REPORT WORKBOOK, then I don't understand where the "link" is.


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[4281967]

well - I may have gotten mixed up on my "vocabulary" -

just to be clear - when I open the report template and go to "Tools - Macros" - I don't see any macros that do not have a "PERSONAL.XLS!" in front of it. I have several macros in my Personal Macro Workbook - and (prior to yesterday when I made the macro) they have never been passed along when I send the report.

 

[SkipVought]

Do you have a reference set in the report workbook VB Editor, to your PERSONAL.XLS workbook


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[4281967]

I'm not sure. Can you please tell me how to find out? Need me to post my VBA?

 

[SkipVought]

in vb editor...

Tools/References

what's checked?


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[4281967]

Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Forms 2.0 Object Library

 

[SkipVought]

None of those references would do that.

In the report workbook VB editor -- File/Print - Range CURRENT PROJECT.

Does any code print?


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[4281967]

yes
3 pages printed.

[page 1]
Sheet105 - 1
Option Explicit

[page 2]
Sheet106 - 1
Option Explicit

[page 3]
Sheet107 - 1
Option Explicit


that's all that came out

 

[SkipVought]

Are any of your macros referencing anything NOT in the Report Template Workbook?

Could you post your code?


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[4281967]

Skip -
I just deleted all my "unnecessary" macros and it looks like its working now. Not sure what I did to screw it up, but I really appreciate your help.

Just to clarify this for myself:

if I make a macro and save it in my personal workbook, run the macro and send it to another user - they Will not get the macro with the spreadsheet as long as I didn't make a reference to it?

where is a good place to learn more about this?

 

[4281967]

forgot this:
...and if I should want to pass a macro along, I save it with the workbook and should digitally sign it. right?

 

[SkipVought]

If you have workbook A, and run a macro that's in your personal workbook, that does stuff to workbook A, then as long as workbook A has no references to your personal workbook, and you have not added any macros to workbook A, then workbook A should have no references to macros.

Where's a good place to learn about...

VBA in Excel?

Excel Help
Tek-Tips forum707 -- also check out FAQs

http://www.j-walk.com

John Walkenbach books
Borders/Barnes & Noble/Amazon......


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[4281967]

Thanks - I know it's not much but here's a star for your help. Thank you.

 

[SkipVought]

You need digital sig only if you users have HIGH MACRO SECURITY requiring DigSig.


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}