Link to home
Start Free TrialLog in
Avatar of devg
devg

asked on

URGENT- Auto enabling the Macros

Hi All!
I need to auto enable the macros in my excel sheet so that, when i can do some validations and calulations based on certain conditions.

When i open the file, it probpts for enable/disable. If I say, disable, none of the coding is working. I want to inform the user that if he selects disable macros, the application will close and do the same in action too.

He needs to always select enable macros to access my file.
How to do this?

Thanks in advance
Devanand
Avatar of Dave
Dave
Flag of Australia image

Hi devg,

You cant auto enable macros.

You can force a user to use macros on startup by rendering the file useless otherwise. ie hide all the sheets using very hidden and protect them so that only code on startup can make the sheets visible again.

If you want to go down this path I'll give you an example.

Cheers

Dave
Avatar of [ fanpages ]
[ fanpages ]

Or you can set the registry key to disabled the prompt to "Enable Macros"...

===
Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Options]
"Options6"=dword:00000000
===

This assumes you have version 9.0 of Office/Excel.
...or more precisely:

The Options6 entry in the registry sets the following options:

Bit 0 Show chart tip names
Bit 1 Show chart tip values
Bit 2 Intellimouse roll action. 0 = zoom, 1 = scroll
Bit 3 Set when macro virus warning is enabled


Your own installation may require differing settings of bits 0, 1 & 2, so set bit 3 accordingly.

fp.
...you may find that you need to change any shortcuts to MS-Excel to invoke a VBScript file, or even a ".exe" that sets "Options6" before invoking the EXCEL.exe, and your workbook then resets it back so that the next time a workbook is opened the prompt appears.

I used a similar method with an application written in Visual Basic (for Windows) that ran overnight and launched MS-Excel to do some number crunching.  However, as nobody was around to click [Enable Macros], then the VB code set the third bit of "Options6" before it launched Excel, and after it had received an inter-process message to say that that the Excel VBA code was running it re-set the 3rd bit to ensure that every other user on the machine was warned for the rest of the day.

BFN,

fp.
...so, to recap...

To enable the virus protection, use:

"Options6"=dword:00000008

To disable the virus protection, use:

"Options6"=dword:00000000


Hope this helps.

BFN,

fp.
SOLUTION
Avatar of Dave
Dave
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi devg,

John Walkenbach suggests making your worksheets xlVeryHidden--except for one that displays a message requesting the user to enable macros. A Workbook_Open macro then unhides the hidden worksheets and hides the one with the message. Another macro reverses the hiding process when the workbook is saved. http://j-walk.com/ss/excel/tips/tip100.htm

Cheers!

Brad
Thanks for that link Brad - I didn't realise that this method had been written up on Walkenbach's site

Cheers

Dave
Me neither!

It is a method I have implemented myself for many of my clients not realising it was considered the 'de facto' standard :)
Yeah - I thought I "discovered" it too :)

Avatar of devg

ASKER

Dear All!
Thanks for all your Suggestions.
My Problem is, I am sharing this file in a common folder which has readonly rights. Many people access is using password given by me.
After the password authentication, Its prompts them to enable/disable macros.
They should not press Disable macros. Do I have to set the registry settings for everyone(for all machines?)?
I think its impossible.
The other option suggested was to hide the sheets. How to do that? Whether it will solve the problem?

Pls help me.
To hide a worksheet:
Dim ws as Worksheet
ws.Visible=xlSheetVeryHidden

To unhide:
ws.Visible=xlSheetVisible
To hide and unhide the worksheets (depending on whether macros are enabled), paste the following code into the ThisWorkbook code sheet:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet, wsSplash As Worksheet
Application.ScreenUpdating = False
Application.EnableEvents = False
Set wsSplash = Worksheets("Splash screen")
wsSplash.Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Splash screen" Then ws.Visible = xlSheetVeryHidden
Next ws
Cancel = True
ThisWorkbook.Save
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Splash screen" Then ws.Visible = xlSheetVisible
Next ws
wsSplash.Visible = xlSheetVeryHidden
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Open()
Dim ws As Worksheet, wsSplash As Worksheet
Application.ScreenUpdating = False
Set wsSplash = Worksheets("Splash screen")
wsSplash.Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Splash screen" Then ws.Visible = xlSheetVisible
Next ws
wsSplash.Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub
The Spash screen worksheet would contain a message saying that you can't see any worksheets unless macros are enabled. If you really wanted to be firm about it, you would even protect the structure of the workbook and hide the macro sheets.
Hi again,

