Help! Combine Macros
Hi,
I have two codes, (attached below) that I need to enter on the workbook. The first code is to hide all the menus, toolbars etc. The second item is to close the file if it stays inactive for 5 minutes. First question - is it possible to change the file close macro so it closes excel and the file rather than just the file? Secondly I can get both of these to run independently but not at the same time in the same folder. Could some-one help me to either rewrite the code or tell me how to get them both to run? Thanks a million! ----------------------------------------------------------------- Code 1 - menu and toolbar hide Private mFormulaBar Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim oCB As CommandBar For Each oCB In Application.CommandBars oCB.Enabled = True Next oCB Application.DisplayFormulaBar = mFormulaBar End Sub Private Sub Workbook_Open() Dim oCB As CommandBar For Each oCB In Application.CommandBars oCB.Enabled = False Next oCB mFormulaBar = Application.DisplayFormulaBar Application.DisplayFormulaBar = False End Sub ------------------------------------------------------------------------- Code 2 - file close after 5 minutes of inactivity Public RunWhen As Double Public Const NUM_MINUTES = 10 Public Sub SaveAndClose() ThisWorkbook.Close savechanges:=True End Sub Private Sub Workbook_Open() 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 Application.OnKey "%{F11}", "dummy" End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.OnTime RunWhen, "SaveAndClose", , False On Error GoTo 0 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 |
Help! Combine Macros
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 ------------------------------------------------------------------------- Code 2 - file close after 5 minutes of inactivity Public RunWhen As Double Public Const NUM_MINUTES = 10 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 -- __________________________________ HTH Bob "LiAD" wrote in message ... Hi, I have two codes, (attached below) that I need to enter on the workbook. The first code is to hide all the menus, toolbars etc. The second item is to close the file if it stays inactive for 5 minutes. First question - is it possible to change the file close macro so it closes excel and the file rather than just the file? Secondly I can get both of these to run independently but not at the same time in the same folder. Could some-one help me to either rewrite the code or tell me how to get them both to run? Thanks a million! ----------------------------------------------------------------- Code 1 - menu and toolbar hide Private mFormulaBar Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim oCB As CommandBar For Each oCB In Application.CommandBars oCB.Enabled = True Next oCB Application.DisplayFormulaBar = mFormulaBar End Sub Private Sub Workbook_Open() Dim oCB As CommandBar For Each oCB In Application.CommandBars oCB.Enabled = False Next oCB mFormulaBar = Application.DisplayFormulaBar Application.DisplayFormulaBar = False End Sub ------------------------------------------------------------------------- Code 2 - file close after 5 minutes of inactivity Public RunWhen As Double Public Const NUM_MINUTES = 10 Public Sub SaveAndClose() ThisWorkbook.Close savechanges:=True End Sub Private Sub Workbook_Open() 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 Application.OnKey "%{F11}", "dummy" End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.OnTime RunWhen, "SaveAndClose", , False On Error GoTo 0 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 |
Help! Combine Macros
Thanks a lot ...... again!
"Bob Phillips" wrote: 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 ------------------------------------------------------------------------- Code 2 - file close after 5 minutes of inactivity Public RunWhen As Double Public Const NUM_MINUTES = 10 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 -- __________________________________ HTH Bob "LiAD" wrote in message ... Hi, I have two codes, (attached below) that I need to enter on the workbook. The first code is to hide all the menus, toolbars etc. The second item is to close the file if it stays inactive for 5 minutes. First question - is it possible to change the file close macro so it closes excel and the file rather than just the file? Secondly I can get both of these to run independently but not at the same time in the same folder. Could some-one help me to either rewrite the code or tell me how to get them both to run? Thanks a million! ----------------------------------------------------------------- Code 1 - menu and toolbar hide Private mFormulaBar Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim oCB As CommandBar For Each oCB In Application.CommandBars oCB.Enabled = True Next oCB Application.DisplayFormulaBar = mFormulaBar End Sub Private Sub Workbook_Open() Dim oCB As CommandBar For Each oCB In Application.CommandBars oCB.Enabled = False Next oCB mFormulaBar = Application.DisplayFormulaBar Application.DisplayFormulaBar = False End Sub ------------------------------------------------------------------------- Code 2 - file close after 5 minutes of inactivity Public RunWhen As Double Public Const NUM_MINUTES = 10 Public Sub SaveAndClose() ThisWorkbook.Close savechanges:=True End Sub Private Sub Workbook_Open() 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 Application.OnKey "%{F11}", "dummy" End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.OnTime RunWhen, "SaveAndClose", , False On Error GoTo 0 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 |
All times are GMT +1. The time now is 02:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com