object not found error

[Willardio]

I have the following code that, among other things, delete's specific oleobjects if the referenced rows are hidden. The problem is that it gets stuck when it doesn't find an object, whereas I would like it to simply skip ahead in the module. Here's my code:

Please Login or Register  to view this content.

Any help will be much appreciated, thanks!

[Norie]

You need the on error before the line giving you the error

Please Login or Register  to view this content.

[Willardio]

Thanks Norie but I don't know how to do it, so can you help me with that? I tried simply moving the line up, but no dice.
Here's a sample with the code in it:
Object doesnt exist sample.xlsm

if you tried this earlier, please download again. the sample was flawed but i fixed it.

[xladept]

Hi Willardio,

Try this:

Please Login or Register  to view this content.

[Norie]

What objects are you actually trying to delete?

There are none with a name like WDME.

Also, where is BRNumRGT defined and/or given a value?

[Willardio]

sorry for the confusion Norie, xladept helped me with this in an earlier thread and there is a sample sheet there:

http://www.excelforum.com/excel-prog...html?p=3051926

Plus I got my code all messed up trying to solve this last glitch by myself! I have to go out now but I will clean the code up as best I can tomorrow or the next day and reply to you in this thread again.

Merry Christmas!

~~Willardio~~

[Willardio]

Norie, here's the sample: delete excess Oleobjects problem sample.xlsm

[OllieB]

Hi Williardo,

I was curious about what you were trying to achieve so I have given it a go as well. I have re-written the original routine and have given it a different name. If you want to use this you will have to copy & paste the code below into any module and change the code in the worksheet_change event to call this new routine as opposed to the old routine. I have tested it and thinks it works.

Please Login or Register  to view this content.

Hope it works for you

[OllieB]

Ps. Manually delete existing objects before using the new code because I have slightly changed the name convention.

[Willardio]

Olaf, your solution (module 'pub_sub_PrepareWorksheet') works perfectly, only it seems to add the boxes three rows too high. at first it was not doing that, so maybe it's something i did? I didn't alter your code, but i did remove three rows on the last page so that the pages will all be the same length. The other thing is that I really need a pagebreak every 43 rows throughout the document, excluding hidden rows (so that the page numbers will show correctly when printing). This is because other macros called "AddPage" (not included in this sample) sometimes are called to add exactly 43 rows (one page) at various points. I added a macro for that (PageBreaks43rows) that might be called from your macro to achieve this.
Here's the new sample:

delete excess Oleobjects problem sample Olaf Solution.xlsm

I was wondering also whether there is a way to do this without the "on error resume" line at the beginning. Perhaps that could be put within the 'Oleobjects.delete' subroutine where it is needed?

Thanks for all of your help so far. This code runs much faster than my previous code and is looking very promising!

Best,

Willardio

[OllieB]

Williardo, I will try to have a look tomorrow otherwise it will be Thursday. Rgds Olaf

[Willardio]

Thank you Olaf, this has already been my best Christmas present this year! )

[OllieB]

Hi Willardo,

I have removed the On Error Resume Next in the main routine (though I am sure it was used properly).

I have added a new function to check for the existence of an OLE Object with the desired name, and if it cannot be found, ignore that error as a new object will be created anyway. The new function is the only one now that still requires on the On Error Resume Next solution.

I have run the process a couple of times and the OLE objects get positioned in the correct location (or at least I do not see any problem).

The page breaks are inserted properly by the code for every report, except for a missing fixed page break for the final page. I have added a line of code for that in the main routine, so that should be solved now as well.

The code is shown below and I have enclosed the workbook as well.

Please Login or Register  to view this content.

delete excess Oleobjects problem sample Olaf Solution revised.xlsm

[Willardio]

Thank you Olaf. I did lot of testing and found that when I set F19 to 0 or blank, the next time I reset that cell to another number is when the oleobjects jump up three rows. However, if I manually delete the Oleobjects BEFORE setting 0 or blank at F19, the problem doesn't happen. so is there a simple code I can run to delete all Shapes with names starting "WDME*" as a before_change event? With that your original code will work fine.

Sorry to be a such a nudge, but my users can be even bigger nudges!

Best,

Willardio

[OllieB]

Hi Willardio,

No problem, if there is a bug, I will fix it. That brings me to the next point, I have tried to do what you do:
1. enter 5 in F19;
2. enter 0 in F19;
3. enter 5 in F19

and

1. enter 5 in F19;
2. enter blanks in F19;
3. enter 5 in F19

But in all situations the OLE Objects are created at the correct rows (at steps 1 and 3), and are all removed in step 2. The object are positioned at rows 74, 117, 160, 203 and 246. Are these the correct/desired rows?

[OllieB]

Hi Willardio, please note that I am testing with the most recent version, the one that I have submitted to you yesterday at 10:02. Have you perhaps made any changes in your version since then?. Something perhaps with the form length? From which report onwards are you seeing a shift of rows? Can you post a copy of the workbook that you are using that shows the error mentioned.

[Willardio]

This is a new sample based on your first code, not the new one. I will try the new code now.

delete excess Oleobjects with separate pagebreaks macro and explaination.xlsm

Thanks,

Willardio

[Willardio]

It's the view! as long as i am using Page Break Preview when I run the code the objects place fine. When I run it with the headers and margins showing is when they move up three rows. I will try adding a bit of code to switch views at the beginning and end of your macro to see if that corrects it.

One other thing: I noticed in the sample you sent the objects are the right size. On my machine I get the propper width but the height is always short. Adjusting .Height or removing the # symbol have no affect here.

[Willardio]

I added view switching code at the beginning and end, inside the EnableEvents and and it works fine now.

Please Login or Register  to view this content.

The only remaining problem is the .Height setting, but the subject of this thread is solved as far as I'm concerned.
Thanks so much for your help, you have undoubtedly prevented countless incidents of office violence and computer vandalism, and provided me the best christmas present ever!

Best Regards,

``Willardio

[OllieB]

Willardio, glad to hear. You are welcome