Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='777799' date='01-Jun-2009 10:49']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.[/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?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Torquemada' post='777814' date='01-Jun-2009 17:55']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?[/quote]
    No, but try printing it from Firefox

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Torquemada' post='777814' date='01-Jun-2009 11:55']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?[/quote]
    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='777874' date='01-Jun-2009 16:00']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.[/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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Torquemada' post='778052' date='02-Jun-2009 22:46']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.[/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.

  9. #9
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='778067' date='02-Jun-2009 18:22']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.[/quote]
    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!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Remove the line that only contains "End":

    Code:
    ...
    	FixedAssets
    	' Sheet exists
    	End ' ************ remove this line ***********
      End If
    End Sub
    This line clears all variables!

  11. #11
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='778263' date='03-Jun-2009 17:42']Remove the line that only contains "End":

    Code:
    ...
    	FixedAssets
    	' Sheet exists
    	End ' ************ remove this line ***********
      End If
    End Sub
    This line clears all variables![/quote]
    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?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  13. #13
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!!

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  15. #15
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='779401' date='11-Jun-2009 15:53']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.[/quote]
    Ok, let me get that to you shortly. thanks.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •