Is it possible?

[cstimart]

I highly doubt that it is possible, but hopefully I can get a definitive answer here. (Excel 2007)

I have a macro that I have created in a file that has a pivot table w/an ODBC connection. This is used by several people in my office and some of these people are paranoid about macro code being corrupt, malicious, etc...

I could just move the code into my personal.xlsb file and the macro would work fine. However, I have built into this macro/file a flag that will not allow the file to be saved after the macro has run (to protect the common pivot table format).

I assume there is no way to incorporate that into my personal file, so I can save the shared file in a .xlsx format?

Thanks for the help!

 

[doofusboy]

Can't you just comment out the code that won't allow the file to be saved after the macro has been run? And then uncomment it after you've moved the file/macro?

 

[cstimart]

Not sure you understood my question.

Basically, this macro has been in the file for a while and noby knew about it except me, as it was an .xls (Office 2003) format. However, now that we've converted to Office 2007, the .xlsm makes people freak out.

My intent is to retain the macro, but utilize it from my personal file. However, the code to prevent the saving does not live in a Module, it's in the ThisWorkbook tab.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Saved = (MacroRunFlag = True)
End Sub
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If MacroRunFlag = True Then
    MsgBox "File will not save after running the Macro."
    Cancel = MacroRunFlag
    Saved = True
End If
End Sub

MacroRunFlag in a Public/Boolean variable.

 

[Peter_SSs]

.. this macro has been in the file for a while and noby knew about it except me, as it was an .xls (Office 2003) format.

Didn't the other users get a prompt about enabling macros when they opened the Excel 2003 file?

 

[cstimart]

Didn't the other users get a prompt about enabling macros when they opened the Excel 2003 file?

Nope

 

[Peter_SSs]

Nope

That would seem to mean that in Excel 2003 their macro security setting was either ..

a) Very High. Which means the macro would have been disabled anyway and you can replicate that in 2007 with 'Disable all macros without notification'

or

b) Low. This can be replicated in 2007 with 'Enable all macros'

 

[cstimart]

That would seem to mean that in Excel 2003 their macro security setting was either ..

a) Very High. Which means the macro would have been disabled anyway and you can replicate that in 2007 with 'Disable all macros without notification'

or

b) Low. This can be replicated in 2007 with 'Enable all macros'

The IS Department has changed the setup in regards to the macros...either way, the .xlsm format does give away the presence of the macro and that causes the freak out.

Ideally, I would like to have the shared file in the .xlsx format.

 

[Peter_SSs]

Ideally, I would like to have the shared file in the .xlsx format.

I can't think of a way to do that. Could you not save it in .xls format still?