Posts by ShosMeister

Re: Trigger ItemAdd event with MAPI/Internet calendar

Rats! Guess no one has any ideas on this?

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
Stop
  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.

Function GetFolderPath(ByVal FolderPath As String) As Outlook.Folder
  Dim oFolder As Outlook.Folder
  Dim FoldersArray As Variant
  Dim I As Integer
  
  'On Error GoTo GetFolderPath_Error
  
  If Left(FolderPath, 2) = "\\" Then
    FolderPath = Right(FolderPath, Len(FolderPath) - 2)
  End If
  
  'Convert folderpath to array
  FoldersArray = Split(FolderPath, "\")
  
  Set oFolder = Application.Session.Folders.Item(FoldersArray(0))
  If Not oFolder Is Nothing Then
    For I = 1 To UBound(FoldersArray, 1)
      Dim SubFolders As Outlook.Folders
      Set SubFolders = oFolder.Folders
      Set oFolder = SubFolders.Item(FoldersArray(I))
      
      If oFolder Is Nothing Then
        Set GetFolderPath = Nothing
      End If
    Next
  End If
  
  'Return the oFolder
  Set GetFolderPath = oFolder
  Exit Function
  
GetFolderPath_Error:
  Set GetFolderPath = Nothing
  Exit Function
End Function

Public Function GetFolder(strFolderPath As String) As MAPIFolder
  ' strFolderPath needs to be something like
  '   "Public Folders\All Public Folders\Company\Sales" or
  '   "Personal Folders\Inbox\My Folder"
  Dim objApp As Outlook.Application
  Dim objNS As Outlook.NameSpace
  Dim objFolder As Outlook.MAPIFolder
  Dim colFolders As Outlook.Folders
  
  Dim arrFolders() As String
  Dim I As Long
  On Error Resume Next
  strFolderPath = Replace(strFolderPath, "/", "\")
  arrFolders() = Split(strFolderPath, "\")
  Set objApp = Application
  Set objNS = objApp.GetNamespace("MAPI")
  Set objFolder = objNS.Folders.Item(arrFolders(0))
  If Not objFolder Is Nothing Then
    For I = 1 To UBound(arrFolders)
      Set colFolders = objFolder.Folders
      Set objFolder = Nothing
      Set objFolder = colFolders.Item(arrFolders(I))
      If objFolder Is Nothing Then
        Exit For
      End If
    Next
  End If
  Set GetFolder = objFolder
  Set colFolders = Nothing
  Set objNS = Nothing
  Set objApp = Nothing
End Function

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.
Thanks!!!

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: Automate Individual Emails with unique Data as Attachment using Macro

Quote from LCLARK62;567515

Hi there.

This is exactly what I was looking for. Much appreciated

Regards
Lance

YW

Re: Automate Individual Emails with unique Data as Attachment using Macro

Singla,

Since you are not the originator of this thread, I can only guess that you are either hi-jacking it or worse, spam of some sort. As such, you will not be receiving an e-mail. If you have a problem, please post a new thread.

Re: Consolidate data from 1 worksheet to a summary page

Who created the worksheet? You may need to have them adjust the lists to have defined names which would make things "slightly" easier. Otherwise, it can be done but it will not be very flexible to changes in the lists.

Re: Consolidate data from 1 worksheet to a summary page

The other issue is the extensive number of "lists" that are being used by the pull-downs and none of them are name referenced. That's going to make it impossible to maintain.

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

E-mailing the file would be fine but remember the rules state that you keep the discussion on the site and don't PM/e-mail individuals for help directly.

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: How to create a dynamic chart using a list box

Like this?

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?

Re: Create stacked column chart

try this.

Re: Disable/Enable Check box

Change them from CHECKBOXES to RADIO BUTTONS.

Re: Automate Individual Emails with unique Data as Attachment using Macro

Try this. Assuming you are on a PC and running Outlook.

Re: Consolidate data from 1 worksheet to a summary page

In your attachment, could you provide what you expect the summary to look like given the sample data on the order form?