Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Help! Combine Macros
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 386
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,420
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 386
Default 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




Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Combine 3 Macros LiAD Excel Programming 4 April 22nd 09 07:37 AM
Combine 2 macros into 1 Please. Steved Excel Programming 3 May 24th 07 06:29 PM
combine two macros Lisa Excel Worksheet Functions 1 July 20th 06 02:10 AM
Combine 2 Macros al007 Excel Programming 1 December 29th 05 04:57 PM
Combine 2 macros Steph[_3_] Excel Programming 0 January 20th 04 03:05 PM


All times are GMT +1. The time now is 03:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement