Startup Macro and Security Check

[fchepote]

Hi everybody!

For the past few days I have been banging my head against my computer screen, as my VB programming skills are almost like my parent's. I want to write a macro that startups with the excel Worksheet and checks for a certain file to be in certain location (to avoid mass distribution of the file). If the file is not there, a dialog box appears saying "You are not allowed to use this file" (or whatever text) and closes the workbook. Can anybody help me out?

* It has to be a startup macro

* It has to be enabled to make the worksheet visible. If the macro is turned off, the worksheet should close or not be visible

* It must check a certain file is in the hard drive (for example: c:\windows\system32\msmmpi2.sys)

* When the worksheet is saved, the macro must be saved with it.

Thank you very much

-Felipe Chepote

 

[Smitty]

Welcome to the Board!

The following code requires you to have a sheet called "Notification". On that sheet you need to put a message to your users that they must enable macros. The code goes in the ThisWorkbook module. Change the path to suit.

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    <SPAN style="color:#00007F">If</SPAN> ActiveWorkbook.FullName <> _
        "C:\Documents and Settings\All Users\Desktop\Force Macro Enable.xls" _
    <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "You are not authorized to view this workbook."
        ActiveWorkbook.Close <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
        <SPAN style="color:#00007F">If</SPAN> ws.Name <> "Notification" <SPAN style="color:#00007F">Then</SPAN>
            ws.Visible = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> ws
    
    Sheets("Notification").Visible = xlVeryHidden

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    Sheets("Notification").Visible = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
        <SPAN style="color:#00007F">If</SPAN> ws.Name <> "Notification" <SPAN style="color:#00007F">Then</SPAN>
            ws.Visible = xlVeryHidden
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> ws
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty

EDIT: Left out a snippet