Posts by lackeye

    Re: Clear Forms Control ComboBox

    Thanks for the quick suggestion. Unfortunately I am not using a linked cell. Is it possible to call the control and clear it directly from a standard procedure?

    I have a combobox from the Forms Toolbox inserted in a worksheet and have a macro assigned to run when the user selects a value. After the value is selected in the combobox I'd like the combobox to clear itself (show a blank). Currently the selected value stays highlighted in the combobox after the selection is made. I know how to do this with a Controls combobox, but cannot figure out how to use it with a Forms combobox. (The reason I am not using the controls cb is because there seems to be an Excel2007 bug that causes the properties of the displayed text to randomly change when the control is activated. When a selection is made in the controls cb, the text displayed in the cb turns bold, and either grows or shrinks in size to the point that it is unreadable. Same thing seems to happen with all other ActiveX controls...)
    Sample attached. Any suggestions are greatly appreciated.

    Re: Apply Pivot Item filter to Grouped Data

    Yes, this works! Thank you. Please IM me your PayPal email address. One last question that's worth $25 to me if you (or anyone) ha san answer to... When I use Active X Controls (option buttons, check boxes, etc.) they tend to either randomly grow or shrink when activated. See attached, second check box. Any clue why this is happening and how to fix it?

    I am using Excel 2007 and have generated code to automatically build a pivot table. I’m using the pivot table ‘Group’ feature to allow the user to filter the data by Year, Quarters, Months, and Days (or any combination of these based on option button selections.) The user can also enter a Start Date and End Date in cells to specify the window of time to summarize in the pivot. The code is working fine, but the Group functionality has an unintended annoyance when the data is filtered by the Start and End date entered.

    When the pivot is generated it adds a summary column for all data prior to the Start Date and a column for all the data past the End Date. For example, if my Start Date is 08/04/2009 and my End Date is 12/01/2010, the Group feature adds a column to the pivot with the heading ‘<08/04/2009’ and ‘>12/01/2010’. This is annoying because no rows of data are filtered so the table is always X rows in length, regardless of the occurrence of actual data within the stated range.

    I have been trying to figure out a way to apply a filter to this field to not show anything that starts with < or >. So far all attempts have been unsuccessful and I find myself in need of a solution as soon as possible.

    I am willing to pay $50 for a solution payable by PayPal (I pay fast too). Sample file attached. (File is a 2007 file saved as 2003 so that I could upload it here. I can email the 2007 version if needed.) I stripped out a lot of code that wasn’t needed in the attached so if anything is confusing, please IM me.
    Thanks in advance for taking a look.

    I have a workbook used to track project budgets. There is a separate worksheet with a data ledger for each project and a 'Master Ledger' worksheet that contains a summary of all the data on the individual project sheets. The master ledger is deleted and rebuilt using the data on the project sheets with VBA when a command button is pressed.

    I now need to be able to build the project sheets using only the Master Ledger data. Workflow would be something like: 1) build master ledger from project sheets, 2) edit the data on master ledger, and 3) delete data on project sheets and then rebuild them using only the data from the master ledger.

    All project sheets contain "(Data)" at the end of the sheet name to help identify them as project sheets. All ledgers start on row 9 including the master ledger. The master ledger contains a column with the originating sheet name. I've attached a small sample workbook to show you the basic layout of my tool.

    I appreciate any help you can offer and will pay $25 by PayPal for a solution. If more info is needed, please let me know. Thank you!

    Re: Validate Cell Entries To Named Range List

    AAE and Dave, thank you for taking the time to provide input. I've given this a lot of thought and of course you both are correct. Adding DV to the cells directly is certainly the best approach. After all the VBA generated for this project I simply got caught up in the 'it has to be VBA' mindset. Again, appreciate you taking the time to reply when the solution was obvious already. Cheers.

    Re: VBA to validate cell entry based on named range

    AAE, I completely agree that for a simple workbook the built in Data Validation feature of Excel works perfect. However in my case I have a complex workbook. There are 20+ worksheets of data ledgers of varying length. New ledgers are added frequently. I currently use VBA to consolidate all these ledgers into a Master Ledger and then generate pivots based on several user selectable parameters. When the Master Ledger is generated, I use VBA to go in and replace all formulas since these are often accidentally overwritten by the user. Since I have no way of knowing how many rows of data each sheet will contain, I was looking for a way to use VBA to verify user entries were correct. I realize it's a more complicated approach, but I think in this case it might offer a more robust solution. Any thoughts?

    I have been trying to figure out how to use a named range (on another sheet) to validate the user entered data in a specific column. All my attempts at utilizing worksheet_change event have ended in errors. I've created a simple workbook that shows what I'm trying to do and have attached it to this message. The 'Sample (Data)' worksheet has the data table that would be completed by the user. On the 'Validation' worksheet I have named the range to be used to validate the data as 'rngVal'. So when something is entered under the 'Expense Type' heading, the code would verify that the value entered was contained in the 'myVal' validation list. If it was not present in the list, a msgbox would instruct you to try again. Any help would be greatly appreciated.

    After stealing bits of knowledge and code from many of the posts on this site I cobbled together the following (which results in an error at the 'set rngFind' line):

    Re: Store Current Sheet Name As Variable

    I've been working at this since posting the last update. If I add the line "Sheets(DataSheetName).Activate" to the end of the PivotByQuarter code it works as expected. Pivot table is created on sheet "Summary View" and then you are returned to the original sheet containing data. It looks like my problem is that I store the current sheet name as a variable when running the PivotByQuarter code, but then when I try to call that saved variable from another module, it's no longer saved or available for use. How do I store a variable in one module and then call that stored value in another module? Please help... Thanks in advance.
    (If this should be a new post, please let me know. It's related to my original title/question, but I don't want to violate forum rules. Thanks)

    Re: Store Current Sheet Name As Variable

    Thank you both for the help but I am having trouble applying it to my situation. I’m posting the code for the two macros I’m using to switch between the “data” sheet and the “pivot” sheet.

    Here is the code that builds the pivot cache, takes you to the sheet named “Summary View” and builds the pivot table:

    Here is the code for that is supposed to take you back to the original page the data is on:

    When I run the PivotByQuarter code from a worksheet containing data the pivot table gets created on the “Summary View” sheet without trouble. However when I run the ShowData code from the “Summary View” worksheet I get the following VB error: Run-time error ‘9’, Subscript out of range with the Sheets(DataSheetName).Activate line highlighted when I click debug. Any idea what I’m doing wrong?

    I have a workbook that is composed of forty (or so) worksheets containing data and a single summary worksheet that has command buttons that take the user to the appropriate data worksheet for their specific project. Each data worksheet is exactly the same in terms of where the header row starts, and the specific headings.

    On each data worksheet there is a command button that when clicked, builds a pivot table of the data for the current project. I have been able to create VBA code that hides the columns containing the data and then creates the pivot table in the empty (unhidden) columns n the same worksheet. This works fine, but is not a good solution from a useability standpoint.

    What I have been trying to do is when the command button is clicked I want to capture the name of the current worksheet as a variable in VBA, go to a separate worksheet to build the pivot table, and when the user clicks a ‘Review Data’ command button on the pivot table worksheet they are taken back to their original worksheet containing their data. Is this possible?

    I have searched for hours on this forum (and the web in general) for a solution and haven’t been able to find one (possibly because this is such a basic problem…) I’m just learning VBA so let me apologize in advance if this is a dumb question. Any coding help is greatly appreciated! Cheers, Pete

    Re: Dynamically Sort Generic Pivot Table Field

    Well sort of... I think I know what I need to do, I just don't know how to do it. I'm still learning how to write loops and if-then-else code in VBA and I can't figure out how to write the code so that it is generic to actual pivot field row names. I want to know if there is a way to write a loop to sort all pivot rows from the first to the last without writing the actual name of the pivot row in the code. Sorry if this is a dumb question... Thanks in advance for any help or suggestions you might have.

    Is there a way to generically sort all pivot rows of a pivot table alphabetically each time the table is refreshed? I have a pivot table that pulls its data from a dynamic named range on a separate worksheet. As users add data to the range and refresh the pivot table the newly added items appear at the bottom of the table rather than alphabetically. I know how to specifically call out a pivot row and have it sort alphabetically (see code below) but I still want to allow users the flexibility of changing the fields and order of the pivot rows so specifically calling out a pivot row by field name isn’t going to work. I need to somehow add code that says: For each pivot row (1 to n, where n = number of pivot rows) sort each in alphabetical order.

    ActiveSheet.PivotTables("ptDWDM").PivotFields("Project Name").AutoSort _
            xlAscending, "Project Name"

    So in the above code I don’t want the reference to “Project Name” to be hard coded. Is this possible?

    Re: Consolidate Data Multiple Worksheets Based On Naming Convention

    Well this worked like a charm in the simple example spreadsheet I uploaded, but when I copied it into my real workbook I got an error. Here is my actual code:

    When I run this I get “Run-time error ‘1004’. Application-defined or object-defined error.” When I click debug the “With .Resize(.Rows.Count – 1).Offset(1)” row gets highlighted. Any idea what I might be doing wrong?