Back to the enable/disable macros issue:

You could write a VBScript (.vbs) file, a Visual Basic (for Windows) executable (.exe), or any number of other applications with other development languages that simply accepts a parameter - the MS-Excel filename you wish to open - disables the macros on the local machine (using the Registry changes I summarised above), launches MS-Excel and opens the filename specified, then after verifying that the file is open, could then re-enable macros on the machine, and the application (or VBScript) could then terminate.

The MS-Excel file itself could even re-set the Registry settings if desired.

You would therefore personally not need to touch the settings on anybody's individual machine.

BFN,

fp.


Avatar of devg

ASKER

Please hold, Let me try the suggestions

Thanks and will be back
Hi again,

I was wondering if you needed any further advice with your problem?

BFN,

fp.
Avatar of devg

ASKER

Hi All!
I am sorry,
Nothing helped me, when the user disables the macros and set the security to high or medium
Hi GhostMod,

Sorry - I must have missed devg's last reply.

I have no objections to the deletion, although I am puzzled as to why my advice regarding the modifying of the registry settings did not address the initial question.

Please remove if no more objections are raised.

BFN,

fp.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of devg

ASKER

Hi All!
This file has to be distributed to more than 25 people in the orgranisation with different OS versions.
Many suggested ones are changing the registry settings and using external files like VBS or a VB function, which is possible for me to configure for each one of them. Some are roaming profiles with Laptops. The same guy can move the file to different machines with different security system.

What I wanted is, The file should be indigenous,and should be able to handle the macros without getting help of others.

I think I was not clear in my last clarifications
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of devg

ASKER

hi!
Yes I agree with you. But our management decided not to concentrate much on this issue, as they feel,a simple excel file handling invovoves this much work. They decided to have a Web based Interface with security to view all.

Anyhow, Thanks for your suggestions
Avatar of devg

ASKER

Let me ask the moderator to split the points equally to all.

Thanks
Can you please post (or send me) that VB code you mentioned Fanpages ?

Cheers

Dave
Sorry, Dave, which VB code?

I mentioned so much above - I need to make sure what you are interested in.

Thanks.

BFN,

fp.
Sorry, you talked about involking a VB script program when you ran Excel shortcuts or a VB program to modify the DWORD setting. I'd like to see either.

BTW, I couldn't use the entry ("Options6") you talked about although I found your suggestion at http://www.ntware.com/2000/bugs/excel__3.html 

But I did find it could be done tweaking the entry 'Level' under the following two keys for Excel 2000 & 2003.

HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Security
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security

Cheers

Dave


Hi again Dave,

...I talk a lot about code that would work but never actually code it up to prove the principle (due to time constraints)! :)

In this case, you'd just need a ".vbs" file that does this kind of thing:

=== Start of file: "Run_Excel.vbs" ===

Dim objArguments
Dim objWScript_Shell
Dim strCmd
Dim strParam1

Set objArguments = WScript.Arguments
Set objWScript_Shell = WScript.CreateObject("WScript.Shell")

strParam1 = ""

If objArguments.length = 1 then
   strParam1 = objArguments(0)
End If  

strCmd = "excel " & strParam1

' insert setting of appropriate Registry values here

ReturnCode = objWScript_Shell.Run(strCmd, 1, True)

=== End of file: "Run_Excel.vbs" ===


Save the file, say, as "c:\Run_Excel.vbs", then create a shortcut (on your desktop), to invoke "c:\Run_Excel.vbs f:\open_me.xls" (without the quotes).

When the shortcut is clicked, assuming the file "f:\open_me.xls" exists, it will be opened, but as you can see from the .VBS file listing (above), the registry values can be changed in advance of this.

The Workbook_Close() event of the "f:\open_me.xls" file can then set the registry values back again.

BFN,

fp.

Cheers fp

Now I'm wondering if this thread will be or should be deleted ......
No problem - sorry... I'd forgotten about your original request for the information.

Do you mean you have reservations about security implications & hence the thread should be deleted?

Or, that you hope it isn't removed as it contains useful pointers for others?

!?!
LOL - its too useful!
:)  Perhaps I can find a virus-writing forum to submit it to? ;)