Macro to check workbook

Topic

New Reply

I want a macro to run every time someone opens an Excel file sent to them for posting in the General Ledger system. I have a macro Checkaccts that does the test, but sometimes processors forget to run it and things get missed. So I’m looking for an event macro and came up with this.

Sub App_WorkbookOpen(ByVal Wb As Workbook)

Active.Workbook.Range(“b12”).Select
If ActiveCell.Value = “Journal Description” Then
Checkaccts
End If

End Sub

I want to give this out to the processors for their own Personal.xls workbooks (that have Checkaccts in it) but it doen’t work. Someone said it’s because Excel is thinking Personal.xls is the workbook at issue, but when I open a new workbook, shouldn’t that become the active workbook? Any suggestions? Thanks.

Reply | Quote

Replies

Active.Workbook.Range(“b12”) isn’t well-defined. Apart from the fact that it’s ActiveWorkbook, not Active.Workbook, what if the workbook has more than one worksheet? What if there is a worksheet with “Journal Description” in cell B12, but that sheet is not the active one?
Your macro will need to be modified depending on the answers.

Furthermore, in order to use application-level events, you need to do a little bit more. See How to create application-level event handlers in Excel and Application Events.

Reply | Quote

Active.Workbook.Range(“b12”) isn’t well-defined. Apart from the fact that it’s ActiveWorkbook, not Active.Workbook, what if the workbook has more than one worksheet? What if there is a worksheet with “Journal Description” in cell B12, but that sheet is not the active one?
Your macro will need to be modified depending on the answers.

Furthermore, in order to use application-level events, you need to do a little bit more. See How to create application-level event handlers in Excel and Application Events.

This is brilliant. I go to the Microsoft Article and try to print it…the bulleted data doesn’t print, even when I select the Print this page button. Any idea why?

Reply | Quote

I go to the Microsoft Article and try to print it…the bulleted data doesn’t print, even when I select the Print this page button. Any idea why?

No, but try printing it from Firefox

Reply | Quote

This is brilliant. I go to the Microsoft Article and try to print it…the bulleted data doesn’t print, even when I select the Print this page button. Any idea why?

I read the article and now want to have the macro test to see if there is a tab “Journal” in the workbook, to address the issue cited, and if so, run the macro, if not, end. So how do I frame that test? How does one say in activeworkbook.sheets.names??? includes “Journal” then do the sub, else end? One other question: the article seemed to say you have to run the Test sub to get the workbook open event macro to run. Is that true? Thanks.

Reply | Quote

The code could look like this:

In the class module:

Code:

Public WithEvents app As Application

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
  Dim ws As Worksheet
  Dim lngErr As Long
  On Error Resume Next
  Set ws = Wb.Worksheets("Journal")
  ' If there is no worksheet named Journal,
  ' lngErr will be unequal to 0
  lngErr = Err.Number
  On Error GoTo 0
  If Not lngErr = 0 Then
	' Code to correct problem here?
  Else
	' Sheet exists
  End If
End Sub

At the top of a standard module:

Code:

Public myClass As New Class1

where Class1 is the name of the class module.

In the ThisWorkbook module:

Code:

Private Sub Workbook_Open()
  Set myClass.app = Application
End Sub

The latter procedure is called each time the workbook is opened. It initializes the app variable and thereby gets the ball rolling.

Reply | Quote

The code could look like this:

In the class module:

Code:

Public WithEvents app As Application

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
  Dim ws As Worksheet
  Dim lngErr As Long
  On Error Resume Next
  Set ws = Wb.Worksheets("Journal")
  ' If there is no worksheet named Journal,
  ' lngErr will be unequal to 0
  lngErr = Err.Number
  On Error GoTo 0
  If Not lngErr = 0 Then
	' Code to correct problem here?
  Else
	' Sheet exists
  End If
End Sub

At the top of a standard module:

Code:

Public myClass As New Class1

where Class1 is the name of the class module.

In the ThisWorkbook module:

Code:

Private Sub Workbook_Open()
  Set myClass.app = Application
End Sub

The latter procedure is called each time the workbook is opened. It initializes the app variable and thereby gets the ball rolling.

Ok, I copied the above into my Personal.xls and fiddled with it until it worked on the first workbook I opened that had a Jorunal tab with the account number range in it. When I tried a second workbook, nothing happened. The macro should check each workbook that gets opened in Excel. The processors open dozens a day and any one may or may not have an account to be checked. But we’re making progress! Thanks.

Reply | Quote

Ok, I copied the above into my Personal.xls and fiddled with it until it worked on the first workbook I opened that had a Jorunal tab with the account number range in it. When I tried a second workbook, nothing happened. The macro should check each workbook that gets opened in Excel. The processors open dozens a day and any one may or may not have an account to be checked. But we’re making progress! Thanks.

Are you sure that you copied each of the parts into the correct module? I’ve tested the code, and it worked for EACH workbook that was opened.

Reply | Quote

Are you sure that you copied each of the parts into the correct module? I’ve tested the code, and it worked for EACH workbook that was opened.

I put the Public myClass As New Class1 in a Module named Woodys_763868.
In the Personal xls “ThisWorkbook” module I have the following:
Sub ChangeTitlebar()
ActiveWindow.Caption = ActiveWorkbook.FullName

End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName & “/” & ” &A” & vbCr
ActiveSheet.PageSetup.RightFooter = “&D &T”
ActiveSheet.PageSetup.CenterFooter = “&P of &N”

End Sub

Private Sub Workbook_Open()
Set myClass.app = Application
End Sub

And finally, I have the piece de resistance in the Class1 module:

Public WithEvents app As Application

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
Dim ws As Worksheet
Dim lngErr As Long
On Error Resume Next
Set ws = Wb.Worksheets(“Journal”)
‘ If there is no worksheet named Journal,
‘ lngErr will be unequal to 0
lngErr = Err.Number
On Error GoTo 0
If Not lngErr = 0 Then

‘ Code to correct problem here?
Else
Wb.Worksheets(“Journal”).Select

FixedAssets
‘ Sheet exists
End
End If
End Sub

It happend again today-predictably-that it worked on the first workbook I opened but not the second. I basically was flipping the call to the FixedAssets test sub until I got it to work at all, but it may be in the wrong spot. Thanks!

Reply | Quote

Remove the line that only contains “End”:

Code:

...
	FixedAssets
	' Sheet exists
	End ' ************ remove this line ***********
  End If
End Sub

This line clears all variables!

Reply | Quote

Remove the line that only contains “End”:

Code:

...
	FixedAssets
	' Sheet exists
	End ' ************ remove this line ***********
  End If
End Sub

This line clears all variables!

Looks like it! Will test with others now. Does that “bad statement” affect just that section or the Class1Module and the other module as well?

Reply | Quote

From the VBA help (available on the net in End Statement):

When executed, the End statement resets all module-level variables and all static local variables in all modules. To preserve the value of these variables, use the Stop statement instead. You can then resume execution while preserving the value of those variables.

Note: The End statement stops code execution abruptly, without invoking the Unload, QueryUnload, or Terminate event, or any other Visual Basic code. Code you have placed in the Unload, QueryUnload, and Terminate events of forms and class modules is not executed. Objects created from class modules are destroyed, files opened using the Open statement are closed, and memory used by your program is freed. Object references held by other programs are invalidated.

So End is a kind of emergency break, it terminates code execution ruthlessly.

Reply | Quote

I got the setup to work on my pc. I copied each module, and the class module, into someone else’s Personal.xls and added the This Workbook statement to their personal xls file. When I closed Excel and restarted it, and opened a test file, I got an Error 461 message, which I cannot interpret. I carefully reviewed the macros etc. and they look ok. When I inserted a Class Module, it created one called Class1 and then I imported the class module with the code in it, renaming it Class11. I copied it into the CLass1 module, but could not delete the other one. How does one do that? Also, with this stuff in the Personal Xls file, when it calls FixedAssets, that macro suddenly bombs because it says it cannot reset the interionr color to red. Never happened before. Ideas? I’ll keep trying. Thanks!!

Reply | Quote

Where does the error 461 occur? It means that something doesn’t exist.

Also, since you haven’t given us the code for FixedAssets, it’s impossible for us to tell why it fails.

Reply | Quote

Where does the error 461 occur? It means that something doesn’t exist.

Also, since you haven’t given us the code for FixedAssets, it’s impossible for us to tell why it fails.

Ok, let me get that to you shortly. thanks.

Reply | Quote

Sorry this took so long..here’s the code i have on someone else’s personal xls to run the test. It works on mine but I get that error msg on this uint. Lines have been temporairly rem’d out to avoid problemsThanks.
Class1:
‘Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
‘Dim ws As Worksheet
‘ Dim lngErr As Long
‘ On Error Resume Next
‘ Set ws = Wb.Worksheets(“Journal”)
‘ If there is no worksheet named Journal,
‘ lngErr will be unequal to 0
‘ lngErr = Err.Number
‘ On Error GoTo 0
‘ If Not lngErr = 0 Then

‘ Code to correct problem here?
‘ Else
‘ Wb.Worksheets(“Journal”).Select

‘ FixedAssets
‘ Sheet exists
‘ End If
‘End Sub

In Module 1:
Sub FixedAssets()
Dim msg As Variant
Dim NaturalCell As Range
Range(“e18”).Select
Range(ActiveCell, “e2000”).Select

For Each NaturalCell In Selection
If NaturalCell.Value > 160000 And NaturalCell.Value <= 180000 Then
With NaturalCell
.Interior.Color = vbRed
.Font.Color = vbWhite
End With
msg = True
End If
Next NaturalCell
If msg = True Then
MsgBox "Fixed Assets Involved!!!"
Else
MsgBox "No Fixed Asset accounts found"
End If
End Sub

And in the This Workbook Module/Workbook Open
Private Sub Workbook_Open()
Set myClass.app = Application

End Sub

Hope this is enough. I'll keep trying.

Reply | Quote

You don’t mention the declarations

Public WithEvents app As Application

in Class1, and

Public myClass As New Class1

in the standard module. I assume that they are present in the real code.

I have no idea why it doesn’t work. I have tested the code again (with the above declarations), and I don’t get an error message.
You still haven’t told us exactly where the error occurs.

Reply | Quote

I checked and rechecked and I have exactly what works in my workbook, which is a copy of what was originally suggested. The Excel I am trying to get this on opens up without any workbook or worksheet open. Is that why I get “Method ‘Range of object’_Global failed”? The macro seems to execute automatically until the statement Range (“e18”).Select is reached, then it stops. I stop the debugger, open a blank worksheet, then open a file that should trigger the FixedAsset macro. Is it too late by then? Thanks.

Reply | Quote

The problem could occur if the user’s Personal.xls workbook contains a worksheet named Journal, or if the user tries to open any workbook that contains a hidden sheet named Journal.

Reply | Quote

The problem could occur if the user’s Personal.xls workbook contains a worksheet named Journal, or if the user tries to open any workbook that contains a hidden sheet named Journal.

making progress! we have Excel opening up now with a blank worksheet, but the macro now runs every time, instead of stopping when it cannot find a sheet named Journal. Also, how do I delete the Class11 object? I think it might be gumming things up.

Reply | Quote

To delete a module or class module, right-click it in the Project Explorer on the left hand side of the Visual Basic Editor, and select Remove … from the popup menu. Answer No to the question whether you want to export it before removing it.

The code in the class module

Code:

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
  Debug.Print Wb.Name
  Dim ws As Worksheet
  Dim lngErr As Long
  On Error Resume Next
  Set ws = Wb.Worksheets("Journal")
  'If there is no worksheet named Journal,
  'lngErr will be unequal to 0
  lngErr = Err.Number
  On Error GoTo 0
  If Not lngErr = 0 Then
	' Code to correct problem here?
  Else
	Wb.Worksheets("Journal").Select
	FixedAssets
	' Sheet exists
  End If
End Sub

shouldn’t do anything if the workbook being opened doesn’t contain a sheet named “Journal”. It only calls FixedAssets if there is such a sheet in the workbook.

Reply | Quote

Ok, thanks. I’ll try this asap.

Reply | Quote