Posts by ShosMeister

    Been scratching my head on this for a bit and figured I'd better get back to the experts.

    First off, I know everything else is working as if I change the code to deal with the default calendar, the events get triggered properly. Here's the situation.

    Dim WithEvents curCal As ItemsPrivate Sub Application_Startup()
      Dim NS As Outlook.NameSpace
      Set NS = Application.GetNamespace("MAPI")
      Set curCal = NS.GetDefaultFolder(olFolderCalendar).Items
      Set curCal = GetFolder("iCloud\FMFA").Items
      Set NS = Nothing
    End Sub

    When I run, curCal looks fine in both calls and is pointing to the proper location verified by the number of items that it shows.

    There are two functions, GetFolderPath and GetFolder that I copied from elsewhere online. For some reason the GetFolderPath only works with the default folder and errors out on the internet folder so I used the GetFolder for that one. Ideally, would prefer to have one but that can wait.

    I put a Stop in my curCal_ItemAdd sub and if I use just the default folder value, it triggers just fine. When I use the internet one (iCloud\FMFA), it never triggers.

    Figure I'm missing something really simple here but just can't see it.

    Re: Reference table headers in conditional sum formula

    Are you using Excel 2007? If so, look at the SUMIFS which is new in 2007. It allows for multiple criteria. You could then change the left heading to the value you want to match similar to what you are doing with the $C$2/$F$2 reference (number and sales).

    Not sure if this will help or not but thought I'd throw it out there.

    Re: Consolidate data from 1 worksheet to a summary page

    Here's the issues:

    You have pull-downs that are using a look-up list on another sheet to populate the choices. No big deal as that's the best way to do it. Issue is it's hard coded to an absolute range for each of the pull-downs.

    It is then linked to another cell (the one directly under it) that puts the index, 1 to x, in that cell. That value is then used in another lookup to find the cost associated with that item. Again, not a big problem as that's the normal/best way to do it. Issue is again that it's all hard coded for each cell to the references.

    Now, I have to try to figure out what pull-down has been changed, fairly easy as the index will be >1, and then try to figure out what the value is in that pull-down. That's the difficult part. Since the index is a number from 1 to x, I could then use that number to find the value in the list. The issue is there is no way to identify the list without completely hard coding the lookup. This will result in everything breaking if anything in the table of lookups is changed.

    What I've managed to figure out is that when the workbook is opened, I step through every shape on the "Order Form" worksheet. If it's a pull-down, I then capture the linked cell and reference and store those values in an array. Then, when I find a value that has changed on the Order Form (index >1), I look up in the array the linked cell equal to the cell I'm currently looking at. Then I can get the reference and use the index to get the actual text value.

    I've e-mailed you back the workbook as it's a bit large to be attached here and didn't want to submit it publicly with possible proprietary values. Let me know if it is functioning properly. The macro can be run from the list but could have a button put on the worksheet or even setup to run automatically every time one of the cell values changes on the Order Form.

    The lesson here is that a lot of what you may or may not be able to do with a workbook depends on design so you kind-of have to plan ahead. Keep this in mind when you design other workbooks or try to re-design this one.

    Re: Consolidate data from 1 worksheet to a summary page

    Would just make it more difficult for you to maintain/adjust given your inexperience with VBA.

    Here's the basic process that I'm thinking:
    Since your pull-downs all link to column C, step through Cx to find cells >1. That indicates something was selected there.
    Copy those values from the table using the value in Cx as the index for the text.
    Copy the cost values from the order form.

    Decision: Use a button for manual launch of the macro or make it automatically run somehow based on changes on the invoice sheet.

    Re: Consolidate data from 1 worksheet to a summary page

    Took a quick look. One caution would be that your "Order" may have a lot more than you have lines for in the Summary. Not really a simple way to handle that, but .....

    Also, can't think of any way to do this without VBA. I'll try to get some work done on it in the next day or so.

    Re: Consolidate data from 1 worksheet to a summary page

    So I take it you didn't design the original worksheet then? It may not be able to be done with just cell formulas given your requirement of only displaying the relevant data so it may require VBA code. Not sure if you'd want to go that route just yet given your newness to Excel.

    Also, your example, while okay, has a lot of errors in the worksheet. There are #REF errors in a lot of the pull-downs so it would be best to have a better working copy.

    Re: Inserting excel spreadsheet into word doc

    If you don't want the worksheet to be visible, why even put it in the Word doc? Why not just use VBA in Word to generate the random number and assign it to a field variable. Then you just use that reference in place of the text in your doc (which it sounds like you are doing anyway).

    Re: Reference table headers in conditional sum formula

    If you have the list defined, you just use the list name. Don't understand why you want to use an indirect reference to a list unless there is something else going on. If you have a sample that isn't working, attach that so we can see what you have and also include what it's supposed to look like/do.

    Re: Consolidate data from 1 worksheet to a summary page

    Okay, the issue you are having is trying to get the value out of your control, right? Instead of placing controls on the sheet, why now just use the cell pull-down feature? You already have the lists defined so that's a relatively easy change. Then, your summary just pulls the data from the cells rather than trying to find the value out of the control. Make sense or am I missing something?