Refresh and update of Pivot tables on protected sheets on opening wkbk.

I have the following code run on opening the file, but it says, "Cannot edit
pivot table on protected sheet.  That dialog box comes up twice, immediately
after each other.  After clicking OK, the file opens still having done the
full procedure!??
The strange thing is that if I execute the code via VBA editor it works
perfectly without the messages.
I can't see what the problem is. Please help
When I open VBA editor immediately after this, it always opens at another
worksheet module ("Sheet9"), that only has this code in it.
Sub RefreshPivot()
Call ThisWorkbook.UpdateAllPivotTables
End Sub

The "ThisWorkbook" code is as follows:
Private Sub Workbook_Open()
Call UpdateAllPivotTables
'Application.ScreenUpdating = False
With Sheets("ALL A-C")
 .EnableAutoFilter = True
..Protect Password:="", UserInterfaceOnly:=True
End With
'Application.ScreenUpdating = True
End Sub

Sub UpdateAllPivotTables()
'Debra Dalgleish's gets rid of unused items in PivotTable
' AND update pivot tabel (based on MSKB Q202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer  'I don't think this dim is required.
'Application.ScreenUpdating = False
With Sheet5
..Unprotect Password:=""
End With
With Sheet6
..Unprotect Password:=""
End With
With Sheet9
..Unprotect Password:=""
End With
'Application.ScreenUpdating = False
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
   For Each pt In ws.PivotTables
     pt.RefreshTable
     For Each pf In pt.PivotFields
       For Each pi In pf.PivotItems
         If pi.RecordCount = 0 And _
           Not pi.IsCalculated Then
           pi.Delete
         End If
       Next
     Next
   Next
Next
With Sheet5
..Protect Password:="", UserInterfaceOnly:=True
End With
With Sheet6
..Protect Password:="", UserInterfaceOnly:=True
End With
With Sheet9
..Protect Password:="", UserInterfaceOnly:=True
End With
Application.ScreenUpdating = True
End Sub

Thankyou for any help,
Rob


0
robnobel (213)
12/3/2003 8:17:27 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
1086 Views

Similar Articles

[PageSpeed] 14

Just a guess and some comments.

comments first:
I think you'd be better off moving the UpdateAllPivotTables and RefreshPivot
into a general module.  (Remember to adjust the call's to them.)

I like to only put events and code for controls from the controltoolbox toolbar
in the worksheet module.

(But I don't think that would cause your problem.)

I've seen some posts by Tom Ogilvy that say that he's had some trouble using the
..unprotect against worksheets without activating the sheet first.  (They might
have even said when they were in (or called by???) the workbook_open event.)

You may want to try:

with sheet5
    .activate
    .Unprotect Password:=""
end with

If that doesn't help, try putting Stop as the first statement of your
workbook_open code.

Save and close your workbook and reopen.  Maybe you'll see something when it's
stepping through it (maybe...).




rob nobel wrote:
> 
> I have the following code run on opening the file, but it says, "Cannot edit
> pivot table on protected sheet.  That dialog box comes up twice, immediately
> after each other.  After clicking OK, the file opens still having done the
> full procedure!??
> The strange thing is that if I execute the code via VBA editor it works
> perfectly without the messages.
> I can't see what the problem is. Please help
> When I open VBA editor immediately after this, it always opens at another
> worksheet module ("Sheet9"), that only has this code in it.
> Sub RefreshPivot()
> Call ThisWorkbook.UpdateAllPivotTables
> End Sub
> 
> The "ThisWorkbook" code is as follows:
> Private Sub Workbook_Open()
> Call UpdateAllPivotTables
> 'Application.ScreenUpdating = False
> With Sheets("ALL A-C")
>  .EnableAutoFilter = True
> .Protect Password:="", UserInterfaceOnly:=True
> End With
> 'Application.ScreenUpdating = True
> End Sub
> 
> Sub UpdateAllPivotTables()
> 'Debra Dalgleish's gets rid of unused items in PivotTable
> ' AND update pivot tabel (based on MSKB Q202232)
> Dim ws As Worksheet
> Dim pt As PivotTable
> Dim pf As PivotField
> Dim pi As PivotItem
> Dim i As Integer  'I don't think this dim is required.
> 'Application.ScreenUpdating = False
> With Sheet5
> .Unprotect Password:=""
> End With
> With Sheet6
> .Unprotect Password:=""
> End With
> With Sheet9
> .Unprotect Password:=""
> End With
> 'Application.ScreenUpdating = False
> On Error Resume Next
> For Each ws In ActiveWorkbook.Worksheets
>    For Each pt In ws.PivotTables
>      pt.RefreshTable
>      For Each pf In pt.PivotFields
>        For Each pi In pf.PivotItems
>          If pi.RecordCount = 0 And _
>            Not pi.IsCalculated Then
>            pi.Delete
>          End If
>        Next
>      Next
>    Next
> Next
> With Sheet5
> .Protect Password:="", UserInterfaceOnly:=True
> End With
> With Sheet6
> .Protect Password:="", UserInterfaceOnly:=True
> End With
> With Sheet9
> .Protect Password:="", UserInterfaceOnly:=True
> End With
> Application.ScreenUpdating = True
> End Sub
> 
> Thankyou for any help,
> Rob

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
12/4/2003 2:13:32 AM
Thanks for that suggestion Dave. I did wonder if there was a way to stop
execution of a macro on opening the wkbk.
I added the ".activate" preceeding both .protect and .unprotect sections of
the code.
I ran the procedure which works faultlessly until the last line is reached
(which is: End Sub) and then the dialog box stating, "Cannot edit pivot
table on protected sheet" shows.  The actions the procedure sets out to do
are actually completed BUT... then it says it can't do it.  On checking the
pivot table, the data has actually been updated even though it says it can't
do it! (The procedure steps past the unprotection and protection without
breaking.) It's a mystery to me!
If I remove Stop and rerun the procedure it works fine without the message
box appearing.  AAAARRGGHHH!!
Just a question re where to place code.  Why in a different module.  Is it
for a good programming reason?
Rob

"Dave Peterson" <ec35720@msn.com> wrote in message
news:3FCE984C.E4BBCA9C@msn.com...
> Just a guess and some comments.
>
> comments first:
> I think you'd be better off moving the UpdateAllPivotTables and
RefreshPivot
> into a general module.  (Remember to adjust the call's to them.)
>
> I like to only put events and code for controls from the controltoolbox
toolbar
> in the worksheet module.
>
> (But I don't think that would cause your problem.)
>
> I've seen some posts by Tom Ogilvy that say that he's had some trouble
using the
> .unprotect against worksheets without activating the sheet first.  (They
might
> have even said when they were in (or called by???) the workbook_open
event.)
>
> You may want to try:
>
> with sheet5
>     .activate
>     .Unprotect Password:=""
> end with
>
> If that doesn't help, try putting Stop as the first statement of your
> workbook_open code.
>
> Save and close your workbook and reopen.  Maybe you'll see something when
it's
> stepping through it (maybe...).
>
>
>
>
> rob nobel wrote:
> >
> > I have the following code run on opening the file, but it says, "Cannot
edit
> > pivot table on protected sheet.  That dialog box comes up twice,
immediately
> > after each other.  After clicking OK, the file opens still having done
the
> > full procedure!??
> > The strange thing is that if I execute the code via VBA editor it works
> > perfectly without the messages.
> > I can't see what the problem is. Please help
> > When I open VBA editor immediately after this, it always opens at
another
> > worksheet module ("Sheet9"), that only has this code in it.
> > Sub RefreshPivot()
> > Call ThisWorkbook.UpdateAllPivotTables
> > End Sub
> >
> > The "ThisWorkbook" code is as follows:
> > Private Sub Workbook_Open()
> > Call UpdateAllPivotTables
> > 'Application.ScreenUpdating = False
> > With Sheets("ALL A-C")
> >  .EnableAutoFilter = True
> > .Protect Password:="", UserInterfaceOnly:=True
> > End With
> > 'Application.ScreenUpdating = True
> > End Sub
> >
> > Sub UpdateAllPivotTables()
> > 'Debra Dalgleish's gets rid of unused items in PivotTable
> > ' AND update pivot tabel (based on MSKB Q202232)
> > Dim ws As Worksheet
> > Dim pt As PivotTable
> > Dim pf As PivotField
> > Dim pi As PivotItem
> > Dim i As Integer  'I don't think this dim is required.
> > 'Application.ScreenUpdating = False
> > With Sheet5
> > .Unprotect Password:=""
> > End With
> > With Sheet6
> > .Unprotect Password:=""
> > End With
> > With Sheet9
> > .Unprotect Password:=""
> > End With
> > 'Application.ScreenUpdating = False
> > On Error Resume Next
> > For Each ws In ActiveWorkbook.Worksheets
> >    For Each pt In ws.PivotTables
> >      pt.RefreshTable
> >      For Each pf In pt.PivotFields
> >        For Each pi In pf.PivotItems
> >          If pi.RecordCount = 0 And _
> >            Not pi.IsCalculated Then
> >            pi.Delete
> >          End If
> >        Next
> >      Next
> >    Next
> > Next
> > With Sheet5
> > .Protect Password:="", UserInterfaceOnly:=True
> > End With
> > With Sheet6
> > .Protect Password:="", UserInterfaceOnly:=True
> > End With
> > With Sheet9
> > .Protect Password:="", UserInterfaceOnly:=True
> > End With
> > Application.ScreenUpdating = True
> > End Sub
> >
> > Thankyou for any help,
> > Rob
>
> --
>
> Dave Peterson
> ec35720@msn.com


0
robnobel (213)
12/5/2003 12:33:35 AM
General modules for general routines.

Worksheet modules for events and controls.

Seems like nice organization and it makes calls to the general routines easier.

rob nobel wrote:
> 
> Thanks for that suggestion Dave. I did wonder if there was a way to stop
> execution of a macro on opening the wkbk.
> I added the ".activate" preceeding both .protect and .unprotect sections of
> the code.
> I ran the procedure which works faultlessly until the last line is reached
> (which is: End Sub) and then the dialog box stating, "Cannot edit pivot
> table on protected sheet" shows.  The actions the procedure sets out to do
> are actually completed BUT... then it says it can't do it.  On checking the
> pivot table, the data has actually been updated even though it says it can't
> do it! (The procedure steps past the unprotection and protection without
> breaking.) It's a mystery to me!
> If I remove Stop and rerun the procedure it works fine without the message
> box appearing.  AAAARRGGHHH!!
> Just a question re where to place code.  Why in a different module.  Is it
> for a good programming reason?
> Rob
> 
> "Dave Peterson" <ec35720@msn.com> wrote in message
> news:3FCE984C.E4BBCA9C@msn.com...
> > Just a guess and some comments.
> >
> > comments first:
> > I think you'd be better off moving the UpdateAllPivotTables and
> RefreshPivot
> > into a general module.  (Remember to adjust the call's to them.)
> >
> > I like to only put events and code for controls from the controltoolbox
> toolbar
> > in the worksheet module.
> >
> > (But I don't think that would cause your problem.)
> >
> > I've seen some posts by Tom Ogilvy that say that he's had some trouble
> using the
> > .unprotect against worksheets without activating the sheet first.  (They
> might
> > have even said when they were in (or called by???) the workbook_open
> event.)
> >
> > You may want to try:
> >
> > with sheet5
> >     .activate
> >     .Unprotect Password:=""
> > end with
> >
> > If that doesn't help, try putting Stop as the first statement of your
> > workbook_open code.
> >
> > Save and close your workbook and reopen.  Maybe you'll see something when
> it's
> > stepping through it (maybe...).
> >
> >
> >
> >
> > rob nobel wrote:
> > >
> > > I have the following code run on opening the file, but it says, "Cannot
> edit
> > > pivot table on protected sheet.  That dialog box comes up twice,
> immediately
> > > after each other.  After clicking OK, the file opens still having done
> the
> > > full procedure!??
> > > The strange thing is that if I execute the code via VBA editor it works
> > > perfectly without the messages.
> > > I can't see what the problem is. Please help
> > > When I open VBA editor immediately after this, it always opens at
> another
> > > worksheet module ("Sheet9"), that only has this code in it.
> > > Sub RefreshPivot()
> > > Call ThisWorkbook.UpdateAllPivotTables
> > > End Sub
> > >
> > > The "ThisWorkbook" code is as follows:
> > > Private Sub Workbook_Open()
> > > Call UpdateAllPivotTables
> > > 'Application.ScreenUpdating = False
> > > With Sheets("ALL A-C")
> > >  .EnableAutoFilter = True
> > > .Protect Password:="", UserInterfaceOnly:=True
> > > End With
> > > 'Application.ScreenUpdating = True
> > > End Sub
> > >
> > > Sub UpdateAllPivotTables()
> > > 'Debra Dalgleish's gets rid of unused items in PivotTable
> > > ' AND update pivot tabel (based on MSKB Q202232)
> > > Dim ws As Worksheet
> > > Dim pt As PivotTable
> > > Dim pf As PivotField
> > > Dim pi As PivotItem
> > > Dim i As Integer  'I don't think this dim is required.
> > > 'Application.ScreenUpdating = False
> > > With Sheet5
> > > .Unprotect Password:=""
> > > End With
> > > With Sheet6
> > > .Unprotect Password:=""
> > > End With
> > > With Sheet9
> > > .Unprotect Password:=""
> > > End With
> > > 'Application.ScreenUpdating = False
> > > On Error Resume Next
> > > For Each ws In ActiveWorkbook.Worksheets
> > >    For Each pt In ws.PivotTables
> > >      pt.RefreshTable
> > >      For Each pf In pt.PivotFields
> > >        For Each pi In pf.PivotItems
> > >          If pi.RecordCount = 0 And _
> > >            Not pi.IsCalculated Then
> > >            pi.Delete
> > >          End If
> > >        Next
> > >      Next
> > >    Next
> > > Next
> > > With Sheet5
> > > .Protect Password:="", UserInterfaceOnly:=True
> > > End With
> > > With Sheet6
> > > .Protect Password:="", UserInterfaceOnly:=True
> > > End With
> > > With Sheet9
> > > .Protect Password:="", UserInterfaceOnly:=True
> > > End With
> > > Application.ScreenUpdating = True
> > > End Sub
> > >
> > > Thankyou for any help,
> > > Rob
> >
> > --
> >
> > Dave Peterson
> > ec35720@msn.com

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
12/5/2003 1:13:08 AM
Thanks

"Dave Peterson" <ec35720@msn.com> wrote in message
news:3FCFDBA4.FB8419FE@msn.com...
> General modules for general routines.
>
> Worksheet modules for events and controls.
>
> Seems like nice organization and it makes calls to the general routines
easier.
>
> rob nobel wrote:
> >
> > Thanks for that suggestion Dave. I did wonder if there was a way to stop
> > execution of a macro on opening the wkbk.
> > I added the ".activate" preceeding both .protect and .unprotect sections
of
> > the code.
> > I ran the procedure which works faultlessly until the last line is
reached
> > (which is: End Sub) and then the dialog box stating, "Cannot edit pivot
> > table on protected sheet" shows.  The actions the procedure sets out to
do
> > are actually completed BUT... then it says it can't do it.  On checking
the
> > pivot table, the data has actually been updated even though it says it
can't
> > do it! (The procedure steps past the unprotection and protection without
> > breaking.) It's a mystery to me!
> > If I remove Stop and rerun the procedure it works fine without the
message
> > box appearing.  AAAARRGGHHH!!
> > Just a question re where to place code.  Why in a different module.  Is
it
> > for a good programming reason?
> > Rob
> >
> > "Dave Peterson" <ec35720@msn.com> wrote in message
> > news:3FCE984C.E4BBCA9C@msn.com...
> > > Just a guess and some comments.
> > >
> > > comments first:
> > > I think you'd be better off moving the UpdateAllPivotTables and
> > RefreshPivot
> > > into a general module.  (Remember to adjust the call's to them.)
> > >
> > > I like to only put events and code for controls from the
controltoolbox
> > toolbar
> > > in the worksheet module.
> > >
> > > (But I don't think that would cause your problem.)
> > >
> > > I've seen some posts by Tom Ogilvy that say that he's had some trouble
> > using the
> > > .unprotect against worksheets without activating the sheet first.
(They
> > might
> > > have even said when they were in (or called by???) the workbook_open
> > event.)
> > >
> > > You may want to try:
> > >
> > > with sheet5
> > >     .activate
> > >     .Unprotect Password:=""
> > > end with
> > >
> > > If that doesn't help, try putting Stop as the first statement of your
> > > workbook_open code.
> > >
> > > Save and close your workbook and reopen.  Maybe you'll see something
when
> > it's
> > > stepping through it (maybe...).
> > >
> > >
> > >
> > >
> > > rob nobel wrote:
> > > >
> > > > I have the following code run on opening the file, but it says,
"Cannot
> > edit
> > > > pivot table on protected sheet.  That dialog box comes up twice,
> > immediately
> > > > after each other.  After clicking OK, the file opens still having
done
> > the
> > > > full procedure!??
> > > > The strange thing is that if I execute the code via VBA editor it
works
> > > > perfectly without the messages.
> > > > I can't see what the problem is. Please help
> > > > When I open VBA editor immediately after this, it always opens at
> > another
> > > > worksheet module ("Sheet9"), that only has this code in it.
> > > > Sub RefreshPivot()
> > > > Call ThisWorkbook.UpdateAllPivotTables
> > > > End Sub
> > > >
> > > > The "ThisWorkbook" code is as follows:
> > > > Private Sub Workbook_Open()
> > > > Call UpdateAllPivotTables
> > > > 'Application.ScreenUpdating = False
> > > > With Sheets("ALL A-C")
> > > >  .EnableAutoFilter = True
> > > > .Protect Password:="", UserInterfaceOnly:=True
> > > > End With
> > > > 'Application.ScreenUpdating = True
> > > > End Sub
> > > >
> > > > Sub UpdateAllPivotTables()
> > > > 'Debra Dalgleish's gets rid of unused items in PivotTable
> > > > ' AND update pivot tabel (based on MSKB Q202232)
> > > > Dim ws As Worksheet
> > > > Dim pt As PivotTable
> > > > Dim pf As PivotField
> > > > Dim pi As PivotItem
> > > > Dim i As Integer  'I don't think this dim is required.
> > > > 'Application.ScreenUpdating = False
> > > > With Sheet5
> > > > .Unprotect Password:=""
> > > > End With
> > > > With Sheet6
> > > > .Unprotect Password:=""
> > > > End With
> > > > With Sheet9
> > > > .Unprotect Password:=""
> > > > End With
> > > > 'Application.ScreenUpdating = False
> > > > On Error Resume Next
> > > > For Each ws In ActiveWorkbook.Worksheets
> > > >    For Each pt In ws.PivotTables
> > > >      pt.RefreshTable
> > > >      For Each pf In pt.PivotFields
> > > >        For Each pi In pf.PivotItems
> > > >          If pi.RecordCount = 0 And _
> > > >            Not pi.IsCalculated Then
> > > >            pi.Delete
> > > >          End If
> > > >        Next
> > > >      Next
> > > >    Next
> > > > Next
> > > > With Sheet5
> > > > .Protect Password:="", UserInterfaceOnly:=True
> > > > End With
> > > > With Sheet6
> > > > .Protect Password:="", UserInterfaceOnly:=True
> > > > End With
> > > > With Sheet9
> > > > .Protect Password:="", UserInterfaceOnly:=True
> > > > End With
> > > > Application.ScreenUpdating = True
> > > > End Sub
> > > >
> > > > Thankyou for any help,
> > > > Rob
> > >
> > > --
> > >
> > > Dave Peterson
> > > ec35720@msn.com
>
> --
>
> Dave Peterson
> ec35720@msn.com


0
robnobel (213)
12/5/2003 10:50:10 PM
Reply:

Similar Artilces:

Pivot Chart Drilldown
I am working on a chart drilldown - I use drilldown to mean that a Shift+Click opens the underlying data into a separate tab - and although the event module I've coded works for 1 row by 1 column pivot charts with any number of pages, I am having issues with drilling down into more complex charts. Currently, I am using the series collection, the MouseUp chart event, as well as features of the active chart, but I am struggling with a more elegant way to execute complex drilldowns. Any ideas? James Igoe 646.303.2584 || james.i...@gmail.com || http://code.comparative-advantage.com I...

Pivot table question #6
Hi, I'm using an excel spreadsheet and I have a list of customer numbers in column A and a list of account numbers in column B. Many customers have more than 1 account number. What I would like to do is change the layout so that I have each customer number listed only once and have all of the account numbers corresponding to the customer number in the same row (so for example I would have customer number in A:1, account number 1 in B:1, account number 2 in B:2, account number 3 in B:3 ect) . Is there a way to do this? Is it possible to do this in a pvt table? Thanks. Hi You could...

Files tool long to open
I have an excel file (about 8MB) and it takes a long time to open and close (even without saving). I know that it has a lot of sheets, macros and formulae but here is the funny thing. The predecessor to this file (the new file is an offshoot of this one) is much larger in size (13MB) and includes more formulae and sheet and it opens and closes much faster than the smaller file. What exactly determines how long a file takes? How can I make this new file (8MB) open and close much faster, especially when I am not even saving it? Thanks, Jay Cleaning the windows temp folder sometimes w...

Copy a sheet contained formulas from one workbook to another and remove file links
Hello, in our company we use a special excel file with more sheets. Every user copies first this file from the server to his own computer and then he/she can customize the workbook. Usually the user only makes his own new sheets where there are formulas which contain cells from the original sheets. Occasionally a new release of the original excel file appears and the user must make a new copy of this file. Commonly the user wants to have in the new file all of his own created sheets from the first workbook. If he normally copies the sheets from the first workbook to the new one, the f...

How to run Visual Basic from Excel sheet #5
How do I run a Visual Basic routine from a normal Excel cell. I would like to do something like this: =IF(RunIt=TRUE, Run my Visual Basic routine, Do nothing) So if some calculations results in the variable "RunIt" is set equal to TRUE, the Visual Basic routine must run. Kristian ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ ...

directory update exchange 5.5
I just removed a secondary smtp address from a user however after several hours the user still recieves emails from the removed address. Did i miss something tks angelo ...

Form to populate table issue
I am using a form to update invoice entries. The form pulls info (the funding source) from a different table, based on a parameter query. When I enter the parameter info, the query populates the funding fields automatically from the funding table. This works great. My problem is I then enter invoice info (amounts) in the form for each funding source. When I check the invoice table, the amounts are there, but the funding source info is not. Somehow the table is only taking the info I am typing in on the form, but not the info that is being supplied through the parameter query for...

Word Freezes on opening
Machine is daughters Gateway laptop, when problem 1st occurred was running Vista Home Premium. I installed Office 03 when new 2 years ago, all programs ran fine. About 6 months ago daughter said Word stalled on opening, all other Office progs still work. Removed, re-installed Office 03, no luck. Deleted 03 and installed Office 2000, problems remains. I get a general box with the outline of a paper, but no menus or commands. Program stalls at that point. Am now running WIN 7 Ultimate, no change when clicking on Word. Thanks, Bill W Try to delete any files called "norma...

Excel 2003 - VBA
Hi: I need to clear 31 sheets of data (daily downloads) and cycling through them is timely and just not very elegent. (Look at me, trying to be elegent when I can barely use VBA!) What I did was turned on macro recording, selected several sheets then depressed "Delete". After responding "Yes" to three popups asking if I wanted to clear the QueryTables, it seemed to work. When I went back to look at the generated Macro, it got hung up (Application-definded or object-defined error) on the line deleteing the querytable, and this seemed to happen before I even re-executed the...

Returning value from related table
Access 03 Have four entities. Customer, Order, Order Details and Product. In an order form (based on Order as parent and OrderDetails as SubForm) I want to return the price of an item stored in the products table. I have tried to build a query and use the value from the ProductID field as the criteria and place the looked up PartPrice in the control source of the ProductPrice field but get a #name error. This works fine on a standalone form, don't understand why it won't work on a subform. Any help appreciated. Thanks Mick Do not use criteria as you would need to refresh everyti...

Group Sheets
Is there a way to group several sheets in order that if I add or delete a row in one .... that row is deleted in each of the sheets in the group? Thanks Non-contiguous sheets: Hold down <Ctrl>, and click in each tab that you wish to group. Contiguous: Click in the first tab, hold down <Shift>, click in the last tab. Each grouped sheet will display "[Group]" appended to the sheet name in the title bar. Don;t forget to "UnGroup" when you're done, or you'll end up with a big mess if you continue to make revisions to what you later think is a single s...

Problem viewing Excel 2003 Pivot Chart fields in Excel 2007
When I’m running Excel 2007 and I’m viewing a chart in a file created in Excel 2003 using the ‘Pivot Chart and Pivot Chart Report’ functionality, I’m unable to see the Pivot Chart (drop down) fields anywhere on the Pivot Chart. This happens even when the file created in Excel 2003 is ‘converted’ to 2007 when it is opened. -- Ronny B Hi, When the pivot chart is selected try choose PivotChart Tools, Analyze, and turn on the Pivot Chart Filter. -- Thanks, Shane Devenshire "ronny B" wrote: > When I’m running Excel 2007 and I’m viewing a chart in a file created in > ...

Calculating Month to Date on a Summary Sheet
Good Morning. I have a summary sheet for an entire month of data, and 31 seperate tabbed sheets for each day of the month. Is there a quick way to set up the month to date summary 'totals' in each cell? Currently, I am holding control and going into each cell on 31 sheets after =sum I'm sure there is a quick way to calculate, and I hope so, as I have about 15 cells that I would like to calculate on the summary page Any help would be greatly appreciated!! Thank you Bradle If the cells are always the same, type =sum( select the first sheet, hold down shift and click the last she...

Why update to SP3?
I'm sitting here eating a nice bagel and reading a Microsoft white paper which what's contained in SP3: http://www.microsoft.com/downloads/details.aspx?FamilyId=68C48DAD-BC34-40BE-8D85-6BB4F56F5110&displaylang=en#QuickInfoContainer I've just gotten some bugs OUT of my desktop machine as of a day or two ago. It's running beautifully. I don't see anything in the white paper which applies to my computing situation. Desktop machine, not wireless, never will be. As close as I get to "networked" (other than the internetZ) is using some VPN software o...

Can I set the default to Open on a web page link to ICS file?
I use links like this on my web site to give users an option to automatically add an event to their Outlook calendars. This requires them to click "Open" for it to work. Right now, I have text on the web page that tells the user to select Open not Save, etc. Is it possible to have the event added to the users' calendars without them clicking on a pop-up window? <a href="/MyEvent.ics">Add Event to Outlook Calendar</a> Thanks in advance to all the people so much smarter than I am. >-----Original Message----- >I use links like this on my web site t...

Table Import no longer shows tables
In GP 10, if you go to any screen and then try to go to Table Import, it just goes to Table Import and it no longers shows the tables that are being access by the form. Is there anyway to get this feature back? Hi Rich Table Import does still work as before. The difference now is the new security model is pessimistic. Unless the user you have been using has been granted access to the tables, or is a POWERUSER, you will not see the tables. Hope this helps. David Musgrave [MSFT] Escalation Engineer - Microsoft Dynamics GP Microsoft Dynamics Support - Asia Pacific Microsoft Dynamics (f...

pivot table ????
hi there how to create a pivot table? or please give me the links so i can gone through mysel thank you One good source: http://peltiertech.com/Excel/Pivots/pivotstart.htm In article <0C04B245-3B7B-408E-9DF9-8F35DFEA6A72@microsoft.com>, "chng" <anonymous@discussions.microsoft.com> wrote: > hi there; > > how to create a pivot table? or please give me the links so i can gone > through myself > thank you Hi see http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html -- R...

Excel Protection Q
I understand that Excel protection is not that strong for determined people to work around. Are there any 3rd Party software that users have used which encrypts Excel to a relatively satisfactory level? I still wish to use and distribute Exel files but with a stronger protection aspect Thanks "Sean" <seanrya...@yahoo.co.uk> wrote... >I understand that Excel protection is not that strong for determined >people to work around. Are there any 3rd Party software that users >have used which encrypts Excel to a relatively satisfactory level? I >still wish to use and...

when I open excel file,changes was undid-excel 2007, compatibility
I have a file that was made in excel 2007 working in compatibility mode and when I change format in some cell (for example font size), then I save changes and after reopen this fiel the changes was undid. Can some help me? Sorry for my english isn't very well -- thanks Pedro ...

Using Firefox to open links in Outlook
Hello, I set my default browser to be Firefox, I click a link in Outlook it opens fine. When I close Outlook and reopen it and then try to click a link I get an error saying "This operation has been canceled due to restrictions on this computer." The only way I can get it to work again is if I first set Internet Explorer to be my default and then make Firefox my default again. Any ideas how to fix this? ...

Pivot chart values
On my stacked column pivot chart I want to show on the data labels the percentage that make up the column and on my y axis the actual number can I show this? ...

Software Update M05
Everytime I open Money it tells me there is a software update available, click ok to download and install. After downloading it tells me it couldn't perform the update to make sure I'm still contected to the internet and try again. Anyone solve this problem? In microsoft.public.money, Jason wrote: >Everytime I open Money it tells me there is a software >update available, click ok to download and install. >After downloading it tells me it couldn't perform the >update to make sure I'm still contected to the internet >and try again. If you use XP, ...

Table structure for Course Attributes
I have a database which stores information about courses. This Course table structure is very simple: CourseID, Description, Title, etc. I need to store other information about the courses, but this information is more irregular. I was thinking about creating a CourseAttribute table with just a few fields: CourseID, AttributeType, AttributeValue, SortOrder. If I need to store books needed, it would be in Attribute records: CourseID, AttributeType = "Book", AttributeValue= bookID. A course may have several books, or no books. If I need to store lists of Course Obj...

Arrow keys moves the sheet, not the marker!
Hi! This "feature" is driving me nuts! When I use the arrow keys in the excel worksheet, I'm not moving the marker between cells, but instead I'm scrolling the work sheet, e.i. the entire sheet is moving, but the marker remains on the same cell. This goes for all documents I open in Excel. Please help me restore my dear Excel! regards, Andreas On a keyboard there is a function key called "Scroll Lock" with accompaning light on/off. Please make sure that you have it OFF. -- HTH Topola, http://vba.blog.onet.pl Thanks! This was probably the first (and the last...

MS Query error Could not add the table
I get the error "Could not add the table '\\lth01pc354\c$\Logs\History\TEST.CSV)' when running this subquery on an external data source: SELECT BEMIS_BAGGER_RATE AS 'RATE', (SELECT Avg(Val(BEMIS_BAGGER_RATE)) FROM \\lth01pc354\c$\Logs\History\TEST.CSV) AS 'AVERAGE' FROM \\lth01pc354\c$\Logs\History\TEST.CSV What am I doing wrong? KHanna wrote: > I get the error "Could not add the table > '\\lth01pc354\c$\Logs\History\TEST.CSV)' when running this subquery on an > external data source: > > SELECT BEMIS_BAGGER_RATE AS 'RATE', ...