Results 1 to 4 of 4
  1. #1
    Kurt
    Guest

    Please fix this easy code for me...

    Could someone please fix this code for me..thanks..
    When I run it...I get an error..

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Worksheets("Macros Not enabled").Visible = True
    For Each ws In Worksheets
    If ws.Name "Macros Not Enabled" Then ws.Visible = xlVeryHidden 'this line turned to red
    Next
    End Sub

    Private Sub Workbook_Open()
    For Each ws In Worksheets
    If ws.Name "Macros Not Enabled" Then ws.Visible = True 'this line turned to red
    Next
    Worksheets("Macros Not Enabled").Visible = xlVeryHidden
    End Sub

    The .visible = xlVeryHidden makes the worksheet virtually impossible for a novice to get to
    since it doesn't even show up in the "Hidden list." The BeforeClose EVENT "very hides" all
    of the worksheets and displays the "Macros Not Enabled" worksheet. Next time a user
    opens it up with macros enabled, it hides "Macros Not Enabled" and displays all other
    worksheets. If a user doesn't enable macros, he opens up to the "Macros Not Enabled"
    worksheet and all others are still "very hidden." You can even insert a msg note like "You
    must enable macros to use this workbook!!!!!" on the "Macros NOt Enabled" worksheet.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Please fix this easy code for me...

    Hi Kurt,
    I think you need to change it to:
    If ws.Name <> "Macros not enabled" then ws.Visible = xlVeryHidden
    in the first macro and something similar (except = True at the end) in the second macro.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Dec 2000
    Location
    Indiana
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Please fix this easy code for me...

    Rory beat me to the punch. This code worked in my Excel97 system:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Worksheets("Macros Not enabled").Visible = True
    For Each ws In Worksheets
    If ws.Name <> "Macros Not Enabled" Then ws.Visible = xlVeryHidden 'this line turned to red
    Next
    End Sub

    Private Sub Workbook_Open()
    For Each ws In Worksheets
    If ws.Name <> "Macros Not Enabled" Then ws.Visible = True 'this line turned to red
    Next
    Worksheets("Macros Not Enabled").Visible = xlVeryHidden
    End Sub

  4. #4
    Kurt
    Guest

    Re: Please fix this easy code for me...

    thanks kbelesky..now it works..you are great..
    thanks too to rory..bye

Posting Permissions

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