Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: auto close

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Today is 28.MAR.2002
    A1 is 27.mar.2002

    if file is open today it should not work in anyway but just close.

    how to do it?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    The way to do this is with a little VBA code. You need to open the workbook code module - to do this right click the lower of the 2 Excel icons at the top left of the screen. You should see an option which says View Code - choose it. Now paste this code:-

    Private Sub Workbook_Open()
    If Date > Sheets("Sheet1").Range("A1") Then
    ThisWorkbook.Close False
    End If
    End Sub

    That's the first part sorted. However, this code won't work if the user disables macros. There is no entirely satisfactory solution to getting around this but here's a commonly used method:

    Insert an additional sheet in your workbook and include a message on it saying something like Macros must be enabled to use this workbook. Name the sheet Macros Disabled. Then run this code in a standard module:-

    Sub HideSheets()
    Dim sht As Object

    For Each sht In ThisWorkbook.Sheets
    If sht.Name <> "Macros Disabled" Then
    sht.Visible = xlSheetVeryHidden
    End If
    Next
    End Sub

    This will hide your sheets so that they cannot be hidden with Format, Sheet, Unhide. The only way to unhide them is with code.

    Now save your file. Then in the workbook code module (same place you put the first procedure) replace the old one with these 2 procedures:-

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim sht As Object

    Sheets("Macros Disabled").Visible = xlSheetVisible
    For Each sht In ThisWorkbook.Sheets
    If sht.Name <> "Macros Disabled" Then
    sht.Visible = xlSheetVeryHidden
    End If
    Next
    ThisWorkbook.Save
    End Sub

    Private Sub Workbook_Open()
    Dim sht As Object
    If Date > Sheets("Sheet1").Range("A1") Then
    ThisWorkbook.Close False
    End If
    For Each sht In ThisWorkbook.Sheets
    sht.Visible = xlSheetVisible
    Next
    Sheets("Macros Disabled").Visible = xlSheetVeryHidden
    End Sub


    You will now find that if the user opens the file with macros disabled the only thing they'll see is a worksheet with your 'Macros must be enabled blah blah' message. If they enable macros then that sheet will be hidden and the other sheets will be made visible.

    Long winded, yes! Pain in the a*s!, yes!

    It's probably experimenting with this code on a test workbook so you understand what's happening before doing something nasty to your real workbook.

    HTH,
    Dan




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
  •