Posts by Charis Custard

    Re: Conditional Formatting Delete Rule

    sounds like the fc.Type does not get assigned for user-defined conditions or something. Have you ever tripped on

    fc != null

    ? I can't do much to help troubleshoot since I haven't got a copy of excel 2007. With 2003 and vba I'd have a GOTO pointer to skip to the next loop item, but that's cheating ;)

    Re: Conditional Formatting Delete Rule

    have you tried a messagebox to display what the type is to see why your switch function won't work?


    int type = fc.Type

    works, then you should be able to switch your "type" variable in place of fc.Type. Might be a type mismatch problem, but without knowing the value it fails on it's hard to tell.

    Re: Model Not Saving

    I would actually resist the urge to disable events - since any error could skip out and disable events altogether, and it's possible that it may prevent a legitimate other event from firing.

    Have you tried defining and setting a global variable instead such as "blnInSaveEvent"? You could set it to TRUE where you would otherwise disable events and FALSE when you are done, and your before save event could test the value of the variable before running and exit the sub if it is TRUE.

    Re: Prevent Macro Buttons Opening Another File To Run Macro


    I suspect that if you swap the forms buttons for the buttons found on the control toolbox, then your macros will still work once the sheet is detached - HOWEVER, control toolbox buttons operate differently and need to have macros assigned in the Click event of the object rather than just "attaching" them to the object.

    I agree with dr, a prototype would help us help you come up with a solution.

    Re: Updating Distributed VBA Macro Code

    when running addins it's best to have some form of trigger for the macro other than the events - in the scenario you describe it sounds like the macro is running both from the sheet and from the addin. Have you considered having some form of update hotkey, like Ctrl+Shift+U rather than having it run on open? This would avoid the update running on unrelated workbooks automatically. The alternative is to name your macro sequence as a public Subroutine (put it in a code module to make sure you can call it directly) and call that from the xls workbook you wish to update, i.e.

    put the following code in the xls file:

    Private Sub Workbook_Open()
       Application.Run "UbdateWBData"
    End Sub

    It will run the "UbdateWBData" Sub from your active addins (change this to whatever your Sub name actually is), but that macro will not run automatically from any other workbook.

    Also, are you sure when you opened the xls it was the version that was stripped of code? It should not normally ask you whether to run code from addins, so the prompt sounds like it was launched from your xls file opening.

    Re: Optimize Vba Code For Speed

    you can actually bulk paste an R1C1 formula into an entire range.


    ThisWorkbook.Sheets("Data").Range(Cells(1, 2), Cells(10000, 2)).Formula = "=INT((MONTH(RC[-1])-1)/3)+1" 
            ThisWorkbook.Sheets("Data").Range(Cells(1, 2), Cells(10000, 2)) = ThisWorkbook.Sheets("Data").Range(Cells(1, 2), Cells(10000, 2)).Formula.Value

    in place of your loop

    Re: Translate Cell Values Two Worksheets


    the workbook I uploaded contains a macro that will just about do the trick. Open this workbook, and then the sheet you want translated. Then hit CTRL+t or run the macro from the macros list.

    It will replace values in Column A of the active workbook to the corresponding legal description match from Column B of the glossary workbook (which the form will prompt you to open).

    The only thing it does not do is differentiate between the 2 instances of =1335. If they are physically identical, what criteria should I be using to tell them apart?

    Let me know and I will make adjustments.

    -Charis )o(

    Re: Create Pie Chart From Table Summary Results

    Quote from binar

    What I am curious to find out and would appreciate any feedback from anyone out there relates to the dynamic pivot chart itself. Will such a dynamic pivot chart automatically update if I apply a filter in the source data for a date range of five weeks or three weeks? Or does the Pivot table have to be manually refreshed so that the pivot chart updates?

    I would use the page fields on the pivot table itself rather than the autofilter in the source if you are going the pivot table route. Just keep in mind if you are going to group data (i.e. dates by month or some such) you need to do it before dragging the field to the page level. I prefer to do pivot table groupings via VBA in case I do have to replicate them later.

    But Dr's VBA solution would work just as well for your purpose and allow you to keep your autofilter functions in a consistent way. You'll find there are often many ways of accomplishing the same goal in excel and some choices do come down to personal preference since all alternatives have some drawbacks (although some others are clearly superior)

    You'll still want to check out dave's link on dynamic named ranges though. A formula you have personally set beats defining a "Table" or "List" in excel 9 times out of 10 and will ensure back compatibility to excel 2000 in case you like me need to send the sheet to a variety of users.

    Re: Create Pie Chart From Table Summary Results

    What dave means is use a named range as the source for your pivot table - in place of just cell references.

    Then the name can contain whatever formula you like to have it keep pace with your data contents - that's where the dynamic link come in and I highly recommend you check out those formulas - I use them on a daily basis.

    Re: Efficient Layout Of Data For Analysis

    Cut the guy a break Dave - you know tone can't always be interpereted in forum posts ;)

    And your posts are often so efficiently concise that they're hard for beginers to interpret... *grin*

    Breathe deep and give everyone's posts the benefit of the doubt (that goes to both of you though). You both appear to have read sarcasm in each others previous posts where none was likely intended from my point of view.

    Now I'm crawling back into my cubicle and won't feed the trolls anymore :P

    Re: Code In BeforeSave Event Causing a Double Save

    It sounds like the save prompt is coming from the close event, not the save event.

    you may want to set cancel=true in the before close event, then close the worksheet manually usting:

    ThisWorkbook.Close False

    to disable the user prompt since you've already saved from the sound of things.

    Re: Prevent Unhiding of Hidden Sheets

    you could lock the workbook structure, thus preventing the hiding or unhiding of sheets without the password (which as you've pointed out would be malicious...)

    but then your previously mentionned macro would need to protect and unprotect the workbook everywhere whenever you wished to show or hide anything.

    Stil not "secure" but to circumvent it would require deliberate "malicious" action.

    Re: Aggregate values from multiple worksheets into a total

    Try the attached - I created a UDF called SumTarget with inputs so you can change where things are located:

    Function SumTarget(rngChargeCodes As Range, strCode As String, rngNames As Range, rngHours As Range, rngRates As Range)
        Dim lngCount As Long
        SumTarget = 0
        For lngCount = 1 To rngChargeCodes.Rows.Count
            If rngChargeCodes.Cells(lngCount, 1) = strCode Then
                SumTarget = SumTarget + rngHours.Cells(lngCount, 1) * Application.WorksheetFunction.VLookup(rngNames.Cells(lngCount, 1), rngRates, 2, False)
            End If
        Next lngCount
    End Function

    rngChargeCodes is the range containing the chargecode data
    strCode is the code you want the sum for
    rngNames is the range in the charge code table with the names
    rngHours is the range in the charge code table with the hours worked
    and rngRates in the full table of roster rates

    Re: Retrieve Multiple Results On Multiple Criteria

    I would definitely go with a macro to transfer your criteria to the filter/pivot selector. you can even hide your autofilter columns so at first the user sees only the whole list of products and that gets narrowed down as they select from your dropdown (either turn your validation into a true combobox and attach a macro, or embed it in the worksheet_change event and check the target address is one of your filters).

    Otherwise the formulas get ugly and will chew up resources making your book a lot slower (depending on amount of data)