form buttons on sheet collapse on unhide - sometimes
 

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

form buttons on sheet collapse on unhide - sometimes
 

Hi,

Am Wed, 15 Jan 2014 04:46:46 -0800 (PST) schrieb :

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

form buttons on sheet collapse on unhide - sometimes
 

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


Thank you... again.

form buttons on sheet collapse on unhide - sometimes
 

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[s] 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



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

form buttons on sheet collapse on unhide - sometimes
 

On Wednesday, January 15, 2014 9:12:29 AM UTC-6, GS wrote:
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[s] 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/position...in-excel-2010/

form buttons on sheet collapse on unhide - sometimes
 

On Wednesday, January 15, 2014 9:12:29 AM UTC-6, GS wrote:
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[s] 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/position...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



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com