Excel 2007 ungrouping controls

[Iain King]

I have a workbook which is used on many computers on our network. One
of these computers has just been upgraded to Excel 2007 (the rest use
2000). Excel 2007 is set to save in Excel 97-2003.xls format, but
what we find is that when you save the workbook on the 2007 machine
and then open it in Excel 2000 all the buttons used to control the
sheet have been ungrouped and lowered behind the autofilter (and are
thus unclickable). How do I stop it doing this?

Iain

 

[Jim Rech]

Although someone who has encountered this exact problem may know what you
mean perhaps you should provide the exact steps to duplicate the issue.
What kind of buttons? From what toolbar? Why are they grouped? What does
"lowered behind an autofilter" mean?

--
Jim
|I have a workbook which is used on many computers on our network. One
| of these computers has just been upgraded to Excel 2007 (the rest use
| 2000). Excel 2007 is set to save in Excel 97-2003.xls format, but
| what we find is that when you save the workbook on the 2007 machine
| and then open it in Excel 2000 all the buttons used to control the
| sheet have been ungrouped and lowered behind the autofilter (and are
| thus unclickable). How do I stop it doing this?
|
| Iain

 

[Iain King]

Although someone who has encountered this exact problem may know what you
mean perhaps you should provide the exact steps to duplicate the issue.
What kind of buttons? From what toolbar? Why are they grouped? What does
"lowered behind an autofilter" mean?

--

|I have a workbook which is used on many computers on our network. One
| of these computers has just been upgraded to Excel 2007 (the rest use
| 2000). Excel 2007 is set to save in Excel 97-2003.xls format, but
| what we find is that when you save the workbook on the 2007 machine
| and then open it in Excel 2000 all the buttons used to control the
| sheet have been ungrouped and lowered behind the autofilter (and are
| thus unclickable). How do I stop it doing this?
|
| Iain

Sorry, I could have been clearer. Quick guide to duplicating:
Make a worksheet up with some tabular data, with the headers in row
1. Turn on the autofilter. Increase the height of row 1 so that
there is space above the header text. From the Draw toolbar add a
rectangle, covering the top half of row 1. From the Forms toolbar add
some buttons on top of this rectangle and attach macros to them. Now
select the rectangle and all the buttons (I use Select Objects mode
from the Draw toolbar and drag around them) and Group them, then Raise
the group to front. This is basically how our workbook works: you can
autofilter by clicking the table headers, and run various macros by
clicking the buttons.
However, after saving this in Excel 2007 (as a 97-2003 workbook) we
found that the buttons and background rectangle had become ungrouped,
and had fallen behind the autofilter with regards to clicking. They
looked the same, but if you tried to click on a button it would
activate the autofilter dropdown instead.

Iain

 

[Iain King]

Interesting. I fully expected to duplicate your issue because I know MS did
a lot of tinkering with shapes/drawing objects in Excel 2007. I created the
workbook in Excel 2003 per your instructions and opened and saved it in
Excel 2007 (albeit with a different name). However back in Excel 2003 it
seems to be the same as it was originally. I've attached my round-tripped
workbook. I can email it to you if you're not using a newsreader program
and cannot access it.

You can tell this workbook was saved in Excel 2007 because it has the
gratuitous adding of unasked for styles like "20%-Accent 1".

I'm using Excel 2007 SP1 (12.0.6300.5000)

--

| > Although someone who has encountered this exact problem may know what
you
| > mean perhaps you should provide the exact steps to duplicate the issue.
| > What kind of buttons? From what toolbar? Why are they grouped? What
does
| > "lowered behind an autofilter" mean?
| >
| > --
| >
| >| > |I have a workbook which is used on many computers on our network. One
| > | of these computers has just been upgraded to Excel 2007 (the rest use
| > | 2000). Excel 2007 is set to save in Excel 97-2003.xls format, but
| > | what we find is that when you save the workbook on the 2007 machine
| > | and then open it in Excel 2000 all the buttons used to control the
| > | sheet have been ungrouped and lowered behind the autofilter (and are
| > | thus unclickable). How do I stop it doing this?
| > |
| > | Iain
|
| Sorry, I could have been clearer. Quick guide to duplicating:
| Make a worksheet up with some tabular data, with the headers in row
| 1. Turn on the autofilter. Increase the height of row 1 so that
| there is space above the header text. From the Draw toolbar add a
| rectangle, covering the top half of row 1. From the Forms toolbar add
| some buttons on top of this rectangle and attach macros to them. Now
| select the rectangle and all the buttons (I use Select Objects mode
| from the Draw toolbar and drag around them) and Group them, then Raise
| the group to front. This is basically how our workbook works: you can
| autofilter by clicking the table headers, and run various macros by
| clicking the buttons.
| However, after saving this in Excel 2007 (as a 97-2003 workbook) we
| found that the buttons and background rectangle had become ungrouped,
| and had fallen behind the autofilter with regards to clicking. They
| looked the same, but if you tried to click on a button it would
| activate the autofilter dropdown instead.
|
| Iain

Grouped07.xls
39KViewDownload

Yeah, that works ok. Hmmm. I'll have to check if we have SP1
installed, that might fix it if we don't. Only other discrepancy I
can think of is that our workbook has a trigger on opening which
Protects all sheets with UserInterfaceOnly:=True and an accompanying
EnableAutofilter. Of course, it could be some other call in the
workbook, but I don't see an easy way to narrow that down (workbook is
very code heavy). Thanks for the response (and the effort you've gone
to). I'll experiment more and post back with what I find.

Iain