Excel 2007 VBA - macro stops after autofilter command

[KBarrett]

I have a spreadsheet that was created in an earlier version of Excel
and is now being used in Excel 2007 with compatibility mode on. One
of the macros filters the data on the sheet, using the autofilter
command, based on selections the user has made via a pop-up form.
Previous to Excel 2007, this was working fine. Now it appears the any
command lines following the autofilter command are ignored. Has
anyone encountered a similar situation?

 

[OssieMac]

Can you post the code where it gets the userform data and the Autofilter is
invoked plus a few lines that you say are not getting processed.

Also is the userform closed before the Autofilter is invoked?

Have you tried putting a msgbox between some of the lines that you believe
are not being processed to establish whether in fact they are not being
processed?

 

[Martin Brown]

I have a spreadsheet that was created in an earlier version of Excel
and is now being used in Excel 2007 with compatibility mode on. One
of the macros filters the data on the sheet, using the autofilter
command, based on selections the user has made via a pop-up form.
Previous to Excel 2007, this was working fine. Now it appears the any
command lines following the autofilter command are ignored. Has
anyone encountered a similar situation?

Not that particular one, but it is worth looking in the MSKB to see if
they actually admit to a problem with migrating XL2003 autofilter.

Does it stop with an error or appear to skip lines of code?

I have basically lost count of the number of horrors I have seen moving
complex spreadsheets with VBA from XL2003 to XL2007. I cannot for the
life of me see why anyone would install XL2007 until it is actually
stable. Productivity takes an instant dive and never seems to recover.
Still the customer is always right - even when they say there are two
M's in custommer (sic).

It is great fun watching experienced XL2003 users struggle to find where
in the new ribbon interface crucial options have been hidden.

Regards,
Martin Brown

 

[KBarrett]

I have a spreadsheet that was created in an earlier version of Excel
and is now being used in Excel 2007 with compatibility mode on.  One
of the macros filters the data on the sheet, using the autofilter
command, based on selections the user has made via a pop-up form.
Previous to Excel 2007, this was working fine.  Now it appears the any
command lines following the autofilter command are ignored.  Has
anyone encountered a similar situation?

I think I may have stumbled upon the cause of the problem. The
worksheet has a LOT of conditional formatting that has gotten messy
over the years but had never caused a problem in previous versions of
Excel (one of the things on my 'to-do' list was to clean it all up in
the 2007 version). When I removed all of the conditional formatting
from the sheet, my code ran fine and continued on past the autofilter
commands. I'm guessing the code was hanging on trying to evalute the
conditional formatting after the autofilter? I had put a timer in
the code before and after the one autofilter line, but lost patience
after waiting 15 minutes (there are only 4500 lines of data on the
sheet). As I sais, this wasn't a problem in Excel 2003. Now I have
to go back and add a cleaned-up version of the conditional formatting
and hope it doesn't hang on the autofilters again. Ahhhh,
Progress!!

Thanks for the responses that helped get me thinking "outside the box"
on ths one!

Karen

 

[KBarrett]

I have a spreadsheet that was created in an earlier version of Excel
and is now being used in Excel 2007 with compatibility mode on.  One
of the macros filters the data on the sheet, using the autofilter
command, based on selections the user has made via a pop-up form.
Previous to Excel 2007, this was working fine.  Now it appears the any
command lines following the autofilter command are ignored.  Has
anyone encountered a similar situation?

I think I may have stumbled upon the cause of the problem. The
worksheet has a LOT of conditional formatting that has gotten messy
over the years but had never caused a problem in previous versions of
Excel (one of the things on my 'to-do' list was to clean it all up in
the 2007 version). When I removed all of the conditional formatting
from the sheet, my code ran fine and continued on past the autofilter
commands. I'm guessing the code was hanging on trying to evalute the
conditional formatting after the autofilter? I had put a timer in
the code before and after the one autofilter line, but lost patience
after waiting 15 minutes (there are only 4500 lines of data on the
sheet). As I sais, this wasn't a problem in Excel 2003. Now I have
to go back and add a cleaned-up version of the conditional formatting
and hope it doesn't hang on the autofilters again. Ahhhh,
Progress!!

Thanks for the responses that helped get me thinking "outside the
box"
on ths one!


Karen

 

[nod]

I think I may have stumbled upon the cause of the problem.  The
worksheet has a LOT of conditional formatting that has gotten messy
over the years but had never caused a problem in previous versions of
Excel (one of the things on my 'to-do' list was to clean it all up in
the 2007 version).  When I removed all of the conditional formatting
from the sheet, my code ran fine and continued on past the autofilter
commands.  I'm guessing the code was hanging on trying to evalute the
conditional formatting after the autofilter?   I had put a timer in
the code before and after the one autofilter line, but lost patience
after waiting 15 minutes (there are only 4500 lines of data on the
sheet).  As I sais, this wasn't a problem in Excel 2003.  Now I have
to go back and add a cleaned-up version of the conditional formatting
and hope it doesn't hang on the autofilters again.  Ahhhh,
Progress!!  

Thanks for the responses that helped get me thinking "outside the
box"
on ths one!

Karen

You are not the only one with this problem, as I have the same.

nod