Autostart macro-protect file

M

Mia

Hi,

I have written following code to protect my file from
beeing used from people who shouldent use it. The problem
I have is that you have to activate the macro when you
open the file to get it started, so it don't totaly work.
Do anyone know howe to solv this?

Private Sub Workbook_Open()
If Application.OrganizationName = "Min pc" Then

MsgBox "Hej " & Application.UserName

Else

Application.Quit

End If
End Sub
 
D

Dave Peterson

The only effective way to keep people out of your workbook is to not share it
with them.

Anything that depends on a macro means that macros can be disabled--and it's not
too difficult to break into your code and just turn off the things that do the
checking.

You could give the workbook a nice password to open and only share the password
with trusted co-workers, but even those can be broken.
 
M

Mia

Hi Dave,

I know that I can´t protect the files totaly.
Anyone as you say that are good i excel can open them,
but I want to protect them from "a normal" user.
I have some wery critical information regarding our
business that I don´t want to be spread outside our firm?

Do you know any way to do this automatic, for
example with a macro? If I can chose I dont
want a password.

--
Best regards
Mia


"Dave Peterson" skrev:
 
D

Dave Peterson

You seem to know how to do it with a macro, but you also know that if macros are
disabled, then this won't work.

Harlan Grove recommended that you add a UDF (user defined function) that is
essentially a do nothing function. But if macros are disabled, then the UDF
will fail.

Then you include this function in very important formulas.

Option Explicit
Function iFunc() As Long
iFunc = 1
End Function


Then in an important/complex formula that returns a number:

=if(...)*someotherformula*ifunc()

If macros are disabled (and depending on the version of excel that the user is
using -- and the user hasn't changed any register settings!), then when the file
is opened with macros disabled, the function will return a #NAME? error.

If you do it in enough spots (and all that other stuff is still true), then you
may stop the user from being able to do anything with your workbook.
 
M

Mia

Hello again,

You are right, I know how to do it with a macro and I know that if macros are
disabled, then this won't work.

I tried your formula bu I don´t get it right ( I´m not so god at this as you
are).
If I have my macro like this, how do I write to get it to work?

Private Sub Workbook_Open()
If Application.OrganizationName = "Min pc" Then

MsgBox "Hej " & Application.UserName

Else

Application.Quit

End If
End Sub

Do I have to write a complete new macro and where do I put it?

Thank you for your time!

--
Best regards
Mia


"Dave Peterson" skrev:
 
D

Dave Peterson

The code looks ok to me, but you have to make sure it's in the right spot.

Open the VBE and make sure that code is under the ThisWorkbook module--not
anywhere else.

Then close excel (saving the test workbook) and reopen it with macros enabled to
test.

On the other hand, the UDF that I suggested before goes in a regular module
(insert|Module from within the VBE).

And at the same time, you'll need to make some changes to existing formulas that
return numbers:

=a1*31.323+b9*iFunc()

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
Hello again,

You are right, I know how to do it with a macro and I know that if macros are
disabled, then this won't work.

I tried your formula bu I don´t get it right ( I´m not so god at this as you
are).
If I have my macro like this, how do I write to get it to work?

Private Sub Workbook_Open()
If Application.OrganizationName = "Min pc" Then

MsgBox "Hej " & Application.UserName

Else

Application.Quit

End If
End Sub

Do I have to write a complete new macro and where do I put it?

Thank you for your time!
 
R

ryguy7272

There is no way to be 100% certain that your work can’t be
hacked, stolen, etc. However, I’ve found that the following combination
(both #1 & #2 together) offers pretty good protection:

#1) Tools > Protection > Protect Sheet (or Protect Workbook), then enter a
Password.
#2) Tools > Share Workbook > Allow Changes.

If you really want to protect your data from users, you may be much better
off using Access for your app. Access security can be disabled as well, but
it is harder to do so.


Good luck,
Ryan---
 
D

Dave Peterson

#1. This kind of worksheet and workbook protection is really simple to break.
I wouldn't trust it at all.

#2. I don't understand how sharing a workbook would add any level of
protection. And there are so many features that are unavailable when the
workbook is shared, that many don't use it (and others avoid it because of its
reputation of corrupting workbooks).


There is no way to be 100% certain that your work can’t be
hacked, stolen, etc. However, I’ve found that the following combination
(both #1 & #2 together) offers pretty good protection:

#1) Tools > Protection > Protect Sheet (or Protect Workbook), then enter a
Password.
#2) Tools > Share Workbook > Allow Changes.

If you really want to protect your data from users, you may be much better
off using Access for your app. Access security can be disabled as well, but
it is harder to do so.

Good luck,
Ryan---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top