form buttons on sheet collapse on unhide - sometimes

[oldyork90]

I have a sheet with multiple form buttons. They are placed in a hidden area. When the user chooses to unhide this area (a number of rows) the buttons, like the rows, reappear... usually.

Every so often, the all the buttons collapse into a single black line. They wind up on top of each other. If you select this line, you can re-size each "button" (they have no vertical dimension), and move them back to where they belong. A lot of work.

Why are these buttons doing this? What's the fix?

Here are the format parameters.

Obj pos - move and size with cells
Intern margin - automatic
protection - locked, locked text
scale - lock aspect ratio

Thanks

Excel 2010

 

[Claus Busch]

Hi,

Am Wed, 15 Jan 2014 04:46:46 -0800 (PST) schrieb (e-mail address removed):

Why are these buttons doing this? What's the fix?

that only happens in xl2010. I guess it is a bug.
Write a macro to reset your buttons (here for buttons with topleftcell
D2, D5, D8):

Sub ResetButtons()
Dim shp As Shape
Dim myArr As Variant
Dim i As Long

myArr = Array("D2", "D5", "D8")
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 3) = "But" Then
shp.Top = Range(myArr(i)).Top
shp.Left = Range(myArr(i)).Left
i = i + 1
End If
Next
End Sub


Regards
Claus B.

 

[oldyork90]

Write a macro to reset your buttons (here for buttons with topleftcell

Thank you... again.

 

[GS]

IMO, any time you use buttons in hidden areas you can count on
unexpected behavior. I use cells that are formatted to look/feel/behave
like buttons so their position/appearance is reliably consistent when
the visibility of the area they reside is toggled.

This, of course, requires using the Selection_Change event to determine
which 'button' was clicked and run the appropriate procedure via a
Select Case construct!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[oldyork90]

IMO, any time you use buttons in hidden areas you can count on
unexpected behavior. I use cells that are formatted to look/feel/behave
like buttons so their position/appearance is reliably consistent when
the visibility of the area they reside is toggled.

I think I'll go your route next time. What a headache. I had to control visibility, placement and naming. Hiding form controls, at least buttons, is not something you want to do in Excel 2010. Never again. Thank you

http://www.excel-ticker.com/positioning-of-form-controls-using-vba-code-bug-in-excel-2010/

 

[GS]

IMO, any time you use buttons in hidden areas you can count on
unexpected behavior. I use cells that are formatted to
look/feel/behave like buttons so their position/appearance is
reliably consistent when the visibility of the area they reside
is toggled.

I think I'll go your route next time. What a headache. I had to
control visibility, placement and naming. Hiding form controls, at
least buttons, is not something you want to do in Excel 2010. Never
again. Thank you

http://www.excel-ticker.com/positioning-of-form-controls-using-vba-code-bug-in-excel-2010/

Wel.., this issue is not specific to v2010. It happens regularly in any
version if the controls are positioned in areas that hide/unhide
(however caused). I started using cells disguised as 'controls' when I
was working on a worksheet based wizard style form. Each pseudo-control
cell was given a local scope defined name that reflected its 'menu
caption', and their click was queried in the Selection_Change event of
the worksheet they were used on...

Select Case Target.Address
Case Range("btn1").Address: Call btn1Procedure
Case Range("btn2").Address: Call btn2Procedure
...
End Select 'Case Target.Address

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion