Posts by Tybaltcap

  • Camera Tool not displaying PivotChart

    Re: Camera Tool not displaying PivotChart


    The picture is in the same sheet as the Pivot Chart. The picture will show the cells behind the chart, but not the actual chart.


    I have another picture on the same sheet - it updates just fine, but thats probably becuase it is showing the actually cells, not a chart over the cells


    Tim

  • Simple VBA but Not enough resources

    Re: Simple VBA but Not enough resources


    Tybaltcap



    Hi, I have created a dashboard with 4 pivot tables with their filters linked to combo boxes. There are 3 combo boxes that change the department, the date, and the the active task. As it is now, all the code is in a module ( I'm too much of a newbie to know if I should separate it etc.) It doesn't seem like its a lot for the computer to have to handle - it ran it fine when it was three pivots all linked, but now with 4 it crashes. Can anyone see what I'm doing wrong?


    As a side note, when I had the code telling excel to go load the another page (pivot gantt) filter it, then come back to the dashboard it worked just fine. Why is it more resource intensive when everything is on one sheet.


    here is my code, but I'm also attaching the file.



    Thanks!

  • Simple VBA but Not enough resources

    Re: Simple VBA but Not enough resources


    FIRST - I know my code is coming out all in one line - how do I fix that?? Hi, I have created a dashboard with 4 pivot tables with their filters linked to combo boxes. As it is now, all the code is in a module (I'm too much of a newbie to know if I should separate it etc.) It doesn't seem like its a lot for the computer to have to handle - it ran it fine when it was three pivots all linked, but now with 4 it crashes. Can anyone see what I'm doing wrong?

    Code
    Sub DateFilterSelect()  'filters dates in in all pivots  If Range("Date_Filter").Value = "Next 2 weeks" Then   ActiveSheet.PivotTables("ProjectsTable").PivotFields("Date rank").CurrentPage _          = "(All)"       With ActiveSheet.PivotTables("ProjectsTable").PivotFields("Date rank").PivotItems("other").Visible = False      End With       ActiveSheet.PivotTables("ProjectsTable").PivotFields("Date rank"). _  EnableMultiplePageItems = True          ActiveSheet.PivotTables("DateGraph").PivotFields("Date rank").CurrentPage = "(All)"       With ActiveSheet.PivotTables("DateGraph").PivotFields("Date rank").PivotItems("other").Visible = False      End With       ActiveSheet.PivotTables("DateGraph").PivotFields("Date rank").EnableMultiplePageItems = True       With ActiveSheet.PivotTables("workload").PivotFields("Date rank").PivotItems("other").Visible = False      End With       ActiveSheet.PivotTables("workload").PivotFields("Date rank").EnableMultiplePageItems = True   With ActiveSheet.PivotTables("gantt").PivotFields("Date rank").PivotItems("other").Visible = False       End With      ActiveSheet.PivotTables("gantt").PivotFields("Date rank").EnableMultiplePageItems = True          Else  ActiveSheet.PivotTables("DateGraph").PivotFields("Date rank").ClearAllFilters     ActiveSheet.PivotTables("DateGraph").PivotFields("Date rank").CurrentPage = Range("Date_Filter").Value  ActiveSheet.PivotTables("ProjectsTable").PivotFields("Date rank").ClearAllFilters     ActiveSheet.PivotTables("ProjectsTable").PivotFields("Date rank").CurrentPage = Range("Date_Filter").Value      ActiveSheet.PivotTables("workload").PivotFields("Date Rank").ClearAllFilters      ActiveSheet.PivotTables("workload").PivotFields("Date Rank").CurrentPage = Range("Date_Filter").Value  ActiveSheet.PivotTables("gantt").PivotFields("Date Rank").ClearAllFilters  ActiveSheet.PivotTables("gantt").PivotFields("Date Rank").CurrentPage = Range("Date_Filter").Value            End If    End Sub    Sub ActiveTaskFilterSelect()  'filters active projects in all pivots       ActiveSheet.PivotTables("DateGraph").PivotFields("Active Projects").ClearAllFilters     ActiveSheet.PivotTables("DateGraph").PivotFields("Active Projects").CurrentPage = Range("ActiveTask_Filter").Value        ActiveSheet.PivotTables("ProjectsTable").PivotFields("Active Projects").ClearAllFilters     ActiveSheet.PivotTables("ProjectsTable").PivotFields("Active Projects").CurrentPage = Range("ActiveTask_Filter").Value         ActiveSheet.PivotTables("support").PivotFields("Active Projects").ClearAllFilters     ActiveSheet.PivotTables("support").PivotFields("Active Projects").CurrentPage = Range("ActiveTask_Filter").Value    ActiveSheet.PivotTables("gantt").PivotFields("Active Projects").ClearAllFilters  ActiveSheet.PivotTables("gantt").PivotFields("Active Projects").CurrentPage = Range("ActiveTask_Filter").Value    End Sub    Sub DepartmentFilterSelect()  ' departmennt FilterSelect Macro       ActiveSheet.PivotTables("ProjectsTable").PivotFields("Department").ClearAllFilters     ActiveSheet.PivotTables("ProjectsTable").PivotFields("Department").CurrentPage = Range("Department_Filter").Value    ActiveSheet.PivotTables("DateGraph").PivotFields("Department").ClearAllFilters     ActiveSheet.PivotTables("DateGraph").PivotFields("Department").CurrentPage = Range("Department_Filter").Value        ActiveSheet.PivotTables("Support").PivotFields("Department").ClearAllFilters     ActiveSheet.PivotTables("Support").PivotFields("Department").CurrentPage = Range("Department_Filter").Value            ActiveSheet.PivotTables("workload").PivotFields("Department").ClearAllFilters     ActiveSheet.PivotTables("workload").PivotFields("Department").CurrentPage = Range("Department_Filter").Value                ActiveSheet.PivotTables("gantt").PivotFields("Department").ClearAllFilters     ActiveSheet.PivotTables("gantt").PivotFields("Department").CurrentPage = Range("Department_Filter").Value            End Sub

    Thanks!

  • Simple VBA but Not enough resources

    FIRST - I know my code is coming out all in one line - how do I fix that?? Hi, I have created a dashboard with 4 pivot tables with their filters linked to combo boxes. As it is now, all the code is in a module ( I'm too much of a newbie to know if I should separate it etc.) It doesn't seem like its a lot for the computer to have to handle - it ran it fine when it was three pivots all linked, but now with 4 it crashes. Can anyone see what I'm doing wrong?


    Code
    Sub DateFilterSelect() 
'filters dates in in all pivots  If Range("Date_Filter").Value = "Next 2 weeks" Then   ActiveSheet.PivotTables("ProjectsTable").PivotFields("Date rank").CurrentPage _ 
        = "(All)"     With ActiveSheet.PivotTables("ProjectsTable").PivotFields("Date rank")         .PivotItems("other").Visible = False     End With     ActiveSheet.PivotTables("ProjectsTable").PivotFields("Date rank"). _  
EnableMultiplePageItems = True          ActiveSheet.PivotTables("DateGraph").PivotFields("Date rank").CurrentPage _         = "(All)"     With ActiveSheet.PivotTables("DateGraph").PivotFields("Date rank")         .PivotItems("other").Visible = False     End With     ActiveSheet.PivotTables("DateGraph").PivotFields("Date rank"). _         EnableMultiplePageItems = True      With ActiveSheet.PivotTables("workload").PivotFields("Date rank")         .PivotItems("other").Visible = False     End With     ActiveSheet.PivotTables("workload").PivotFields("Date rank"). _         EnableMultiplePageItems = True  With ActiveSheet.PivotTables("gantt").PivotFields("Date rank")         .PivotItems("other").Visible = False     End With     ActiveSheet.PivotTables("gantt").PivotFields("Date rank"). _         EnableMultiplePageItems = True            Else  ActiveSheet.PivotTables("DateGraph").PivotFields("Date rank").ClearAllFilters     ActiveSheet.PivotTables("DateGraph").PivotFields("Date rank").CurrentPage = Range("Date_Filter").Value  ActiveSheet.PivotTables("ProjectsTable").PivotFields("Date rank"). _         ClearAllFilters     ActiveSheet.PivotTables("ProjectsTable").PivotFields("Date rank").CurrentPage _         = Range("Date_Filter").Value      ActiveSheet.PivotTables("workload").PivotFields("Date Rank"). _         ClearAllFilters     ActiveSheet.PivotTables("workload").PivotFields("Date Rank"). _         CurrentPage = Range("Date_Filter").Value  ActiveSheet.PivotTables("gantt").PivotFields("Date Rank").ClearAllFilters ActiveSheet.PivotTables("gantt").PivotFields("Date Rank").CurrentPage = Range("Date_Filter").Value          End If  End Sub  Sub ActiveTaskFilterSelect() 'filters active projects in all pivots     ActiveSheet.PivotTables("DateGraph").PivotFields("Active Projects"). _         ClearAllFilters     ActiveSheet.PivotTables("DateGraph").PivotFields("Active Projects"). _         CurrentPage = Range("ActiveTask_Filter").Value      ActiveSheet.PivotTables("ProjectsTable").PivotFields("Active Projects"). _         ClearAllFilters     ActiveSheet.PivotTables("ProjectsTable").PivotFields("Active Projects"). _         CurrentPage = Range("ActiveTask_Filter").Value       ActiveSheet.PivotTables("support").PivotFields("Active Projects"). _         ClearAllFilters     ActiveSheet.PivotTables("support").PivotFields("Active Projects"). _         CurrentPage = Range("ActiveTask_Filter").Value  ActiveSheet.PivotTables("gantt").PivotFields("Active Projects").ClearAllFilters ActiveSheet.PivotTables("gantt").PivotFields("Active Projects").CurrentPage = Range("ActiveTask_Filter").Value  End Sub  Sub DepartmentFilterSelect()  ' departmennt FilterSelect Macro      ActiveSheet.PivotTables("ProjectsTable").PivotFields("Department"). _         ClearAllFilters     ActiveSheet.PivotTables("ProjectsTable").PivotFields("Department"). _         CurrentPage = Range("Department_Filter").Value          ActiveSheet.PivotTables("DateGraph").PivotFields("Department").ClearAllFilters     ActiveSheet.PivotTables("DateGraph").PivotFields("Department"). _         CurrentPage = Range("Department_Filter").Value          ActiveSheet.PivotTables("Support").PivotFields("Department"). _         ClearAllFilters     ActiveSheet.PivotTables("Support").PivotFields("Department").CurrentPage = Range("Department_Filter").Value          ActiveSheet.PivotTables("workload").PivotFields("Department").ClearAllFilters     ActiveSheet.PivotTables("workload").PivotFields("Department").CurrentPage = Range("Department_Filter").Value              ActiveSheet.PivotTables("gantt").PivotFields("Department").ClearAllFilters     ActiveSheet.PivotTables("gantt").PivotFields("Department").CurrentPage = Range("Department_Filter").Value          


End Sub

    Thanks!

  • Camera Tool not displaying PivotChart

    Re: Camera Tool not displaying PivotChart


    yes, my calculations are set to Automatic...should this be something different? This is really driving me wild - it seem to stop showing the chart, and shows the cells behind the chart every time I hide a row as well...

  • Camera Tool not displaying PivotChart

    Hi, I have a pivot chart which has a filter controlled with a combo box (not sure this matters) On this same Dashboard, I have used the camera tool to take a picture of of the range of cells encompassed by the chart. When I change the options on my combo box, the Pivotchart changes, but my camera's snapshot shows only the blank cells under the chart. Oddly if I select the chart, then select the picture and move it, it seems to refresh. I have coded a macro to do just that...but it does not work unless I manually do it. Have I angered some ghost of excel? I really can't figure this out! Thank you, Tim

  • Using checkboxes to add/remove values to a table

    Re: Using checkboxes to add/remove values to a table


    Also - if I want to add a new table to the same userform (if the first one was say 'class' the second would be 'block') would I need to basically copy everything in Main and all the cbWind code a second time? Then tie them all (the same way I did with the Class) to the new table?

  • Using checkboxes to add/remove values to a table

    Re: Using checkboxes to add/remove values to a table


    Awesome, Awesome, AWESOME!!! I can't believe I got it!!! (totally with your help!) I'm really in your debt.


    Quick question though - I have a delete button - which basically erases the tbName from the Name list - what code do I need to add to that button to make sure that it erases the name in the Class table under the classes that to which its attached? IE uncheck all the checkboxes that might be checked under that name. running the EnableEvents and then having all the cbboxes = false doesn't seem to work...

  • Using checkboxes to add/remove values to a table

    Re: Using checkboxes to add/remove values to a table


    Fantastic! So close I can taste it! You really put a lot of work into this, and I can't thank you enough. If you could help me just a bit more to get to the finish line I'd be much appreciated.


    Is there any way to make it so that it doesn't actually delete the cell when you uncheck the box? I will have other tables which get referenced placed below this table.


    I may try to adapt this to use a listbox for another userform that has many more options (12 'classes') what that be a big big step? IE beyond what a novice might be able to do do.


    And finally - as I'm adapting into the code of another userform (so this is part of a larger form) is there any maybe less than obvious things I need to make sure to change? I can change the table names and the cb/tb box names of course, but what else do I need to pay attention to?


    Its been fun going through looking at this code - I can't wait to get better at this!


    forum.ozgrid.com/index.php?attachment/38607/

  • Using checkboxes to add/remove values to a table

    Re: Using checkboxes to add/remove values to a table


    Gijsmo! you are AWESOME! That is spot on what I'm looking for! I'm so so happy for your assistance!


    I'm having a hard time though going through it to adapt it to my model. I don't see where you have selected the ranges that you are looking in...or how the table is defined. For my model I have a named range that is the table. Can that be used?


    Thanks!
    Tim

  • Using checkboxes to add/remove values to a table

    Hi! Novice here hoping to get some help with making my userform.


    I have a table with "Class1" to "Class 4" as headings and then some values under each one


    I want to make a user-form that has a text-box and 4 check boxes "Class1"..."Class 4"


    When the userform opens if the value in the text-box is in the table under any of the class column headings the corresponding check box is checked, if it is not, the check box for that class is empty.


    the last part (probably the most difficult) is that When you click one of the check boxes it will add that text to the first empty row under that column heading. AND if you un check the textbox it will delete that value from the list and then shift the rest of the column's values up to keep from having an empty cell.


    I'm really struggling with the coding, and a complete beginning so I would REALLY REALLY appreciate any help! Thanks!

  • AutoFill Down Rows Based On Another Columns Last Cell

    Re: AutoFill Down Rows Based On Another Columns Last Cell


    Thanks for that - but it dosnt quite seem to do what I want, but it definatly got me a lot closer! let me try to be a bit more specific.


    On sheet "raw data" I will be pasting the additional rows (each month as new data comes in).


    On sheet "Level 1 Calculations" I have formulas in each of the colums starting from row A6 (through AC6). Many of these formulas are just to copy data in from raw data, but others are more complex.


    I want to copy down these formulas for the number of rows that will exist (including the additions) on Raw data. Raw data sheet also starts its data at A6 but only goes to S6.


    Example - before I add anything both Raw Data and Level 1 Calculations have rows A6-A2,000 filled with data. I add in the monthly data into Raw Data giving it data in rows A6-A2065. I want to copy down the formulas in each of the columns far enough to include the new data (from A6 to A2065).


    Thanks!!! I hope soon Ill be on the other side of the asking/answering side of this forum.

  • Copy Variable Range To Next Blank Row

    Re: Copy Variable Range To Next Blank Row


    Awesome!


    Those both work well - but question - if I want it to cut and paste as opposed to copy what do I need to change?


    Also - just to help me better understand if you dont mind...


    1. what does vbnullstring mean/do?
    2. what does x1Down do?


    Thanks a ton!

  • AutoFill Down Rows Based On Another Columns Last Cell

    Hello Excel Gurus!


    I have absolutly no idea how to do this, but have over the years found that pretty much anything is possible in excel, so lets see.


    I have a workbook which has one sheet "Raw Data" where I will cut and past blocks of data of various numbers of rows.


    Another sheet "level one calcs" conducts various calculations on the raw data.


    I was wondering if there would be any way to create a macro which would copy down (autofill) the last row of my my calculation page for the exact number of rows I added into the raw data.


    The goal here would be so that I dont have to highlight and copy down the formulas on the calc sheet each time I add in new data.


    Thanks!

  • Copy Variable Range To Next Blank Row

    Hello,


    I did some research on the site and found help for people with similar problems, but sadly wasnt quite smart enough to apply those answers to my problem.


    I want to be able to add new accounts in the New Account Input sheet (consistent of names and number) and then push a button to paste them in the next free row on Account master.


    I do know how to assign a macro to a button - its just the actual vb coding Im struggling with.


    Thanks,
    Tim

  • Return Value To Cell Based On CheckBox TRUE/FALSE

    Re: Return Value To Cell Based On CheckBox TRUE/FALSE


    Quote from mikerickson

    Glad to have helped.
    Array formulas are nifty, but they slow things.


    Hey - actually I noticed a small glitch. if you do not select the first option it will return one value less than number of items you click. The reason I guess is that it will be looking for the second lowest row number. I think I can figure out a fix, but if you happen to return to this and know the answer let me know.


    Thanks!