Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Why does a Macro acts on a file that it opens

    I open a file that has a macro to open a second file. Somehow, one of the WS in the 2nd file is calling a macro that is in a Module in the 1st file and, what is worse, when I close the main file it reopens itself. The files are large and could never get them through our firewall so I offer the following description of what is going down in hopes that someone can help.
    The file abstractor_Prod_Monitor_Pivot.XLS opens CURR0.XLS

    Private Sub Workbook_Open()
    ChDir "S:ShareJmoore"
    Workbooks.Open FileName:= _
    "CDAC_OADATA1ShareJMooreCurr0.xls", UpdateLinks:=3
    Workbooks("abstractor_Prod_Monitor_Pivot.XLS").Act ivate
    End Sub
    I don't know why I ended up with "UpdateLinks:=3" in the code. There are no links in either Wb that need to be updated.
    To continue, abstractor_Prod_Monitor_Pivot.XLS also has a macro to close CURR0.xls:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ThisWorkbook.RefreshAll
    End Sub

    There are no Modules in CURR0 and none of the worksheets or ThisWorkBook have any code. The only macro in CURR0.XLS is:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ThisWorkbook.RefreshAll
    End Sub

    Yet, when I click on the WS FTELINKPivot in CURR0 I get the macro failure message shown in the attached Paintbrush file which indicates that the Sub Updateit() has been called and the code "ActiveSheet.PivotTables(iP).RefreshTable" can not be run.

    There are some other macros in abstractor_Prod_Monitor_Pivot.XLS which I can not imagine being the cause of this behavior. Several WS in abstractor_Prod_Monitor_Pivot.XLS have the following code which calls "UpdateIt":

    Private Sub Worksheet_Activate()
    Application.OnSheetActivate = "UpdateIt"
    End Sub

    the UpdateIt" macro is in Module7. All the modules are shown below:

    Module 1:
    Sub countNonBlank() 'Returns a count of non-blank cells in a selection
    Dim myCount As Integer 'using the CountA ws function (all non-blanks)
    myCount = Application.CountA("GT_1_Mod_User")
    MsgBox "The number of non-blank cell(s) in this selection is : " & myCount, vbexplanation, "Count nonBlank Cells"
    End Sub
    Module 2:
    Sub RunALL()
    '
    ' RunALL Macro
    ' Macro recorded 11/17/2000 by FMAS/HCFA
    Range("D2").Select
    ActiveSheet.PivotTables("PivotTable1").RefreshTabl e
    ActiveWorkbook.RefreshAll
    Range("B10").Value = Now()
    End Sub
    Module 3:
    Blank
    Module 4:
    Sub Refreshallinwkbk()
    ThisWorkbook.RefreshAll
    Range("A1").Value = Now()
    End Sub
    Module 5:
    Sub pasteit()
    Worksheets("ALL").Range("N3").Copy
    ActiveSheet.Paste Destination:=Worksheets("ALL").Range("N4:N264")
    End Sub
    Sub fillDown()
    Dim LastRow As Long
    LastRow = Application.CountA(ActiveSheet.Range("F:F"))
    Range("N3:V3").Select
    Selection.AutoFill Destination:=Range("N3:V" & LastRow)
    Range("V3").Select
    End Sub
    Module 6:
    Sub filldown2()
    Dim LastRow As Integer
    LastRow = Application.CountA(ActiveSheet.Range("Z:Z"))
    Range("AE3:AG3").Select
    Selection.AutoFill Destination:=Range("AE3:AG" & LastRow)
    Range("AE3").Select
    End Sub
    Module 7:
    Sub Updateit()
    Dim iP As Integer
    Application.DisplayAlerts = False
    For iP = 1 To ActiveSheet.PivotTables.Count
    ActiveSheet.PivotTables(iP).RefreshTable
    Next
    Application.DisplayAlerts = True
    End Sub
    Module 8:
    Blank


    When File "abstractor_Prod_Monitor_Pivot.XLS" opens the following macro, in WORKBOOK OPEN opens "Curr0.xls":
    ChDir "S:ShareJmoore"
    Workbooks.Open FileName:= _
    "CDAC_OADATA1ShareJMooreCurr0.xls", UpdateLinks:=3
    Workbooks("abstractor_Prod_Monitor_Pivot.XLS").Act ivate
    End Sub

    End of my application, if I can't solve this problem.






    <img src=/S/weep.gif border=0 alt=weep width=21 height=16>
    Attached Images Attached Images

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Why does a Macro acts on a file that it opens

    Stephen,

    It is a litte tricky trying to spot something in so much code without actually having the workbook. However try changing/deleting the UpdateLinks = 3. I think that in th worksheet_activate event you you should just make a call to UpdateIt. I am not sure about Application.OnSheetActivate, but think it might apply to all sheets.

    I'm not surte what

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ThisWorkbook.RefreshAll
    End Sub

    does in connection with closing CURR0.xls.

    Sorry if none of the above help - just groping in the dark really. If anything else occurs to me I will get back to you.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why does a Macro acts on a file that it opens

    Andrew:
    Once again you have saved me. I got rid of the UpdateLinks = 3 and the
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ThisWorkbook.RefreshAll
    End Sub
    which was redundant, anyway, and things began to get better.
    After deleting all of CURR0 macros, one of its worksheets continued to call the Macro in Module 7 of the WorkBook that opened it (the one that had the CHDIR & Open statement in its OPEN event). I deleted all Macros from CURR0 & it still continued to happen. I finally had to delete the WS and re-insert it with a different name. I thought what you see can not hurt you but I guess Microsoft plays by different rules. <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Why does a Macro acts on a file that it opens

    <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

    Good work Stephen, glad you got it sorted.

    Cheers

    Andrew

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why does a Macro acts on a file that it opens

    Andrew:
    I spoke too soon! This is getting VERY frustrating. I am attaching the 2 files to this Post but I have no idea if they will get through.
    The following screwy things are happening:
    1) when I close Abstractor_Prod_Monitor.XLS, it should close Curr0.xls (which it opened). Well it does but then it proceeds to reopen itself and CURR0 also. The only way I can get them to stay closed is with Cntrl-Alt-Delete.
    2) I opened an entirely unrelated file, while I had these bad boys open, and it started to mess up by somehow calling module 7 from Abstractor_Prod_Monitor.XLS.
    Now, after taking 15 minutes to do the Zip files and start this POST, I tried to close the files and they closed fine. So, it seems like it has something to do with our network. The network Administrator has many times told me that I need to wait a 10 minutes and try again when I had some problem. If this is the problem, then I just need to know so I don't come out with serious egg on my face when I demo this thing (scheduled for tomorrow). I do have already put it off a couple of days. Altough, I do have a super understanding Director, it has been an uphill battle to get recognition for these EXCEL models with competition from our IS department that only knows Power Builder, VB, C+, etc.
    Thanks for taking a look at this.
    Stephen
    sstollma@juno.com
    PS
    Could you send me your e-mail address so I can send you the files from home. They are over the 100K limit imposed by WOPR. I may have it already.
    <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Why does a Macro acts on a file that it opens

    Hi,
    Andrew is correct about Application.OnSheetActivate being global for that session of Excel. Therefore whenever you activate a worksheet in any open workbook after the Application.OnSheetActivate macro has been run, it will call your UpdateIt procedure.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why does a Macro acts on a file that it opens

    Rory:
    Thanks for annotating what Andrew had said. I totally missed the significance of the remark. It clears a lot of tings up. However, I still have a problem being able to get rid of code I had entered in Private Sub Worksheet_Activate() (I right click the WS tab and enter code). I delete the code and it acts like it is still there. Its not as big a problem as the close-reopen thing (which I think I have solved). I thought I had finally elimnated the actions on one worksheet, only to find that, when I opened another WS (where I had not removed the code to "Refresh" tables on that sheet) and then returned to the WS that I thought I had fixed, the behavior returned. Does this sound right?
    Stephen

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Why does a Macro acts on a file that it opens

    Hi Stephen,
    Do any of your other worksheets have the Application.OnSheetActivate code in their Activate events? If so, then as soon as you activate them, you're effectively recreating the application level event, so activating any other worksheet afterwards will call that code, even if there's no code actually in that worksheet. Is that what's happening?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why does a Macro acts on a file that it opens

    Rory:
    OK, I think I got it. When I right click on a WS tab and choose "View code" it opens "Private Sub Worksheet_Activate()". SoI didn't think I had any code in in any of "Application.OnSheetActivate" events. But, it was looking me right in the face. Here is the code I had in several of the Private Sub Worksheet_Activate()" modules (or whatever they are called):
    Private Sub Worksheet_Activate()
    Application.OnSheetActivate = "UpdateIt"
    End Sub

    I changed all of these by using " call Updateit instead of Application.OnSheetActivate = "UpdateIt".
    where Updateit() is in Module7 of the main WKBK:
    Sub Updateit()
    Dim iP As Integer
    Application.DisplayAlerts = False
    For iP = 1 To ActiveSheet.PivotTables.Count
    ActiveSheet.PivotTables(iP).RefreshTable
    Next
    Application.DisplayAlerts = True
    End Sub

    I do not think that any of the following code
    in the "Private Sub Worksheet" Activate event of a number of WS should cause me any problem (what do you think?).
    In the CURR0.xls file I have one WS with the fowwowing code:
    Private Sub Worksheet_Activate()
    Dim iK As Integer
    Application.DisplayAlerts = False
    For iK = 1 To ActiveSheet.PivotTables.Count
    ActiveSheet.PivotTables(iK).RefreshTable
    Next
    Application.DisplayAlerts = True
    End Sub

    I have the following code in several WS of the main file:
    sub Filldown()
    Dim LastRow As Long
    LastRow = Application.CountA(ActiveSheet.Range("B:B")) + 2
    Range("A9:A9").Select
    Selection.AutoFill Destination:=Range("A9:A" & LastRow)
    Range("E9:T9").Select
    Selection.AutoFill Destination:=Range("E9:T" & LastRow)
    Range("B9").Select
    End Sub

    Again, thank you very much for your patience.
    Stephen <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Why does a Macro acts on a file that it opens

    Stephen,
    That code all looks OK to me. As long as you've got rid of all instances of Application.OnSheetActivate I think (hope! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>) your problems should disappear.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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