Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Macro error (Outlook 2003)

    I have a macro that has worked great for a while. Now it stops and gives errors always on the last item it gets to. I am attaching a word doc with the coding and he error message. No matter how many items I have in my list to chart, it always stops on the last one. I have put in an On Error statement that keeps me from getting the error message but the last item is not processed. I am at my wit's end. Any help is appreciated.

    itcinc

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro error (Outlook 2003)

    Could you post (a stripped down copy of) the workbook?

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro error (Outlook 2003)

    Here it is. I have left everything that is impotant - I hope.

    Thanks,

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro error (Outlook 2003)

    In the workbook you attached, the first series of the chart, i.e. Charts("Risk Matrix").SeriesCollection(1), has only 8 points, so the code executes correctly for i = 1, 2, ..., 8, but errors when i = 9. Instead of

    i = 1
    Do While i < 21
    ...
    ...
    i = i + 1
    Loop

    it would be better to use

    For i = 1 To Charts("Risk Matrix").SeriesCollection(1).Points.Count
    ...
    ...
    Next i

  5. #5
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro error (Outlook 2003)

    Thank you Hans. No more error messages. However, it will not process the last item. I went ahead and inserted the last item and then sorted to get them in order. That did the trick. There is no range defined so that last row is not outside the range. I have just tried it again. It must be because of the rand for the chart - as a matter of fact I just checked the source data for the chart and that is the issue.
    Do you have any idea how I can handle this in the program?

    Thanks so much for your help,

    itconc

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro error (Outlook 2003)

    You can use dynamic ranges to define the chart series, so that points will be added automatically if the user adds a new row to the table in Risk Register. See attached version.

    Look at Insert | Name | Define... to see the definition of the named dynamic ranges, and click on the series in the chart to see how the named ranges have been used.

    See Dynamic Ranges in Named Ranges on Chip Pearson's site for more info about named ranges.

  7. #7
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro error (Outlook 2003)

    Thank you Hans. The problem I now have is that the Update Issue Sheet button blows up on me.
    This was working fine before so I am not sure whta I did to cause this. The code does not look different now.

    Help Please!

    Thanks

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro error (Outlook 2003)

    Could you attach the latest version, and explain a little what you mean by "blows up on me".

    I assume you are not being literal. (If you are then maybe you shouldn't attach it <img src=/S/smile.gif border=0 alt=smile width=15 height=15>)

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro error (Outlook 2003)

    It blows up when you click on the click on the update issue sheets button. I marked the row in the code. The problem is it sometimes works and sometimes not. I have not been able to identified what causes it to work or to blow up.
    Thanks for looking into this.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro error (Outlook 2003)

    Clicking that button adds 9 Issue sheets for me with no apparent problems. Can you be more specific about what you are doing?
    Legare Coleman

  11. #11
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro error (Outlook 2003)

    I must be going nuts!. It worked for me also when I tried it right now. However, when I add a row it does not give me a new sheet when I rerun the button.
    I am attaching that file.

    Thansk,

    itconc

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro error (Outlook 2003)

    I don't have any problem with that one either.
    Legare Coleman

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro error (Outlook 2003)

    In XL97 I could not get it to work unless the button did not have the focus (this is a issue with XL97). Change the properties of the button by getting in Design mode and changing the "TakeFocusOnClick" property to false.

    The code then seems to work fine (in XL97).

    Steve

  14. #14
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro error (Outlook 2003)

    Well, I feel like a fool. I can still not get the 10th issue copied to a separate sheet. However, I think I am going to give up for the day and try again tomorrow - maybe I will feel less foolish.

    Thanks all of you for your help and suggestions.
    This forum is awesome!!

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro error (Outlook 2003)

    Your workbook already contains a worksheet named Issue 10, but it is hidden - see Format | Sheet | Unhide...

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •