Posts by gweasley

    Re: Conditional Formatting Colorindex Error In Pivot Table

    Reafidy, I did try that as an alternative but it sets the background color, not the font color to red. :(

    Dave, here's the funny part. I did that and the recorded macro won't work on the same thing. ::D The recorded macro yields me about the same kind of code as I currently have. Could it be something to do with the pivot? I tested the code on a "regular" range and it worked just fine!

    I suppose an alternative is to "manually" treat the rows as a regular range and see if that works?

    Below is some code I am trying to run. What I would like to do is select a certain "data row" in the pivot, and apply some conditional formatting to that. It works just fine until I reach the .colorindex = 3 line. It says I'm getting an application/object defined error :crying: Would you guys happen to know how to fix this? I'm on Excel 2007. This was fine on 2000!


    Hey guys,

    Using an example from, I am trying to use the following sub procedure to show ALL pivotitems in a certain Pivot Field, called "Director". This doesn't seem to work for me :( I get an error that says something like "Unable to set Visible Property for the Pivot Item Class" if not an Application defined error. Could I perhaps be missing a library? Any ideas??

    Re: Hide/delete "(all)" As A Pivot Page Field Option


    I don't have the PivotTableUpdate event (I'm guessing that it is available in a later version of Excel - I'm on 2000 : D ). However, I did use your subprocedure under the Worksheet Calculate event and it works PERFECTLY!!! I'll just add in a message box whenever "All" is selected saying it "can't" be selected :)

    Thanks so much for the help!


    Hello folks!

    I was wondering if it is possible at all to hide or delete the default page field option to select "(All)" in a pivot table, and to just have the pivot default to the first list member in that page field.

    Any thoughts would be appreciated!

    Best regards,


    Hey guys,

    I know that there is a way to delete macros in a file using another macro in order to save the file as a macro-free copy.

    I was wondering though if there is a way to comment and uncomment code using a macro, as well. The reason I need to do this is that I have a module that builds Excel files for me using a template, but that template has to have certain lines of code commented first in order for the module to run correctly. After the module is done building the file, I must uncomment those lines manually and was wondering if there is an automatic way to do this.

    Looking forward to your input and thanks a lot!


    Re: Sorting columns by clicking the heading Part 2

    Hey Tony,

    vItemRankingSheet is a global variable I declare where vItemRankingSheet = "Name of Sheet concerned". I managed to work it out by using the double click event of a worksheet. It even toggles between Ascending and Descending! Lemme know if anyone needs similar code, I think it's really effin-cool : D




    I'm trying to sort columns by clicking the heading, as well. I tried using RoyUK's code in response to UKCreation's query. Code doesn't seem to work, however :(

    Any suggestions why? I do have a header row... and the range containing my dataset is A15:I35.

    The error I get is Sort Method of Range Class failed.

    This is how my code looks like:

    This is based off of Roy's original suggestion:

    Thanks for any help you can provide!


    Re: Dynamically Assigning TotalList Values


    Apparently for some reason, the subtotal piece WON'T work if you have a conditional that skips columns if a condition isn't met (which DOES work though if you hardcode it into the method - WEIRD!).

    For now, I'm using an array that will give me a continuous column set, and if you want certain columns skipped, then you can build another piece into the macro (or another macro) that strips those populated columns away.

    You can use something like:

    1. For i = 1 to LastColumn
    2. MyArray(i) = i
    3. Next i
    4. Selection.Subtotal GroupBy:=1, Function:=xlSum, _
    5. totalList:=MyArray, _
    6. Replace:=True, PageBreaks:=False, SummaryBelowData:=True

    This worked for me!

    Hello folks,

    I was wondering if you could help me out. Do you know if it is at all possible to assign a dynamic array to the SubTotal Method's Total List argument?

    My code is as follows but I get an error everytime it reaches the subtotal piece.

    I would appreciate any help you can give me.

    Thanks so much!


    Re: Shape/Picture Events


    This is awesome, thanks! We altered your code a little because we needed it to NOT toggle between watermark and automatic states and we got it!

    1. With ActiveSheet.Shapes(Application.Caller)
    2. .PictureFormat.ColorType = msoPictureWatermark
    3. .PictureFormat.ColorType = msoPictureAutomatic
    4. End With

    (We didn't want the colors to invert so went through all the trouble ;))
    Thanks so much!


    I was wondering if (embedded) shapes or pictures in a worksheet have events activated and if not, is it possible to have events activated for them?

    We tried creating a class module but get an error. What we want to happen is when the picture is clicked (mousedown), it be turned into a watermark so it looks "pressed," but on mouse up, it be restored back to the way the picture looked originally.

    Any help will be greatly appreaciated!



    Re: Animated Chart using a Timer

    I have checked into that and it still doesn't work for some reason. I have also tried wringing the neck of my computer (that part which protrudes from the base of the monitor) to no avail :D Must be an internal excel issue we're having (not the first ;)).

    However, thanks mucho mucho Andy :D

    Re: Animated Chart using a Timer

    Hi Andy,

    Thank you so much for your help on this... For some reason, it's not animating in my computer, though the button's caption changes. Anything I should reset/change/set in excel?

    Thanks again,