J
john.chinstrap
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:\FINADMIN\Budget\TheDaily\The_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.
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:\FINADMIN\Budget\TheDaily\The_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.