Hello All,
I would like to develop a VBA whereby users are only able to access the
Excel file if they run the VBA code. Is there any way of doing this.

I can stop users (via code):

Closing Userforms etc.
Deleting/Adding rows and columns
Disable the menu

Another thing I thought was to protect the Workbook and only Unprotect it
via VBA code.

If the user choses "Disable Macros" at the security prompt, then they will
have full access to the data. Is there a way to stop this.

Reason why I want this is because the application will be one where
employess can book their annual leave. Each person has a limited number of
days they can book and on each day only a limited number of employees can be
off.

As you can imagine if they have full access they can book what they want!

Any suggestions as to what I can do?

Thanks in advance.

Chas

Hi,
Create a new "welcome" worksheet with a text like "Enable macro on open
In the workbook before close event, hide all sheets (xlVeryHidden) except
the new one.
On open, make all visible and hide the welcome sheet.
Regards
JY
"ChasAA" wrote in message
...
Hello All,
I would like to develop a VBA whereby users are only able to access the
Excel file if they run the VBA code. Is there any way of doing this.

I can stop users (via code):

Closing Userforms etc.
Deleting/Adding rows and columns
Disable the menu

Another thing I thought was to protect the Workbook and only Unprotect it
via VBA code.

If the user choses "Disable Macros" at the security prompt, then they will
have full access to the data. Is there a way to stop this.

Reason why I want this is because the application will be one where
employess can book their annual leave. Each person has a limited number
of
days they can book and on each day only a limited number of employees can
be
off.

As you can imagine if they have full access they can book what they want!

Any suggestions as to what I can do?

Thanks in advance.

Chas

Something I have done to force the use of macros is to create a "Splash
Sheet" with the message "Macros must be Enabled to access this Workbook.
Close & Re-Open with Macros Enabled". Now in your Workbook_Close event, hide
all workbook sheets, but unhide the Splash Sheet. You could go as far as to
make them xlVeryHidden so they won't be listed in Format/Sheets/Unhide menu.
Now in your Workbook_Open event, hide the Splash Sheet and Unhide all other
sheets. As you can see if macros are NOT enabled, the Workbook_Open event
code will not run and only the Splash Sheet message will be visible.
To further protect your code modules from curious eyes you can password
protect the Project from VBEditor.

Mike F
"ChasAA" wrote in message
...
Hello All,
I would like to develop a VBA whereby users are only able to access the
Excel file if they run the VBA code. Is there any way of doing this.

I can stop users (via code):

Closing Userforms etc.
Deleting/Adding rows and columns
Disable the menu

Another thing I thought was to protect the Workbook and only Unprotect it
via VBA code.

If the user choses "Disable Macros" at the security prompt, then they will
have full access to the data. Is there a way to stop this.

Reason why I want this is because the application will be one where
employess can book their annual leave. Each person has a limited number
of
days they can book and on each day only a limited number of employees can
be
off.

As you can imagine if they have full access they can book what they want!

Any suggestions as to what I can do?

Thanks in advance.

Chas

Thanks JY,
I'll give it a go. But mearing mind there will be people that want to get
in and "hack", will they be able to "unhide" the very hidden sheets?

If I was to to put in a password would I be able to enter the password via
code, ie if they chose to disable macros then they would have to enter a
password (which they will not know)

Thanks for the suggestion.

Chas

"Jean-Yves" wrote:

Hi,
Create a new "welcome" worksheet with a text like "Enable macro on open
In the workbook before close event, hide all sheets (xlVeryHidden) except
the new one.
On open, make all visible and hide the welcome sheet.
Regards
JY
"ChasAA" wrote in message
...
Hello All,
I would like to develop a VBA whereby users are only able to access the
Excel file if they run the VBA code. Is there any way of doing this.

I can stop users (via code):

Closing Userforms etc.
Deleting/Adding rows and columns
Disable the menu

Another thing I thought was to protect the Workbook and only Unprotect it
via VBA code.

If the user choses "Disable Macros" at the security prompt, then they will
have full access to the data. Is there a way to stop this.

Reason why I want this is because the application will be one where
employess can book their annual leave. Each person has a limited number
of
days they can book and on each day only a limited number of employees can
be
off.

As you can imagine if they have full access they can book what they want!

Any suggestions as to what I can do?

Thanks in advance.

Chas

Just the question of : how much protection do you want ?
Password crackers would get it before they know how to do it in VBA.
You cannot not make the worksheet via the xl menu, only via code.
Regards
JY

"ChasAA" wrote in message
...
Thanks JY,
I'll give it a go. But mearing mind there will be people that want to get
in and "hack", will they be able to "unhide" the very hidden sheets?

If I was to to put in a password would I be able to enter the password via
code, ie if they chose to disable macros then they would have to enter a
password (which they will not know)

Thanks for the suggestion.

Chas

"Jean-Yves" wrote:

Hi,
Create a new "welcome" worksheet with a text like "Enable macro on open
In the workbook before close event, hide all sheets (xlVeryHidden) except
the new one.
On open, make all visible and hide the welcome sheet.
Regards
JY
"ChasAA" wrote in message
...
Hello All,
I would like to develop a VBA whereby users are only able to access the
Excel file if they run the VBA code. Is there any way of doing this.

I can stop users (via code):

Closing Userforms etc.
Deleting/Adding rows and columns
Disable the menu

Another thing I thought was to protect the Workbook and only Unprotect
it
via VBA code.

If the user choses "Disable Macros" at the security prompt, then they
will
have full access to the data. Is there a way to stop this.

Reason why I want this is because the application will be one where
employess can book their annual leave. Each person has a limited
number
of
days they can book and on each day only a limited number of employees
can
be
off.

As you can imagine if they have full access they can book what they
want!

Any suggestions as to what I can do?

Thanks in advance.

Chas