Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Protecting a sheet on startup

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •