Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close help
Hi,
I am trying to use this code to hide the formula bars, menus etc and save and close a file after a certain time has passed, but i can't get it to run. Unfortunately I have reached the limit of what i know to try to fix the problem and get it to run. It errors on this line (line is in two places and both error). ----Application.OnTime RunWhen, "SaveAndClose", , False ------ Any ideas how I can fix this? Thanks LiAD Entered as a workbook code Application.OnTime RunWhen, "SaveAndClose", , False Private mFormulaBar Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim oCB As CommandBar On Error Resume Next Application.OnTime RunWhen, "SaveAndClose", , False On Error GoTo 0 For Each oCB In Application.CommandBars oCB.Enabled = True Next oCB Application.DisplayFormulaBar = mFormulaBar Application.Quit End Sub Private Sub Workbook_Open() Dim oCB As CommandBar On Error Resume Next Application.OnTime RunWhen, "SaveAndClose", , False On Error GoTo 0 Application.OnKey "%{F11}", "dummy" For Each oCB In Application.CommandBars oCB.Enabled = False Next oCB mFormulaBar = Application.DisplayFormulaBar Application.DisplayFormulaBar = False RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0) Application.OnTime RunWhen, "SaveAndClose", , True End Sub Followed by a module of - Public RunWhen As Double Public Const NUM_MINUTES = 5 Public Sub SaveAndClose() ThisWorkbook.Close savechanges:=True End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) On Error Resume Next Application.OnTime RunWhen, "SaveAndClose", , False On Error GoTo 0 RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0) Application.OnTime RunWhen, "SaveAndClose", , True End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) On Error Resume Next Application.OnTime RunWhen, "SaveAndClose", , False On Error GoTo 0 RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0) Application.OnTime RunWhen, "SaveAndClose", , True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close help
haven't asked this already?
anyway if this single line is by itself in the code page for ThisWorkbook, then remove it Application.OnTime RunWhen, "SaveAndClose", , False code must be enclosed in a SUB or Function structure change this Private mFormulaBar to Public mFormulaBar you need to tell us what the errors are. At the top of each Module, type OPTION EXPLICIT this will not just force you to explicitly declare variables, its a great way to prevent typos. Also, before you run code, use the menu Debug/Compile VBAProject item - this will highlight obvious errors. You need a sub named "dummy" in a standard module in order for this line to compile: Application.OnKey "%{F11}", "dummy" "LiAD" wrote in message ... Hi, I am trying to use this code to hide the formula bars, menus etc and save and close a file after a certain time has passed, but i can't get it to run. Unfortunately I have reached the limit of what i know to try to fix the problem and get it to run. It errors on this line (line is in two places and both error). ----Application.OnTime RunWhen, "SaveAndClose", , False ------ Any ideas how I can fix this? Thanks LiAD Entered as a workbook code Application.OnTime RunWhen, "SaveAndClose", , False Private mFormulaBar Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim oCB As CommandBar On Error Resume Next Application.OnTime RunWhen, "SaveAndClose", , False On Error GoTo 0 For Each oCB In Application.CommandBars oCB.Enabled = True Next oCB Application.DisplayFormulaBar = mFormulaBar Application.Quit End Sub Private Sub Workbook_Open() Dim oCB As CommandBar On Error Resume Next Application.OnTime RunWhen, "SaveAndClose", , False On Error GoTo 0 Application.OnKey "%{F11}", "dummy" For Each oCB In Application.CommandBars oCB.Enabled = False Next oCB mFormulaBar = Application.DisplayFormulaBar Application.DisplayFormulaBar = False RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0) Application.OnTime RunWhen, "SaveAndClose", , True End Sub Followed by a module of - Public RunWhen As Double Public Const NUM_MINUTES = 5 Public Sub SaveAndClose() ThisWorkbook.Close savechanges:=True End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) On Error Resume Next Application.OnTime RunWhen, "SaveAndClose", , False On Error GoTo 0 RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0) Application.OnTime RunWhen, "SaveAndClose", , True End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) On Error Resume Next Application.OnTime RunWhen, "SaveAndClose", , False On Error GoTo 0 RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0) Application.OnTime RunWhen, "SaveAndClose", , True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close help
Cheers.
Works now just be removing the two lines and changing priv-public. Yeah I had already asked, twice actually, but didnt make any progress. Thanks a lot for your help "Patrick Molloy" wrote: haven't asked this already? anyway if this single line is by itself in the code page for ThisWorkbook, then remove it Application.OnTime RunWhen, "SaveAndClose", , False code must be enclosed in a SUB or Function structure change this Private mFormulaBar to Public mFormulaBar you need to tell us what the errors are. At the top of each Module, type OPTION EXPLICIT this will not just force you to explicitly declare variables, its a great way to prevent typos. Also, before you run code, use the menu Debug/Compile VBAProject item - this will highlight obvious errors. You need a sub named "dummy" in a standard module in order for this line to compile: Application.OnKey "%{F11}", "dummy" "LiAD" wrote in message ... Hi, I am trying to use this code to hide the formula bars, menus etc and save and close a file after a certain time has passed, but i can't get it to run. Unfortunately I have reached the limit of what i know to try to fix the problem and get it to run. It errors on this line (line is in two places and both error). ----Application.OnTime RunWhen, "SaveAndClose", , False ------ Any ideas how I can fix this? Thanks LiAD Entered as a workbook code Application.OnTime RunWhen, "SaveAndClose", , False Private mFormulaBar Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim oCB As CommandBar On Error Resume Next Application.OnTime RunWhen, "SaveAndClose", , False On Error GoTo 0 For Each oCB In Application.CommandBars oCB.Enabled = True Next oCB Application.DisplayFormulaBar = mFormulaBar Application.Quit End Sub Private Sub Workbook_Open() Dim oCB As CommandBar On Error Resume Next Application.OnTime RunWhen, "SaveAndClose", , False On Error GoTo 0 Application.OnKey "%{F11}", "dummy" For Each oCB In Application.CommandBars oCB.Enabled = False Next oCB mFormulaBar = Application.DisplayFormulaBar Application.DisplayFormulaBar = False RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0) Application.OnTime RunWhen, "SaveAndClose", , True End Sub Followed by a module of - Public RunWhen As Double Public Const NUM_MINUTES = 5 Public Sub SaveAndClose() ThisWorkbook.Close savechanges:=True End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) On Error Resume Next Application.OnTime RunWhen, "SaveAndClose", , False On Error GoTo 0 RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0) Application.OnTime RunWhen, "SaveAndClose", , True End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) On Error Resume Next Application.OnTime RunWhen, "SaveAndClose", , False On Error GoTo 0 RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0) Application.OnTime RunWhen, "SaveAndClose", , True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Before Close Event Hide all Worksheets then Close Application | Excel Programming | |||
Can I stop the close method in an auto close macro | Excel Programming | |||
In Before Close Sub ActiveWorkBook.Close(False) repeat procedure | Excel Programming | |||
Excel shoud not close all active books when clicking close button | Excel Discussion (Misc queries) | |||
excel - Windows close button (x) should only close active workboo. | Setting up and Configuration of Excel |