Hello, I have a workbook that functions mainly through Macros, if you choose
not to run then then you can't get much done. I would like to have a macro
upon opening of the Workbook where a dialog box or whatever prompts the user
to type in a password, if the password is correct then nothing happens, but
if it is not, then it shuts down Excel... anybody know how?

thanks!

Ed

Can be done but what contingency will you have if user chooses to disable macros
when opening the workbook?

Your macro won't run but the workbook will still open in it's useless state.

To properly set up for this type of operation, you would have code when closing
the workbook that hides all sheets except one which has nothing but a message
saying "You have disabled macros. Close workbook and re-open with macros
enabled"

If macros are enabled, workbook opens with sheets unhidden and macros ready to
go.

How do you want to proceed?


Gord Dibben MS Excel MVP

On Thu, 21 Dec 2006 11:20:01 -0800, Ed wrote:

Hello, I have a workbook that functions mainly through Macros, if you choose
not to run then then you can't get much done. I would like to have a macro
upon opening of the Workbook where a dialog box or whatever prompts the user
to type in a password, if the password is correct then nothing happens, but
if it is not, then it shuts down Excel... anybody know how?

thanks!

Hello Gorb, that is a very good idea... this has more of protection that what
I proposed before... but once macros are enabled I would like to make sure
that just certain persons who know the correct password can use it.

For me its the same if the password is typed into a certian cell or in a
dialog box, the only thing I care about is that when you enable macros, first
thing it does is it asks for the password, if the password is incorrect then
it closes the file, if not it leaves you work in it.

Some while ago in this discussion group I came upon this macro (thanks to
the author):

-------------------------
Dim DownTime As Date

Sub SetTime()
DownTime = Now + TimeValue("00:00:05")
Application.OnTime DownTime, "ShutDown"
End Sub

Sub ShutDown()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
-------------------------

So I was thinking maybe I can have a "IF" statement through VBA which
activates this macro or a similar that shuts down the workbook, the thing is
I don't know how to prompt to ask for the password and to use it on the IF
statement... Im learning bit by bit VBA but still can't manage such things!
So whatever solution that gives the result is greatly appreciated!

thanks!




"Gord Dibben" wrote:

Ed

Can be done but what contingency will you have if user chooses to disable macros
when opening the workbook?

Your macro won't run but the workbook will still open in it's useless state.

To properly set up for this type of operation, you would have code when closing
the workbook that hides all sheets except one which has nothing but a message
saying "You have disabled macros. Close workbook and re-open with macros
enabled"

If macros are enabled, workbook opens with sheets unhidden and macros ready to
go.

How do you want to proceed?


Gord Dibben MS Excel MVP

On Thu, 21 Dec 2006 11:20:01 -0800, Ed wrote:

Hello, I have a workbook that functions mainly through Macros, if you choose
not to run then then you can't get much done. I would like to have a macro
upon opening of the Workbook where a dialog box or whatever prompts the user
to type in a password, if the password is correct then nothing happens, but
if it is not, then it shuts down Excel... anybody know how?

thanks!

Ed

Place this in the Thisworkbook module.

Private Sub Workbook_Open()
pword = InputBox("Enter the password here")
If pword < "theword" Then
ActiveWorkbook.Close SaveChanges:=False
End Sub

"theword" will be your choice of password.

Then ToolsVBA Project PropertiesProtection.

Lock for viewing, set a different password on this.

Save and close then re-open to see inputbox message.

If user gets it incorrect, the workbook will close.

If you wanted to get really ambitious you could create a userform instead of the
inputbox which would allow masking ******* of the password so's bystanders could
not see what was entered.

But.......all this will be defeated if user disables macros on opening.

Also remember that Excel VBA Project Protection passwords can be
cracked......not easily.........but can be by a knowledgeable person.


Gord


On Thu, 21 Dec 2006 13:58:00 -0800, Ed wrote:

Hello Gorb, that is a very good idea... this has more of protection that what
I proposed before... but once macros are enabled I would like to make sure
that just certain persons who know the correct password can use it.

For me its the same if the password is typed into a certian cell or in a
dialog box, the only thing I care about is that when you enable macros, first
thing it does is it asks for the password, if the password is incorrect then
it closes the file, if not it leaves you work in it.

Some while ago in this discussion group I came upon this macro (thanks to
the author):

-------------------------
Dim DownTime As Date

Sub SetTime()
DownTime = Now + TimeValue("00:00:05")
Application.OnTime DownTime, "ShutDown"
End Sub

Sub ShutDown()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
-------------------------

So I was thinking maybe I can have a "IF" statement through VBA which
activates this macro or a similar that shuts down the workbook, the thing is
I don't know how to prompt to ask for the password and to use it on the IF
statement... Im learning bit by bit VBA but still can't manage such things!
So whatever solution that gives the result is greatly appreciated!

thanks!




"Gord Dibben" wrote:

Ed

Can be done but what contingency will you have if user chooses to disable macros
when opening the workbook?

Your macro won't run but the workbook will still open in it's useless state.

To properly set up for this type of operation, you would have code when closing
the workbook that hides all sheets except one which has nothing but a message
saying "You have disabled macros. Close workbook and re-open with macros
enabled"

If macros are enabled, workbook opens with sheets unhidden and macros ready to
go.

How do you want to proceed?


Gord Dibben MS Excel MVP

On Thu, 21 Dec 2006 11:20:01 -0800, Ed wrote:

Hello, I have a workbook that functions mainly through Macros, if you choose
not to run then then you can't get much done. I would like to have a macro
upon opening of the Workbook where a dialog box or whatever prompts the user
to type in a password, if the password is correct then nothing happens, but
if it is not, then it shuts down Excel... anybody know how?

thanks!

Hello again Gorb, just one more thing... I don't know how to make it to run
this macro upon opening of the file... I tried first inserting the code into
a Module and nothing happens when I open the file. Then I tried at This
Workbook at the "Microsoft Excel Objects" folder and when I open, it gives me
a "error in private module"... so where should I place the code and is it
missing a command in the code to specify run upon opening or so?

,thanks





"Gord Dibben" wrote:

Ed

Place this in the Thisworkbook module.

Private Sub Workbook_Open()
pword = InputBox("Enter the password here")
If pword < "theword" Then
ActiveWorkbook.Close SaveChanges:=False
End Sub

"theword" will be your choice of password.

Then ToolsVBA Project PropertiesProtection.

Lock for viewing, set a different password on this.

Save and close then re-open to see inputbox message.

If user gets it incorrect, the workbook will close.

If you wanted to get really ambitious you could create a userform instead of the
inputbox which would allow masking ******* of the password so's bystanders could
not see what was entered.

But.......all this will be defeated if user disables macros on opening.

Also remember that Excel VBA Project Protection passwords can be
cracked......not easily.........but can be by a knowledgeable person.


Gord


On Thu, 21 Dec 2006 13:58:00 -0800, Ed wrote:

Hello Gorb, that is a very good idea... this has more of protection that what
I proposed before... but once macros are enabled I would like to make sure
that just certain persons who know the correct password can use it.

For me its the same if the password is typed into a certian cell or in a
dialog box, the only thing I care about is that when you enable macros, first
thing it does is it asks for the password, if the password is incorrect then
it closes the file, if not it leaves you work in it.

Some while ago in this discussion group I came upon this macro (thanks to
the author):

-------------------------
Dim DownTime As Date

Sub SetTime()
DownTime = Now + TimeValue("00:00:05")
Application.OnTime DownTime, "ShutDown"
End Sub

Sub ShutDown()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
-------------------------

So I was thinking maybe I can have a "IF" statement through VBA which
activates this macro or a similar that shuts down the workbook, the thing is
I don't know how to prompt to ask for the password and to use it on the IF
statement... Im learning bit by bit VBA but still can't manage such things!
So whatever solution that gives the result is greatly appreciated!

thanks!




"Gord Dibben" wrote:

Ed

Can be done but what contingency will you have if user chooses to disable macros
when opening the workbook?

Your macro won't run but the workbook will still open in it's useless state.

To properly set up for this type of operation, you would have code when closing
the workbook that hides all sheets except one which has nothing but a message
saying "You have disabled macros. Close workbook and re-open with macros
enabled"

If macros are enabled, workbook opens with sheets unhidden and macros ready to
go.

How do you want to proceed?


Gord Dibben MS Excel MVP

On Thu, 21 Dec 2006 11:20:01 -0800, Ed wrote:

Hello, I have a workbook that functions mainly through Macros, if you choose
not to run then then you can't get much done. I would like to have a macro
upon opening of the Workbook where a dialog box or whatever prompts the user
to type in a password, if the password is correct then nothing happens, but
if it is not, then it shuts down Excel... anybody know how?

thanks!

Ed

If you put the code into a general module use the line

Sub Auto_Open() instead of Workbook_Open

Otherwise it goes into Thiswork module.

BTW......I neglected to give you an Or Else if password is correct.

Private Sub Workbook_Open()
Dim pword as String
pword = InputBox("Enter the password here")
If pword < "theword" Then
ActiveWorkbook.Close SaveChanges:=False
Else
msgbox "You're in. Go to Work"
End If
End Sub

Don't know why you're getting the error when you put the code into Thisworkbook
module unless you have an error in syntax or something

Try copying the above directly into Thisworkbook module.

With your workbook open, right-click on the Excel logo left of "File" on the
menu bar or left end of title bar if window is not maximized.

Copy/paste into that module.


Gord

On Fri, 22 Dec 2006 06:57:01 -0800, Ed wrote:

Hello again Gorb, just one more thing... I don't know how to make it to run
this macro upon opening of the file... I tried first inserting the code into
a Module and nothing happens when I open the file. Then I tried at This
Workbook at the "Microsoft Excel Objects" folder and when I open, it gives me
a "error in private module"... so where should I place the code and is it
missing a command in the code to specify run upon opening or so?

,thanks





"Gord Dibben" wrote:

Ed

Place this in the Thisworkbook module.

Private Sub Workbook_Open()
pword = InputBox("Enter the password here")
If pword < "theword" Then
ActiveWorkbook.Close SaveChanges:=False
End Sub

"theword" will be your choice of password.

Then ToolsVBA Project PropertiesProtection.

Lock for viewing, set a different password on this.

Save and close then re-open to see inputbox message.

If user gets it incorrect, the workbook will close.

If you wanted to get really ambitious you could create a userform instead of the
inputbox which would allow masking ******* of the password so's bystanders could
not see what was entered.

But.......all this will be defeated if user disables macros on opening.

Also remember that Excel VBA Project Protection passwords can be
cracked......not easily.........but can be by a knowledgeable person.


Gord


On Thu, 21 Dec 2006 13:58:00 -0800, Ed wrote:

Hello Gorb, that is a very good idea... this has more of protection that what
I proposed before... but once macros are enabled I would like to make sure
that just certain persons who know the correct password can use it.

For me its the same if the password is typed into a certian cell or in a
dialog box, the only thing I care about is that when you enable macros, first
thing it does is it asks for the password, if the password is incorrect then
it closes the file, if not it leaves you work in it.

Some while ago in this discussion group I came upon this macro (thanks to
the author):

-------------------------
Dim DownTime As Date

Sub SetTime()
DownTime = Now + TimeValue("00:00:05")
Application.OnTime DownTime, "ShutDown"
End Sub

Sub ShutDown()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
-------------------------

So I was thinking maybe I can have a "IF" statement through VBA which
activates this macro or a similar that shuts down the workbook, the thing is
I don't know how to prompt to ask for the password and to use it on the IF
statement... Im learning bit by bit VBA but still can't manage such things!
So whatever solution that gives the result is greatly appreciated!

thanks!




"Gord Dibben" wrote:

Ed

Can be done but what contingency will you have if user chooses to disable macros
when opening the workbook?

Your macro won't run but the workbook will still open in it's useless state.

To properly set up for this type of operation, you would have code when closing
the workbook that hides all sheets except one which has nothing but a message
saying "You have disabled macros. Close workbook and re-open with macros
enabled"

If macros are enabled, workbook opens with sheets unhidden and macros ready to
go.

How do you want to proceed?


Gord Dibben MS Excel MVP

On Thu, 21 Dec 2006 11:20:01 -0800, Ed wrote:

Hello, I have a workbook that functions mainly through Macros, if you choose
not to run then then you can't get much done. I would like to have a macro
upon opening of the Workbook where a dialog box or whatever prompts the user
to type in a password, if the password is correct then nothing happens, but
if it is not, then it shuts down Excel... anybody know how?

thanks!





Gord Dibben MS Excel MVP

After right-click on logo select "View Code" to open Thisworkbook module.

I should quit posting while I'm thinking "I really should start my Christmas
shopping"


Gord

On Fri, 22 Dec 2006 09:23:44 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

With your workbook open, right-click on the Excel logo left of "File" on the
menu bar or left end of title bar if window is not maximized.

Copy/paste into that module.

Gord Dibben MS Excel MVP

haha I know, Im also pretty distracted right now! but hey, thank you very
much for your help Gord, I tried the code now it now and it works perfect!
Its exactly what I was looking for!

Have a Merry Christmas and a Happy New Year!

,Ed

Best to you for the season.

Thanks for the feedback and your patience with my dithering.


Gord

On Fri, 22 Dec 2006 10:53:01 -0800, Ed wrote:

haha I know, Im also pretty distracted right now! but hey, thank you very
much for your help Gord, I tried the code now it now and it works perfect!
Its exactly what I was looking for!

Have a Merry Christmas and a Happy New Year!

,Ed

Gord Dibben MS Excel MVP

Hi GORD

FARAZ here!

I sure felt your idea great while you discussed with ED regarding macros to
be necessarily enabled for proper working, by single sheet displaying the
message that macros should be enabled for displaying the other sheets and
hiding the message displaying one, password being asked.

Would you kindly give me such a code?

FARAZ! )

"Gord Dibben" wrote:

Ed

If you put the code into a general module use the line

Sub Auto_Open() instead of Workbook_Open

Otherwise it goes into Thiswork module.

BTW......I neglected to give you an Or Else if password is correct.

Private Sub Workbook_Open()
Dim pword as String
pword = InputBox("Enter the password here")
If pword < "theword" Then
ActiveWorkbook.Close SaveChanges:=False
Else
msgbox "You're in. Go to Work"
End If
End Sub

Don't know why you're getting the error when you put the code into Thisworkbook
module unless you have an error in syntax or something

Try copying the above directly into Thisworkbook module.

With your workbook open, right-click on the Excel logo left of "File" on the
menu bar or left end of title bar if window is not maximized.

Copy/paste into that module.


Gord

On Fri, 22 Dec 2006 06:57:01 -0800, Ed wrote:

Hello again Gorb, just one more thing... I don't know how to make it to run
this macro upon opening of the file... I tried first inserting the code into
a Module and nothing happens when I open the file. Then I tried at This
Workbook at the "Microsoft Excel Objects" folder and when I open, it gives me
a "error in private module"... so where should I place the code and is it
missing a command in the code to specify run upon opening or so?

,thanks





"Gord Dibben" wrote:

Ed

Place this in the Thisworkbook module.

Private Sub Workbook_Open()
pword = InputBox("Enter the password here")
If pword < "theword" Then
ActiveWorkbook.Close SaveChanges:=False
End Sub

"theword" will be your choice of password.

Then ToolsVBA Project PropertiesProtection.

Lock for viewing, set a different password on this.

Save and close then re-open to see inputbox message.

If user gets it incorrect, the workbook will close.

If you wanted to get really ambitious you could create a userform instead of the
inputbox which would allow masking ******* of the password so's bystanders could
not see what was entered.

But.......all this will be defeated if user disables macros on opening.

Also remember that Excel VBA Project Protection passwords can be
cracked......not easily.........but can be by a knowledgeable person.


Gord


On Thu, 21 Dec 2006 13:58:00 -0800, Ed wrote:

Hello Gorb, that is a very good idea... this has more of protection that what
I proposed before... but once macros are enabled I would like to make sure
that just certain persons who know the correct password can use it.

For me its the same if the password is typed into a certian cell or in a
dialog box, the only thing I care about is that when you enable macros, first
thing it does is it asks for the password, if the password is incorrect then
it closes the file, if not it leaves you work in it.

Some while ago in this discussion group I came upon this macro (thanks to
the author):

-------------------------
Dim DownTime As Date

Sub SetTime()
DownTime = Now + TimeValue("00:00:05")
Application.OnTime DownTime, "ShutDown"
End Sub

Sub ShutDown()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
-------------------------

So I was thinking maybe I can have a "IF" statement through VBA which
activates this macro or a similar that shuts down the workbook, the thing is
I don't know how to prompt to ask for the password and to use it on the IF
statement... Im learning bit by bit VBA but still can't manage such things!
So whatever solution that gives the result is greatly appreciated!

thanks!




"Gord Dibben" wrote:

Ed

Can be done but what contingency will you have if user chooses to disable macros
when opening the workbook?

Your macro won't run but the workbook will still open in it's useless state.

To properly set up for this type of operation, you would have code when closing
the workbook that hides all sheets except one which has nothing but a message
saying "You have disabled macros. Close workbook and re-open with macros
enabled"

If macros are enabled, workbook opens with sheets unhidden and macros ready to
go.

How do you want to proceed?


Gord Dibben MS Excel MVP

On Thu, 21 Dec 2006 11:20:01 -0800, Ed wrote:

Hello, I have a workbook that functions mainly through Macros, if you choose
not to run then then you can't get much done. I would like to have a macro
upon opening of the Workbook where a dialog box or whatever prompts the user
to type in a password, if the password is correct then nothing happens, but
if it is not, then it shuts down Excel... anybody know how?

thanks!





Gord Dibben MS Excel MVP

Faraz

You insert a new sheet named "Cover"(no quotes) with the message saying
something like "You have disabled macros and rendered this workbook unusable.
Please close and re-open with macos enabled."

In the Thisworkbook module copy/paste these..............

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Cover").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name < "Cover" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In ActiveWorkbook.Sheets
sht.Visible = xlSheetVisible
Next sht
Sheets("Cover").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub


Gord


On Wed, 27 Dec 2006 20:06:00 -0800, FARAZ QURESHI
wrote:

Hi GORD

FARAZ here!

I sure felt your idea great while you discussed with ED regarding macros to
be necessarily enabled for proper working, by single sheet displaying the
message that macros should be enabled for displaying the other sheets and
hiding the message displaying one, password being asked.

Would you kindly give me such a code?

FARAZ! )

"Gord Dibben" wrote:

Ed

If you put the code into a general module use the line

Sub Auto_Open() instead of Workbook_Open

Otherwise it goes into Thiswork module.

BTW......I neglected to give you an Or Else if password is correct.

Private Sub Workbook_Open()
Dim pword as String
pword = InputBox("Enter the password here")
If pword < "theword" Then
ActiveWorkbook.Close SaveChanges:=False
Else
msgbox "You're in. Go to Work"
End If
End Sub

Don't know why you're getting the error when you put the code into Thisworkbook
module unless you have an error in syntax or something

Try copying the above directly into Thisworkbook module.

With your workbook open, right-click on the Excel logo left of "File" on the
menu bar or left end of title bar if window is not maximized.

Copy/paste into that module.


Gord

On Fri, 22 Dec 2006 06:57:01 -0800, Ed wrote:

Hello again Gorb, just one more thing... I don't know how to make it to run
this macro upon opening of the file... I tried first inserting the code into
a Module and nothing happens when I open the file. Then I tried at This
Workbook at the "Microsoft Excel Objects" folder and when I open, it gives me
a "error in private module"... so where should I place the code and is it
missing a command in the code to specify run upon opening or so?

,thanks





"Gord Dibben" wrote:

Ed

Place this in the Thisworkbook module.

Private Sub Workbook_Open()
pword = InputBox("Enter the password here")
If pword < "theword" Then
ActiveWorkbook.Close SaveChanges:=False
End Sub

"theword" will be your choice of password.

Then ToolsVBA Project PropertiesProtection.

Lock for viewing, set a different password on this.

Save and close then re-open to see inputbox message.

If user gets it incorrect, the workbook will close.

If you wanted to get really ambitious you could create a userform instead of the
inputbox which would allow masking ******* of the password so's bystanders could
not see what was entered.

But.......all this will be defeated if user disables macros on opening.

