Excel Buttons Disappear

[Spin]

I have attached several macros to buttons displayed at the top of a
worksheet. When I click a button, the assigned macro runs but the
buttons disappear. I can't bring them back. Sometimes minimizing and
restoring the application brings them back... rarely. I have plenty of
system resources available and nothing in my code (it's very simple
stuff) would tell them to go away by hiding or deleting. Is there a
property that could ensure their visibility?
Thanks

 

[Alva Dean]

Hello Rob,
I have the problem with the button faces changing back to
the default color - the posted message above yous.

It looks like we have stumped the panel. I am reasonably
sure, as you seem to be, that we have missed a properties
box somewhere, but I don't know where either. I wrote the
program I am using on a win 2000 pro platform. I
transfered it to an xpHome system yesterday and got the
same results so I doubt it is operating system conflict.
The program performs fautlessly on both platform, the
command buttons colors just revert to the default color -
without changing the command box property setting.

 

[Greg Wilson]

A similar question was asked by David Pitt on March 2
without a solution. I doubt if this response will be of
any value. I'm only responding because it doesn't look
like anyone else is going to.

This is just a wild guess that at some point you had a
button or buttons on the worksheet that was/were
accidentally hidden the result of row or column deletion.
In such a case, the button's height/width will become zero
and this can lead to a bug. If this is correct, when you
protect the worksheet, the mouse pointer may transform
into the hand icon and clicking anywhere on the sheet may
execute the macro.

Suggested is that you run the following code:
Sub FindButtons()
Dim Shp As Shape, i As Integer
i = 0
For Each Shp In ActiveSheet.Shapes
If Left(Shp.Name, 6) = "Button" Then
i = i + 30
Shp.Left = Columns(2).Left
Shp.Top = i
Shp.Height = 20
Shp.Width = 75
End If
Next
End Sub

If this uncovers hidden buttons then delete them. I did
not replicated your situation using the above theory.
Sorry I couldn't be any more insightfull.

Regards,
Greg

 

[Tom Ogilvy]

You think there is a property that specifies to ignore the color setting and
revert to the default color or to change size without reason or to disappear
intermittently? I doubt it. Since these are visibility problems, they are
perhaps caused by the Accessibility settings or the screen driver has
problems with Excel. Size problems can be associated with font scaling.

Is the zoom setting under view at 100%. Being at other than 100% can cause
problems. The paucity of answers is an indication that few others are
having these problems.

 

[Guest]

Hi Tom,
No I do not think there is a dleiberate setting to make
buttons dissapear or change colors indeterminatily. The
Accessibility settings you metion is the sort of thing I
am looking for. I will research them as soon as I figure
out where they are. I have a current video driver
installed on both machines (win 2000 and xphome platforms)
and both have the same problem with the button face
background colors.

Thanks for the tip.

 

[Carlos]

In format control you have a properties tab, look in and see if the object
position is "Don't move or size with cells"

 

[Tom Ogilvy]

accessibility settings are in the control panel. Perhaps uncheck high
contrast if it is checked.

However, if you get the same problem on two separate machines, it would seem
it is more related to something with your file. I would try with a new file
and see if you can reproduce it.

 

[Guest]

I have spent 10 months getting this excel spread sheet to
the format it is currently in. It will take a while to
recreate the spread sheet on a blank Excel file, but give
me some time and I will let you know if it helps. I can
export and import the worksheet page code and the module1
code, but know of no way to recreate the spread sheet
itself excep to start with a blank sheet.
Thanks for the help. I will post again if I manage to
change anything.

 

[Tom Ogilvy]

Select all the sheets and do

Edit=>Move or copy sheet

Select new workbook and click the make a copy checkbox

Export your code modules and import them into the new workbook or copy and
paste in the VBE.

 

[spin]

Carlos,
I think you may be on to something. I will get an opportunity to try
later today and will post the results.
Thanks Greg, for your input. I will run your code as well if I
reproduce the problem. I will post the results.
I have had this happen in the past and just recreated the worksheets
from scratch... very frustrating. It seems to return after a while. I
do alot of copying and pasting, sorting and filtering, via macros
which may be of some consequence.
Frank