Macro and signature

[jslmvl]

Hi,

Have set my signature for a macro in Excel 2007 but I still cannot run the macro. What I did was:

set Macro Security to
Disable all macros except digitally signed macros

set my signature to the macro and I can see the signature from
VB windows/Tools/Digital signature/Sign as Certificate name: my signature

Any help will be very appreciated.

 

[WalkerEvans]

From the way you worded your post

but I still cannot run the macro

I'm presuming that this is not in fact code that you wrote. If that presumption is correct simply adding your own signature will not work. Post back and let us know if this is your own code or, as I suspect, code that you got from someone else.


Once we know that someone on this board may be able to help.



----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[ck1999]

Is your macro in a worksheet?

If so does it have an extension of xlsm or xlsx

If xlsx I do not think it will let you run macros. Do a File Saveas to an xlsm workbook and try the macro then..

Office 07 differentiates between Macro Enabled workbook(xlsm) and not

ck1999

 

[jslmvl]

To WalkerEvans

Yes, I coded the Macros.

 

[jslmvl]

To ck1999

I coded the macros in modules and the Excel files were created under older Excel version (2000?) with extension .xls.
Did you mean we need to do SaveAs to .xlsm or .xlsx before we can enable macro in Excel2007?

 

[ck1999]

jslmvl,

Not sure, I was just stating that excel 2007 has 2 files extension instead of 1 like the old excels. So if you were working on and xlsx file then the macro should not work. However, if you are using and xls then what I said does not apply.

Will the macro run if you permit all macros?

ck1999

 

[jslmvl]

Yes, the macros will run perfect if I choose Enable all macros.

 

[WalkerEvans]

jslmvl,

Somehow I missed that you were working in Office 2007; sorry about that. ck1999 was right - if you want your macros to run in Excel 2007 you'll have to save the worksheets using the .xlsm format. Here is an excerpt from the 2007 Help file:

Files that are saved by using the default "x" suffix (such as .docx and .pptx) cannot contain Visual Basic for Applications (VBA) macros or ActiveX controls, and therefore do not pose the security risks associated with those types of embedded code. Only files whose file name extension ends with an "m" (such as .docm and xlsm) can contain VBA macros and ActiveX controls, which are stored in a separate section within the file. The distinct file name extensions make it easy to distinguish files that contain macros from those that don't, and make it easier for antivirus software to identify files that contain potentially malicious code. In addition, IT administrators can block the documents that contain unwanted macros or controls, making it safer to open documents.

Once you have saved in the proper format you may still have to tweak your code, as Microsoft eliminated a few functions with 2007; I have no idea why.

If saving in the xlsm format doesn't work I'm not sure what's next. Post back and let us know if the format change works.



----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[jslmvl]

Thanks a lot.

I have just checked to make sure I did set Macro Security to
"Disable all macros except digitally signed macros"
and the macro has got my signature. I then saved the file as .xlsm file.

However, after open the file, I still cannot run the macro.

 

[HughLerwill]

>Did you mean we need to do SaveAs to .xlsm or .xlsx before we can enable macro in Excel2007?

The MS recommendation is to use the new .xlsm format for Workbooks containing macros under Excel 2007 however macros in old style .xls files do continue to work much as before.

VBA in 2007 is not seriously broken; the FileSearch method is gone though; and pay attention to the SaveAs method which saves to the new style formats by default.

 

[jslmvl]

Thank you.

My problem now is I cannot run macros even in a .xlsm file (except allow all).

 

[ck1999]

Just for Clarification you said

set my signature to the macro and I can see the signature from
VB windows/Tools/Digital signature/Sign as Certificate name: my signature

However, what does the
VB windows/Tools/Digital signature/SignED as Certificate name:
say

I am not sure if this is a typo or if you did not look at this section.

ck1999

 

[jslmvl]

Sorry, what I have down is:

Developer/Macro Security/Disable all macros except digitally signed macros

VB windows/Tools/Digital signature/The VBA project is currently signed as Certificate name: my signature

Save as .xlsm file.

What i cannot do is to run my macro.