+ Reply to Thread
Results 1 to 3 of 3

Need help figuring out what line of code I need to erase

Hybrid View

  1. #1
    Registered User
    Join Date
    10-11-2016
    Location
    Rock Hill, SC
    MS-Off Ver
    2013
    Posts
    10

    Need help figuring out what line of code I need to erase

    So I have a file with 19 sheets, 18 of those sheets are hidden with the 1 unhidden one being the Home tab. On the home tab there are cells that take you to the hidden sheets when they are double clicked. Once you get to the hidden sheet you are prompted for a password and that will either hide the sheet and send you back to the home page or it will give you access to the sheet. Before I password protected each sheet so it would just send you back I had locked all of the sheets and password protected them and every time you hit save it would collapse all of the groups, lock the page, and send you back to the hone sheet. Now that I've password protecting each sheet I don't need to lock the sheets but I still want them to collapse and send the user back to the home page when they hit save. Below is the code that I'm using to do this, but what I don't know is what do I need to get rid of to keep the functionality the same but remove locking the sheets?

    For the home (Quick Look) sheet:
     Private Sub Worksheet_Activate()
        Dim sh As Worksheet
    
        For Each sh In ThisWorkbook.Sheets
        If sh.Name <> ("Quick Look") Then
        sh.Visible = xlSheetHidden
        End If
        Next sh
        End Sub
        Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Select Case Target.Address
        Case "$A$1"
        Sheets("Axel").Visible = True
        Sheets("Axel").Activate
        Case "$A$11"
        Sheets("Billy").Visible = True
        Sheets("Billy").Activate
        Case "$A$21"
        Sheets("Charles").Visible = True
        Sheets("Charles").Activate
        
        End Select
        End Sub
    For each hidden sheet:
     Private Sub Worksheet_Activate()
        Const Password As String = "Axel"
        If InputBox("Please input the password to view this sheet.") <> Password Then
        Me.Visible = xlSheetHidden
        Else
        Me.Visible = xlSheetVisible
        End If
        End Sub
    And finally for the workbook:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
            Dim sh As Object
            For Each sh In ThisWorkbook.Sheets
                If TypeName(sh) = "Worksheet" Then
                    On Error Resume Next
                    sh.Outline.ShowLevels RowLevels:=1
                    On Error GoTo 0
                End If
            Next sh
            Set sh = Nothing
    
            Sheets("Axel").Protect "Axel"
            Sheets("Billy").Protect "Billy"
            Sheets("Charles").Protect "Charles"
            Sheets("Quick Look").Activate
            
        End Sub
    Last edited by masonsweats; 10-26-2016 at 03:45 PM.

  2. #2
    Registered User
    Join Date
    10-11-2016
    Location
    Rock Hill, SC
    MS-Off Ver
    2013
    Posts
    10

    Re: Need help figuring out what line of code I need to erase

    Here is a example of the worksheet for anyone to use and look at, I know that I do better with things like this when I can actually work on it. This is a condensed version of what I'm actually using but its large enough to get the idea of what I'm trying to do across.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-11-2016
    Location
    Rock Hill, SC
    MS-Off Ver
    2013
    Posts
    10

    Re: Need help figuring out what line of code I need to erase

    I figured it out, for anyone that comes across this post in like three years with the same issue and whats to know how to solve it I just removed:
            Sheets("Axel").Protect "Axel"
            Sheets("Billy").Protect "Billy"
            Sheets("Charles").Protect "Charles"
    and I kept:
    Sheets("Quick Look").Activate

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Erase unnecessary hidden Line Breaks in the cells
    By ybhatt in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 11-14-2013, 01:16 PM
  2. VB Code to erase two cells as data changes in another
    By Nitefox in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 09-30-2013, 12:28 AM
  3. Erase unnecessary Line Breaks in the cells
    By artner0112 in forum Excel General
    Replies: 7
    Last Post: 09-24-2013, 09:26 AM
  4. [SOLVED] Macros code to erase data
    By Brainstorm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-10-2012, 09:06 PM
  5. Erase Code
    By Bill in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-04-2005, 03:06 PM
  6. Erase all drawing items with code
    By daniels012 in forum Excel General
    Replies: 0
    Last Post: 01-25-2005, 03:17 PM
  7. Code to erase certain data
    By daniels012 in forum Excel General
    Replies: 2
    Last Post: 01-11-2005, 05:34 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1