Excel Macros

[luv2bike2]

I set up a macro in a users excel, which works great for the user, however
there is a problem this user is experiencing:

when ever the user opens up any spreadsheet she has to either click on
enable or disable macros. i had to set the marco up so that the macro
will be there for when she imports a report from crystal reports however she
does not need the marcos for any other spreadsheet. is there a way that i
can
set up the macro so that she does not have to click on disable for all of her
other spreadsheets but the macros be there for when she imports a report
into excel?

The user is using Excel 2003

 

[Otto Moehrbach]

The enable/disable question comes up only if the workbook being opened has
macros in it. One open workbook having macros does not effect how another
workbook opens. HTH Otto

 

[luv2bike2]

before i setup the macro every spreadsheet that was opened up did not have
the disable/enable box now it does. i am not sure why but they do now. i am
confused.

 

[Rick Rothstein \(MVP - VB\)]

Exactly where did you put this macro?

Rick

before i setup the macro every spreadsheet that was opened up did not have
the disable/enable box now it does. i am not sure why but they do now. i
am
confused.

 

[luv2bike2]

Rick,
i hope this helps out as to where i put the macro.

I created a dummy macro:
tools/macro/record new macro/select personal macro workbook/stop macro

reviewed that the macro was there:
Tools/macros/macros - personal xls

create the vb script
tools/macro/vb editor/select sheet1 (under vba project (persnal.xls)

made sure that option explicit is present in the code sheet.

i typed in the code that i needed to put in there

it works great for the user on the spreadsheet that needs the macro but all
other spreadsheets have this disable/enable box pop up when she goes into
them.

Thanks for your assistance in advance

 

[Rick Rothstein \(MVP - VB\)]

It looks like the problem is that you put the macro in the user's personal
worksheet which gets automatically attached to the user's copy of Excel
whenever Excel is started. That means Excel sees there is a macro available
when it starts and, because of the security setting, queries the user about
enabling the macro. You should have put the macro in a Module in the
workbook it applies to only.

Rick

Rick,
i hope this helps out as to where i put the macro.

I created a dummy macro:
tools/macro/record new macro/select personal macro workbook/stop macro

reviewed that the macro was there:
Tools/macros/macros - personal xls

create the vb script
tools/macro/vb editor/select sheet1 (under vba project (persnal.xls)

made sure that option explicit is present in the code sheet.

i typed in the code that i needed to put in there

it works great for the user on the spreadsheet that needs the macro but
all
other spreadsheets have this disable/enable box pop up when she goes into
them.

Thanks for your assistance in advance

 

[luv2bike2]

Thank you Rick,

I understand now that i have to put it in one workbook but the thing is,
this user will be importing a report from crystal reports to excel. is that
possible where i can put the macro in one workbook and import a report into
that workbook everytime and still have the macro?

thanks,

 

[Rick Rothstein \(MVP - VB\)]

If I understand your question correctly, simply importing data into a
workbook will not affect any macros in that workbook... the macro is saved
with the workbook.

Rick

 

[luv2bike2]

I will try setting up the marco next week in a workbook and import a report
into it and see how it goes.
I will report back what my findings are.

thank you very much for your assistance.

 

[Dave Mills]

If none of these suggestion work for you then you could consider using Code
Signing. If the macro you provide in the workbook is "Signed" then the user will
not get prompted once they have the certificate you used to sign the macro
installed on their PC. This is harder to learn to do but has the advantage that
the user will not need to enable the macro ever. Macro that are not signed will
still pop up the question.