Stop WorkbookOpen and WorkbookBeforeClose Macros
Posted by Zif on June 07, 2000 3:49 AM
I have a number of departmental "input" spreadsheets that are completed on a daily basis. These sheets have both a WorkbookOpen and a WorkbookBeforeClose macro that do various things including checking that all cells in the spreadsheet have been completed.
I have another spreadsheet with a macro that opens each of the "input" files each afternoon, copies the data to a "historic" file, clears the previous data and then saves and closes the "input" files.
What I would like is for the Open and Close macros to run whenever the departments access the files, but when I open the files using my "copy and clear" macro for the Open and Close macros to be temporarily disabled.
Any suggestions?
Zif
Posted by Ryan on June 16, 0100 8:39 PM
Zif,
You can use a WorkBookisOpen function to see if the file you use to "copy and clear" is open and if it is skip the code and if it isn't, they run. Here is some sample code with the Function.
Ryan
Private Sub Workbook_Open()
If WorkbookIsOpen(nameofcopyandclearworkbook) = False Then
'copy and clear code
Else
Exit Sub
End Sub
Private Function WorkbookIsOpen(wbName) As Boolean
' Returns TRUE if the workbook is open
Dim X As Workbook
On Error Resume Next
Set X = Workbooks(wbName)
If Err = 0 Then WorkbookIsOpen = True _
Else WorkbookIsOpen = False
Err = 0
End Function
