Protecting a sheet on startup

[Daniel Cremin]

Hi i need code for my auto_open macro that
can make two worksheets ("Control and Reference
Sheet") and "Processing and Storage" protected
with only unlocked cells being allowed to be
selected. The thing is is that i dont want the
user to see these sheets at all - it should
automatically go to a "main menu sheet" with
the sheet tabs, rows and columns ,etc hidden.
is this possible?

Thanks very very much in advance.

Another macro needs to be ab

[Tom Schreiner]

On 2002-04-12 22:34, Daniel Cremin wrote:
Hi i need code for my auto_open macro that
can make two worksheets ("Control and Reference
Sheet") and "Processing and Storage" protected
with only unlocked cells being allowed to be
selected. The thing is is that i dont want the
user to see these sheets at all - it should
automatically go to a "main menu sheet" with
the sheet tabs, rows and columns ,etc hidden.
is this possible?

Thanks very very much in advance.

Another macro needs to be ab

Sure thing.
I have a similiar setup on one of my workbooks.
add this code to your macro:

'this code will protect and limit your selection to unlocked cells only
Sheets("Control and Reference Sheet").EnableSelection = xlUnlockedCells
Sheets("Processing and Storage").EnableSelection = xlUnlockedCells
Sheets("Control and Reference Sheet").Protect
Sheets("Processing and Storage").Protect

'this code will hide many of Excel's objects
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayOutline = False
.DisplayZeros = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With

With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With

'this code will hide the worksheet menubar
Application.CommandBars("Worksheet Menu Bar").Enabled = False

'this will activate you main menu sheet
WorkSheets("main menu sheet").activate


You will probably want to reverse this when closing your workbook
Just copy this macro, rename it, and change all of the False to True

Hope this helps,
Tom


[ This Message was edited by: TsTom on 2002-04-12 22:50 ]

[Daniel Cremin]

cheers very much i have being trying for months to get someone to tell me how to hide the worksheet menu bar as i couldnt record a macro hiding it. If i want a message box to preeced the hiding can i use an application.screenupdating=false in front of all that code, then have the message box and then make it true.

[Tom Schreiner]

Hi
I do not understand what you are trying to do in your last post???

I would definitely use Application.ScreenUpdating = False


Is this what you mean?

Application.ScreenUpdating = False
msgbox "Display while running code"
'Run hide code here
Application.ScreenUpdating = True?

Tom

[ This Message was edited by: TsTom on 2002-04-12 22:56 ]

[Daniel Cremin]

ok what i want is an unlock facility where on a worksheet there is a text box control linked to a named cell "PasswordIn" - the user enters a
password in and clicks an ok button

if the text in the named cell matches that entered in a cel called "password" on the
protected "Control and Reference Sheet" then the
standard, formatting, forms and drawing toolbars
should become visible, the worksheet "Control and Reference should be selected and be
completly unprotected and its
gridlines should be visible along with its
row and column headers. The sheet tabs and formula bar should be made visible. Before this happens a message box should appear saying "system is now unlocked". Oh and the
password text box should become blank by having the named cell its linked to having its
text set to "".

If an incorrect password is entered in then a
message box should appear saying "incorrect password" and the range "password in" should
have its contents cleared so that the password text box is blank again.

[Tom Schreiner]

Hi
Sounds good too me...
What help do you need as far as your goal here?
Sorry to run off before...Am at work...

Tom

[Daniel Cremin]

well i need a way to be able to be able to clear away the contents of a named range cell on "Unlock Facility" without the user seeing it".

This will be the hidden cell that is the linked
cell of the text box where the password is typed in - ill call it PasswordEntry. If this cell doesnt match the named cell "password" on the protected sheet "Control and Reference Sheet" id love a neat way to be able to reset the contents of it to be "" so that the text field is empty after the user clicks ok to a message box saying the system is unlocked.


If the password matches then i also want to be able to make the text in the hidden cell "PasswordEntry" blank anyway, when the rest of the code is run to unhide the toolbars, unprotect the worksheet "control and reference sheet" and select that sheet. Is there a neat way to be able to clear the text without actually having the cell being seen on screen as being selected?

[Daniel Cremin]

Here is the macro i have written.

Passwordchecker is a Dim statement made equal to the named range cell"PasswordEntry".

I want the following code to run if
"PasswordEntry" is equal to "Password":

Application.CommandBars("Standard").Visible = True
Application.CommandBars("Formatting").Visible = True
Application.CommandBars("Formatting").Visible = True
Application.CommandBars("Forms").Visible = True

Sheets("Control and Reference Sheet").Select
ActiveSheet.Unprotect
With ActiveWindow
.DisplayHeadings = True
.DisplayWorkbookTabs = True
Range("PasswordEntry").Text = ""
MsgBox ("System is now unprotected")
End With
Application.ScreenUpdating = True

If its not then

Range("PasswordEntry").text =""
MsgBox ("System still locked")
End If
End Sub

I used an Else statement to try and run the second bit but excel keeps saying "Else without If" even though my If statement is further up the macro.

Could you write me a little macro that would work because ive spent hours trying with no luck.

[Tom Schreiner]

When I password protect a workbook and the security needs to be a bit above average, I use the registry to store a password/user list...
If you do not want to mess with the registry, then why not just add a worksheet, make it invisible, store your password(s) on that sheet?
Another way would be to simply put your password in some obscure cell and change the font to white. Unless you need higher security than this.
Instead of having a textbox on the sheet to prompt for the password, how about using an input box?

Add this to your auto-open sub and see if this will fit your plans.

This code will prompt the user for a password, and if entered correctly, will run the code of your choice...
If the user enteres the wrong password three times in a row, the workbook will close without saving any changes.

Dim PassWord As String
Dim TryTimes As Byte

TryAgain:
PassWord = InputBox("Please enter your password...")

If Trim(PassWord) <> Range("IA55000") Then
If TryTimes = 2 Then ActiveWorkbook.Close savechanges:=False
MsgBox "The password entered is invalid, please try again..."
TryTimes = TryTimes + 1
GoTo TryAgain
Else
'Run your code here to enable, make visible, ect...
End If

Tom




[ This Message was edited by: TsTom on 2002-04-13 01:18 ]

[Tom Schreiner]

Hey Daniel
The code above was wrong.
I should have tested it beforehand.
I edited the
TryAgain:
Placement
It needs to go above the inputbox statement...
Tom