Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jul 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with Run-Time Error 1004 (Excel 2003)

    Sorry about the original post - our smart defense system did not like the use of a certain word in my original message so it delayed the sending of the message. Here is the message in full:

    We are currently using Excel 2003 and IE 7.0.

    We have a daily budget spreadsheet that is posted on our internal "staffweb" intranet.

    Staff access this daily.xls spreadsheet via IE. This spreadsheet contains extensive macros and pivot tables.

    This file is stored on a mapped network drive for our users - something like "F:FINADMINBudgetTheDailyThe_Daily.xls"

    When a person double-clicks on this hyperlink from IE - Excel launches correctly - you see a message about enabling macros and the pivot tables and macros all work just fine.

    When another person say a few minutes latter attempts to launch the same daily.xls they will see a message about enabling macros and an additional message that this file is locked and in use
    by person A and you can open a "read-only" version (this is of course normal). You click the read-only version and the daily.xls launches but then a series of error messages appear. See below:

    "Run-time error 1004"

    Unable to set the _Default property of the PivotItem class

    Here is the debug code:

    Private Sub Worksheet_Change(ByVal Target As Range)

    ' this procedure refreshes the pivot table whenever someone changes a report field

    Select Case Target.Address

    ' if the user changes the division / department, updates the pivottable for that selection

    Case Is = Range("B4").Address
    Application.ScreenUpdating = False
    ActiveSheet.PivotTables("SummaryPT").PivotFields(" Division").CurrentPage = WorksheetFunction.VLookup(Range("SelectionDept"), Sheets("Lists").Range("SelectionDepts2"), 2, False)
    ActiveSheet.PivotTables("SummaryPT").PivotFields(" Department").CurrentPage = WorksheetFunction.VLookup(Range("SelectionDept"), Sheets("Lists").Range("SelectionDepts2"), 3, False)
    Range("B4").Select
    Application.ScreenUpdating = True
    End Select

    End Sub
    Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean)

    ' this procedure launches the popup menu if someone rightclicks on the "data" range

    If Union(Target.Range("A1"), Range("data")).Address = Range("data").Address Then
    CommandBars("MyShortcut").ShowPopup
    Cancel = True
    End If

    End Sub

    So in summary, when people launch this daily xls spreadsheet and another person has it open and they must view it in "read-only" mode the error messages shown above occur.

    Is there something we can do to fix this? This problem only occurs for the second or third or fourth person etc who attempts to view the locked file.

    Thanks again.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Run-Time Error 1004 (Excel 2003)

    Two questions:

    1. What line is highlighted when the error happens
    2. What triggers the change event to run?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Lounger
    Join Date
    Jul 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Run-Time Error 1004 (Excel 2003)

    In answer to question 1:

    This is the line that it stopped at in the VBA editor:

    ActiveSheet.PivotTables("SummaryPT").PivotFields(" Division").CurrentPage =
    WorksheetFunction.VLookup(Range("SelectionDept"), Sheets("Lists").Range("SelectionDepts2"),
    2, False)

    I did not write the macro so I cannot comment on your second question - I will find out from the author.

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with Run-Time Error 1004 (Excel 2003)

    It would be helpful if you could post a copy of the workbook (with proprietary or sensitive information altered/removed).

  5. #5
    Lounger
    Join Date
    Jul 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Run-Time Error 1004 (Excel 2003)

    I am trying to work on this right now to see if we can get a "sanitized" version of the spreadsheet for you and others to look at.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Run-Time Error 1004 (Excel 2003)

    Once you hit debug on the error, choose View, Call stack from the menu. Note the list. These are the steps VBA took to get where you are now.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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