CommandButton objects move in worksheet

[peter]

Hello,

Maybe someone has a hint for me whats wrong here :-)
I have written some VBA code that allows to expand/collapse a previously
grouped set of rows dependent of the state of a checkbox object. Works fine
sofar. Then I have placed a CommandButton within that group, used the
TopLeftCell Property to decide if the button is within the group range and if
yes, hide the CommandButton when collapsing the group with the .visible
property. Still works fine. The problem occurs if I save and close the
worksheet while the respective group is collapsed (showDetail property =
false). If I do this, after re-opening the workbook the CommandButton has
changed its position, obviously messing up the worksheet layout completely.
I have to confess I am a VBA newbie, maybe I have a fundamental conceptual
error here- or is this simply a strange excel behavior?
Any help or hint would be much appreciated!!

Thanks
peter

[Kevin B]

Open the Control Toolbox toolbar, click on the design button, right-click on
your command button and select "Format Control". Click on the properties tab
in the dialog box and click the "Don't move or size with cells" option button.

--
Kevin Backmann


"peter" wrote:

> Hello,
>
> Maybe someone has a hint for me whats wrong here :-)
> I have written some VBA code that allows to expand/collapse a previously
> grouped set of rows dependent of the state of a checkbox object. Works fine
> sofar. Then I have placed a CommandButton within that group, used the
> TopLeftCell Property to decide if the button is within the group range and if
> yes, hide the CommandButton when collapsing the group with the .visible
> property. Still works fine. The problem occurs if I save and close the
> worksheet while the respective group is collapsed (showDetail property =
> false). If I do this, after re-opening the workbook the CommandButton has
> changed its position, obviously messing up the worksheet layout completely.
> I have to confess I am a VBA newbie, maybe I have a fundamental conceptual
> error here- or is this simply a strange excel behavior?
> Any help or hint would be much appreciated!!
>
> Thanks
> peter
>

[peter]

Hi Kevin,

Thanks for the answer, but unfortunately it doesn´t solve the issue...

peter

"Kevin B" wrote:

> Open the Control Toolbox toolbar, click on the design button, right-click on
> your command button and select "Format Control". Click on the properties tab
> in the dialog box and click the "Don't move or size with cells" option button.
>
> --
> Kevin Backmann
>
>
> "peter" wrote:
>
> > Hello,
> >
> > Maybe someone has a hint for me whats wrong here :-)
> > I have written some VBA code that allows to expand/collapse a previously
> > grouped set of rows dependent of the state of a checkbox object. Works fine
> > sofar. Then I have placed a CommandButton within that group, used the
> > TopLeftCell Property to decide if the button is within the group range and if
> > yes, hide the CommandButton when collapsing the group with the .visible
> > property. Still works fine. The problem occurs if I save and close the
> > worksheet while the respective group is collapsed (showDetail property =
> > false). If I do this, after re-opening the workbook the CommandButton has
> > changed its position, obviously messing up the worksheet layout completely.
> > I have to confess I am a VBA newbie, maybe I have a fundamental conceptual
> > error here- or is this simply a strange excel behavior?
> > Any help or hint would be much appreciated!!
> >
> > Thanks
> > peter
> >

[peter]

Hello again,

Based on Kevins input, and looking at another thread (which however related
to excel 2002, and I run 2003) I have tried the option "Move and size with
cells". Although this is not the behavior I really want, it apparently works
around the problem.
What I don´t understand that the problem seems to have survived the version
change to 2003. Has anyone possibly found a real fix for the issue?

regards

peter

"peter" wrote:

> Hi Kevin,
>
> Thanks for the answer, but unfortunately it doesn´t solve the issue...
>
> peter
>
> "Kevin B" wrote:
>
> > Open the Control Toolbox toolbar, click on the design button, right-click on
> > your command button and select "Format Control". Click on the properties tab
> > in the dialog box and click the "Don't move or size with cells" option button.
> >
> > --
> > Kevin Backmann
> >
> >
> > "peter" wrote:
> >
> > > Hello,
> > >
> > > Maybe someone has a hint for me whats wrong here :-)
> > > I have written some VBA code that allows to expand/collapse a previously
> > > grouped set of rows dependent of the state of a checkbox object. Works fine
> > > sofar. Then I have placed a CommandButton within that group, used the
> > > TopLeftCell Property to decide if the button is within the group range and if
> > > yes, hide the CommandButton when collapsing the group with the .visible
> > > property. Still works fine. The problem occurs if I save and close the
> > > worksheet while the respective group is collapsed (showDetail property =
> > > false). If I do this, after re-opening the workbook the CommandButton has
> > > changed its position, obviously messing up the worksheet layout completely.
> > > I have to confess I am a VBA newbie, maybe I have a fundamental conceptual
> > > error here- or is this simply a strange excel behavior?
> > > Any help or hint would be much appreciated!!
> > >
> > > Thanks
> > > peter
> > >