Also remember that Excel VBA Project Protection passwords can be
cracked......not easily.........but can be by a knowledgeable person.


Gord


On Thu, 21 Dec 2006 13:58:00 -0800, Ed wrote:

Hello Gorb, that is a very good idea... this has more of protection that what
I proposed before... but once macros are enabled I would like to make sure
that just certain persons who know the correct password can use it.

For me its the same if the password is typed into a certian cell or in a
dialog box, the only thing I care about is that when you enable macros, first
thing it does is it asks for the password, if the password is incorrect then
it closes the file, if not it leaves you work in it.

Some while ago in this discussion group I came upon this macro (thanks to
the author):

-------------------------
Dim DownTime As Date

Sub SetTime()
DownTime = Now + TimeValue("00:00:05")
Application.OnTime DownTime, "ShutDown"
End Sub

Sub ShutDown()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
-------------------------

So I was thinking maybe I can have a "IF" statement through VBA which
activates this macro or a similar that shuts down the workbook, the thing is
I don't know how to prompt to ask for the password and to use it on the IF
statement... Im learning bit by bit VBA but still can't manage such things!
So whatever solution that gives the result is greatly appreciated!

thanks!




"Gord Dibben" wrote:

Ed

Can be done but what contingency will you have if user chooses to disable macros
when opening the workbook?

Your macro won't run but the workbook will still open in it's useless state.

To properly set up for this type of operation, you would have code when closing
the workbook that hides all sheets except one which has nothing but a message
saying "You have disabled macros. Close workbook and re-open with macros
enabled"

If macros are enabled, workbook opens with sheets unhidden and macros ready to
go.

How do you want to proceed?


Gord Dibben MS Excel MVP

On Thu, 21 Dec 2006 11:20:01 -0800, Ed wrote:

Hello, I have a workbook that functions mainly through Macros, if you choose
not to run then then you can't get much done. I would like to have a macro
upon opening of the Workbook where a dialog box or whatever prompts the user
to type in a password, if the password is correct then nothing happens, but
if it is not, then it shuts down Excel... anybody know how?

thanks!





Gord Dibben MS Excel MVP

Thanx Gord

Shall try out the same. However any idea about how to restrict a user from
resizing/adding/deleting any of the rows/columns by a macro?

Regards Faraz

"Gord Dibben" wrote:

Faraz

You insert a new sheet named "Cover"(no quotes) with the message saying
something like "You have disabled macros and rendered this workbook unusable.
Please close and re-open with macos enabled."

In the Thisworkbook module copy/paste these..............

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Cover").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name < "Cover" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In ActiveWorkbook.Sheets
sht.Visible = xlSheetVisible
Next sht
Sheets("Cover").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub


Gord


On Wed, 27 Dec 2006 20:06:00 -0800, FARAZ QURESHI
wrote:

Hi GORD

FARAZ here!

I sure felt your idea great while you discussed with ED regarding macros to
be necessarily enabled for proper working, by single sheet displaying the
message that macros should be enabled for displaying the other sheets and
hiding the message displaying one, password being asked.

Would you kindly give me such a code?

FARAZ! )

"Gord Dibben" wrote:

Ed

If you put the code into a general module use the line

Sub Auto_Open() instead of Workbook_Open

Otherwise it goes into Thiswork module.

BTW......I neglected to give you an Or Else if password is correct.

Private Sub Workbook_Open()
Dim pword as String
pword = InputBox("Enter the password here")
If pword < "theword" Then
ActiveWorkbook.Close SaveChanges:=False
Else
msgbox "You're in. Go to Work"
End If
End Sub

Don't know why you're getting the error when you put the code into Thisworkbook
module unless you have an error in syntax or something

Try copying the above directly into Thisworkbook module.

With your workbook open, right-click on the Excel logo left of "File" on the
menu bar or left end of title bar if window is not maximized.

Copy/paste into that module.


Gord

On Fri, 22 Dec 2006 06:57:01 -0800, Ed wrote:

Hello again Gorb, just one more thing... I don't know how to make it to run
this macro upon opening of the file... I tried first inserting the code into
a Module and nothing happens when I open the file. Then I tried at This
Workbook at the "Microsoft Excel Objects" folder and when I open, it gives me
a "error in private module"... so where should I place the code and is it
missing a command in the code to specify run upon opening or so?

,thanks





"Gord Dibben" wrote:

Ed

Place this in the Thisworkbook module.

Private Sub Workbook_Open()
pword = InputBox("Enter the password here")
If pword < "theword" Then
ActiveWorkbook.Close SaveChanges:=False
End Sub

"theword" will be your choice of password.

Then ToolsVBA Project PropertiesProtection.

Lock for viewing, set a different password on this.

Save and close then re-open to see inputbox message.

If user gets it incorrect, the workbook will close.

If you wanted to get really ambitious you could create a userform instead of the
inputbox which would allow masking ******* of the password so's bystanders could
not see what was entered.

But.......all this will be defeated if user disables macros on opening.

Also remember that Excel VBA Project Protection passwords can be
cracked......not easily.........but can be by a knowledgeable person.


Gord


On Thu, 21 Dec 2006 13:58:00 -0800, Ed wrote:

Hello Gorb, that is a very good idea... this has more of protection that what
I proposed before... but once macros are enabled I would like to make sure
that just certain persons who know the correct password can use it.

For me its the same if the password is typed into a certian cell or in a
dialog box, the only thing I care about is that when you enable macros, first
thing it does is it asks for the password, if the password is incorrect then
it closes the file, if not it leaves you work in it.

Some while ago in this discussion group I came upon this macro (thanks to
the author):

-------------------------
Dim DownTime As Date

Sub SetTime()
DownTime = Now + TimeValue("00:00:05")
Application.OnTime DownTime, "ShutDown"
End Sub

Sub ShutDown()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
-------------------------

So I was thinking maybe I can have a "IF" statement through VBA which
activates this macro or a similar that shuts down the workbook, the thing is
I don't know how to prompt to ask for the password and to use it on the IF
statement... Im learning bit by bit VBA but still can't manage such things!
So whatever solution that gives the result is greatly appreciated!

thanks!




"Gord Dibben" wrote:

Ed

Can be done but what contingency will you have if user chooses to disable macros
when opening the workbook?

Your macro won't run but the workbook will still open in it's useless state.

To properly set up for this type of operation, you would have code when closing
the workbook that hides all sheets except one which has nothing but a message
saying "You have disabled macros. Close workbook and re-open with macros
enabled"

If macros are enabled, workbook opens with sheets unhidden and macros ready to
go.

How do you want to proceed?


Gord Dibben MS Excel MVP

On Thu, 21 Dec 2006 11:20:01 -0800, Ed wrote:

Hello, I have a workbook that functions mainly through Macros, if you choose
not to run then then you can't get much done. I would like to have a macro
upon opening of the Workbook where a dialog box or whatever prompts the user
to type in a password, if the password is correct then nothing happens, but
if it is not, then it shuts down Excel... anybody know how?

thanks!





Gord Dibben MS Excel MVP

If running version 2002 or newer you can, when protecting the sheet, disallow
many functions.

Column and row resizing, deleting, inserting are some of these functions.

Go to ToolsProtectionProtect Sheet to see your options.

By macro..........I guess you could use event code to undo anything the user
might try on an unprotected sheet.

I have no such code but someone might chip in with something.


Gord


On Thu, 28 Dec 2006 12:35:01 -0800, FARAZ QURESHI
wrote:

Thanx Gord

Shall try out the same. However any idea about how to restrict a user from
resizing/adding/deleting any of the rows/columns by a macro?

Regards Faraz

"Gord Dibben" wrote:

Faraz

You insert a new sheet named "Cover"(no quotes) with the message saying
something like "You have disabled macros and rendered this workbook unusable.
Please close and re-open with macos enabled."

In the Thisworkbook module copy/paste these..............

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Cover").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name < "Cover" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In ActiveWorkbook.Sheets
sht.Visible = xlSheetVisible
Next sht
Sheets("Cover").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub


Gord


On Wed, 27 Dec 2006 20:06:00 -0800, FARAZ QURESHI
wrote:

Hi GORD

FARAZ here!

I sure felt your idea great while you discussed with ED regarding macros to
be necessarily enabled for proper working, by single sheet displaying the
message that macros should be enabled for displaying the other sheets and
hiding the message displaying one, password being asked.

Would you kindly give me such a code?

FARAZ! )

"Gord Dibben" wrote:

Ed

If you put the code into a general module use the line

Sub Auto_Open() instead of Workbook_Open

Otherwise it goes into Thiswork module.

BTW......I neglected to give you an Or Else if password is correct.

Private Sub Workbook_Open()
Dim pword as String
pword = InputBox("Enter the password here")
If pword < "theword" Then
ActiveWorkbook.Close SaveChanges:=False
Else
msgbox "You're in. Go to Work"
End If
End Sub

Don't know why you're getting the error when you put the code into Thisworkbook
module unless you have an error in syntax or something

Try copying the above directly into Thisworkbook module.

With your workbook open, right-click on the Excel logo left of "File" on the
menu bar or left end of title bar if window is not maximized.

Copy/paste into that module.


Gord

On Fri, 22 Dec 2006 06:57:01 -0800, Ed wrote:

Hello again Gorb, just one more thing... I don't know how to make it to run
this macro upon opening of the file... I tried first inserting the code into
a Module and nothing happens when I open the file. Then I tried at This
Workbook at the "Microsoft Excel Objects" folder and when I open, it gives me
a "error in private module"... so where should I place the code and is it
missing a command in the code to specify run upon opening or so?

,thanks





"Gord Dibben" wrote:

Ed

Place this in the Thisworkbook module.

Private Sub Workbook_Open()
pword = InputBox("Enter the password here")
If pword < "theword" Then
ActiveWorkbook.Close SaveChanges:=False
End Sub

"theword" will be your choice of password.

Then ToolsVBA Project PropertiesProtection.

Lock for viewing, set a different password on this.

Save and close then re-open to see inputbox message.

If user gets it incorrect, the workbook will close.

If you wanted to get really ambitious you could create a userform instead of the
inputbox which would allow masking ******* of the password so's bystanders could
not see what was entered.

But.......all this will be defeated if user disables macros on opening.

Also remember that Excel VBA Project Protection passwords can be
cracked......not easily.........but can be by a knowledgeable person.


Gord


On Thu, 21 Dec 2006 13:58:00 -0800, Ed wrote:

Hello Gorb, that is a very good idea... this has more of protection that what
I proposed before... but once macros are enabled I would like to make sure
that just certain persons who know the correct password can use it.

For me its the same if the password is typed into a certian cell or in a
dialog box, the only thing I care about is that when you enable macros, first
thing it does is it asks for the password, if the password is incorrect then
it closes the file, if not it leaves you work in it.

Some while ago in this discussion group I came upon this macro (thanks to
the author):

-------------------------
Dim DownTime As Date

Sub SetTime()
DownTime = Now + TimeValue("00:00:05")
Application.OnTime DownTime, "ShutDown"
End Sub

Sub ShutDown()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
-------------------------

So I was thinking maybe I can have a "IF" statement through VBA which
activates this macro or a similar that shuts down the workbook, the thing is
I don't know how to prompt to ask for the password and to use it on the IF
statement... Im learning bit by bit VBA but still can't manage such things!
So whatever solution that gives the result is greatly appreciated!

thanks!




"Gord Dibben" wrote:

Ed

Can be done but what contingency will you have if user chooses to disable macros
when opening the workbook?

Your macro won't run but the workbook will still open in it's useless state.

To properly set up for this type of operation, you would have code when closing
the workbook that hides all sheets except one which has nothing but a message
saying "You have disabled macros. Close workbook and re-open with macros
enabled"

If macros are enabled, workbook opens with sheets unhidden and macros ready to
go.

How do you want to proceed?


Gord Dibben MS Excel MVP

On Thu, 21 Dec 2006 11:20:01 -0800, Ed wrote:

Hello, I have a workbook that functions mainly through Macros, if you choose
not to run then then you can't get much done. I would like to have a macro
upon opening of the Workbook where a dialog box or whatever prompts the user
to type in a password, if the password is correct then nothing happens, but
if it is not, then it shuts down Excel... anybody know how?

thanks!





Gord Dibben MS Excel MVP




Gord Dibben MS Excel MVP