force users enable macros and auto save&close if idle

[alexandruc]

Hello,

Summary: I need a macro that will force users to enable macros in order to use excel. Also I need a macro that will save and close excel automatically if the user is idle for more then "x" minutes/seconds but without messing up the manual save (or not save) and close.

I got the force users enable macros code from here:
vbaexpress.com/kb/getarticle.php?kb_id=379 (made by Ken Puls)

The problem appears when I try to make this code work with another code for saving closing the worbook automaticaly if the user is idle more then "x" minutes/seconds.

I figure the problem comes from the customsave rutine, but I have no ideea on how trick it when the save is done automaticaly without affecting the manual save and close or just close.

The code I tryed to integrate with Ken's code is the following:
Private Changed As Boolean
Option Explicit

Private Sub Workbook_Open()
Changed = False
Application.OnTime Now + TimeValue("00:00:15"), procedure:="ThisWorkbook.Auto_Close"
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)
Changed = True
End Sub

Private Sub Auto_Close()
If Changed = False Then
ThisWorkbook.Close
End If
Changed = False
Call Application.OnTime(Now + TimeValue("00:00:15"), "ThisWorkbook.Auto_Close")
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub
The code above was written by Simon Lloyd.

This problem was/is disscussed here:
http://www.thecodecage.com/forumz/members-excel-vba-programming/145619-conflict-between-auto-save-close-macro-show-hide-sheets-macro.html where RoyUK and Simon Lloyd helped me a lot (and I am very gratefull), but I haven't seen a working solution so far.

I also turned to this forum because the person who wrote the enable macros code that works best (from my point of view) is a member here, and he knows his code best, thus hopeing he could help integrate the 2 codes.

Attached is a failed attempt at integrating the the 2 codes.

[Bob Phillips]

You cannot enable macros from code, the best that you can do is to inform them that they must and block access if they don't.

[alexandruc]

You cannot enable macros from code, the best that you can do is to inform them that they must and block access if they don't.

I know. "Force users to enable macros in a workbook" is the name of the macro writen by Ken Plus that shows/hides sheets if macros are enabled/disabled.

The problem is making that code work with an "auto save & close if idle" code without messing the manual save & close...

[Simon Lloyd]

alexandruc, when cross posting you MUST provide links to ALL your other postings, for the reason why check the link in my signature

[Bob Phillips]

I don't see why, they are totally separate events.

Show us what you have tried.

[alexandruc]

alexandruc, when cross posting you MUST provide links to ALL your other postings, for the reason why check the link in my signature
I did provide a link in my first post here and also on the codecage forums, but having under 5 posts I could not make it an URL in here.


This problem was/is disscussed here:
thecodecage.com/forumz/members-excel-vba-programming/145619-conflict-between-auto-save-close-macro-show-hide-sheets-macro.html where RoyUK and Simon Lloyd helped me a lot (and I am very gratefull), but I haven't seen a working solution so far.
edit:

I don't see why, they are totally separate events.

Show us what you have tried.

In my first post there is an attachment containing the code...

[Simon Lloyd]

I did provide a link in my first post here and also on the codecage forums, but having under 5 posts I could not make it an URL in here.The ones we are aware of are:
conflict between auto save&close macro and show/hide sheets macro - Excel Help Forum (http://www.excelforum.com/excel-programming/703594-conflict-between-auto-save-and-close-macro-and-show-hide-sheets-macro.html)

conflict between auto save&close macro and show/hide sheets macro - dBforums (http://www.dbforums.com/microsoft-excel/1649168-conflict-between-auto-save-close-macro-show-hide-sheets-macro.html)

conflict between auto save&close macro and show/hide sheets macro - MrExcel Message Board (http://www.mrexcel.com/forum/showthread.php?t=423331)

http://www.thecodecage.com/forumz/members-excel-vba-programming/145619-conflict-between-auto-save-close-macro-show-hide-sheets-macro.html

[Simon Lloyd]

Bob, if you have the time the thread at The Code Cage is extensive and has many attachments, however check the last post.

[Bob Phillips]

You are making it far too complex.

Put this code in Thisworkbook



Const TimeInterval As String = "00:01:00"
Const OnTimeMacro As String = "MyClose"
Private vOldVal 'Must be at top of module

Private Auto As Boolean
Private nTime As Double

Const WelcomePage = "Macros"
Const pwd = "321"

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim sFile
Call HideAllSheets

Application.EnableEvents = False

If SaveAsUI Then

sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If sFile <> False Then

Me.SaveAs sFile
End If
Else

Me.Save
End If
Application.EnableEvents = True

'cancel the timer
Application.OnTime nTime, OnTimeMacro, , False

End Sub

Private Sub Workbook_Open()

Changed = False

'Unhide all worksheets
Application.ScreenUpdating = False
Call ShowAllSheets
Application.ScreenUpdating = True

' create our shutdown timer
nTime = Now + TimeValue(TimeInterval)

Application.OnTime nTime, OnTimeMacro

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Changed = True
Application.OnTime nTime, OnTimeMacro, , False
nTime = Now + TimeValue(TimeInterval)
Application.OnTime nTime, OnTimeMacro
End Sub


Private Sub HideAllSheets()
'Hide all worksheets except the macro welcome page
Dim ws As Worksheet
Worksheets(WelcomePage).Visible = xlSheetVisible

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
Next ws

Worksheets(WelcomePage).Activate
End Sub

Private Sub ShowAllSheets()
'Show all worksheets except the macro welcome page

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Next ws

Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub


and this in a standard code module



Public Changed As Boolean

Public Sub MyClose()
If Changed Then ThisWorkbook.Save
ThisWorkbook.Close savechanges:=False
End Sub

[alexandruc]

something is not working right:

Problem A:
1. I open the workbook
2. I manualy save
3. The workbook goes to welcome page and hides the rest of the pages...

Problem B:
1. I open the workbook
2. I choose enable macros
3. I type something in a cell and wait
4. X time later, excel auto saves and closes
5. I open the workbook again
6. I choose DISABLE macros
7. Excel opens on the sheets that are supposed to be hidden.

Attached is document with your code in it