Posts by lackeye

Re: Clear Forms Control ComboBox

Ok, I'll take that approach. Thanks.

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

Thank you for having a look and fixing this problem as well! Payment was sent so please let me know if you do not receive it in PayPal. All the best, Pete

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?

Re: Apply Pivot Item filter to Grouped Data

Excellent. Thank you.

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.

Re: Split data from one sheet to several sheets

THANK YOU! It works, I'm reviewing the code now to make sure I understand why it works, but IM me your email address so I can send payment. Thank you again!

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):

Private Sub Worksheet_Change(ByVal Target As Range)


Dim rngFind As Range
'Set Target = Cells(ActiveCell.Row, ActiveCell.Column)
    
Set rngFind = Range("rngVal").Find(Target.Value)


'If data in column C changes, do the following
If Target.Column = 3 Then


'If the value is found on the validation list, do nothing.
    If rngFind Is Nothing Then
        Else
            MsgBox "You must enter one of the following in this cell:"
            With Application
                .EnableEvents = False
                .Undo
                .EnableEvents = True
            End With
        End If
Else
End If
End Sub

Re: Store Current Sheet Name As Variable

Thanks for the fast reply Dave. This was the problem. Works perfectly now. Have a great night!

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:

Sub PivotByQuarter()


    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalCol As Long
    Dim FinalRow As Long
    Dim DataSheetName As String
    Dim PTsheet As Worksheet
    
    Set PTsheet = Sheets("Summary View")
    Set WSD = ActiveSheet
    
          
    Application.ScreenUpdating = False


'Turn worksheet protection off
    WSD.Unprotect
 
'Define input area and set up a Pivot Cache (FinalCol assumes data starts in row 10)
    FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(10, Application.Columns.Count).End(xlToLeft).Column


'Assumes data begins on row 10 (To do this the range is offset from Row 1 by 9 rows)
    Set PRange = WSD.Cells(1, 1).Offset(9).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)
    
'Create the Pivot Table from the Pivot Cache starting on row 10 and offset from last data column by 2 columns to the right
    DataSheetName = ActiveSheet.Name
    PTsheet.Unprotect
    PTsheet.Activate
    
    Set PT = PTCache.CreatePivotTable(TableDestination:=PTsheet.Cells(10, 1), TableName:="PivotTable1")
    
'Turn off pivot table updating while building the table
    PT.ManualUpdate = True
    
'Set up the Row, Column, and Page Fields
        PT.AddFields RowFields:=Array("Program Name", "Dept", "Location", "Project Name", "Group"), ColumnFields:="Data"
    
'Supress all subtotals except for "Program"
    PT.PivotFields("Project Name").Subtotals(1) = True
    PT.PivotFields("Project Name").Subtotals(1) = False
    
    PT.PivotFields("Group").Subtotals(1) = True
    PT.PivotFields("Group").Subtotals(1) = False
    
'Use Tabular layout for pivot table
    PT.RowAxisLayout xlTabularRow


'Supress blanks from showing in table for Row Headings
    With PT.PivotFields("Program Name")
        On Error Resume Next
        .PivotItems("(blank)").Visible = False
    End With
    
    With PT.PivotFields("Project Name")
        On Error Resume Next
        .PivotItems("(blank)").Visible = False
    End With
    
    With PT.PivotFields("Group")
        On Error Resume Next
        .PivotItems("(blank)").Visible = False
    End With
    
    
'Set up the Sum Values field (aka Data Fields)
    With PT.PivotFields("08Q1")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""?_);_(@_)"
        .Caption = "'08 Q1"
    End With
        
    With PT.PivotFields("08Q2")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 2
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""?_);_(@_)"
        .Caption = "'08 Q2"
    End With
   
'Ensure that we get zeroes instead of blanks in the data area
    PT.NullString = "0"


'Apply a Table Style to pivot table
    PT.ShowTableStyleColumnHeaders = True
    PT.ShowTableStyleRowHeaders = True
    PT.ShowTableStyleColumnStripes = True
    PT.TableStyle2 = "PivotStyleMedium2"


'Allow Excel to temporarily calculate and draw the pivot table
    PT.ManualUpdate = False
    PT.ManualUpdate = True
    
'Turn screen updating back on so final result is shown on screen
    Application.ScreenUpdating = True
        
End Sub

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

Sub ShowData()
    Dim WSD As Worksheet
    Dim PT As PivotTable
    Dim DataSheetName As String
    Dim PTsheet As Worksheet
    
    Set PTsheet = Sheets("Summary View")
    Set WSD = ActiveSheet
            
    Application.ScreenUpdating = False


' Delete any prior pivot tables
    For Each PT In PTsheet.PivotTables
        PT.TableRange2.Clear
    Next PT


    Sheets(DataSheetName).Activate
    
    Range("A11").Select


    Application.ScreenUpdating = True
          
End Sub

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?
Thanks,
Pete

Re: Consolidate Data Multiple Worksheets Based On Naming Convention

Works perfect. Your assumption was correct, I do have a few sheets with only a header row, so thanks for the modification. You just made my weekend (and coming week) much better! All the best.

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:

Sub BuildMaster()
    
Dim ws As Worksheet
Dim LastR As Range




' Application.ScreenUpdating = False
'
' Clear out any old data on Master Ledger. This code deletes all contents (including headers), 
‘ goes to the "Template"  worksheet and copies the header row information, and pastes it 
‘ back into the Master Ledger header area.
'
    Sheets("Master Ledger").Activate
    Sheets("Master Ledger").Select
    Range("A7").CurrentRegion.ClearContents
    Sheets("Template (Data)").Activate
    Range("7:7").Copy
    Sheets("Master Ledger").Activate
    Range("A7").PasteSpecial
'
' Search for sheets containing project data by looking for worksheets whose
' names end in "(Data)". Copy data to Master Ledger.
'
    For Each ws In Worksheets
        If ws.Name Like "*(Data)" Then
            Set LastR = Sheets("Master Ledger").Range("a" & Rows.Count).End(xlUp)(2)
            With ws.Range("a7").CurrentRegion
                With .Resize(.Rows.Count - 1).Offset(1)
                    LastR.Resize(.Rows.Count, .Columns.Count).Value = .Value
                End With
            End With
        End If
    Next
End Sub

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?