On Oct 8, 1:37*am, GS wrote:
After serious thinking RJQMAN wrote :



Could you give me an example as to how to do the before double click
event?

Not to discount the value of Don's suggestion (not mine), I advocate
that a context-sensitive menu approach would be better whether a custom
toolbar, menus, or cell popups. This is definitely more complex than
Don's suggestion, but would be a great benefit to the program given its
already complex structure.

I will, however, look at the ContestData buttons and see how they can
be replaced with the SheetBeforeDoubleClick event. I'll email you my
results tmo (Sat). I'll look at EnterScores too!



As to the problem, I have several old computers here, and the problem
occurs on those with less memory. *It seems unrelated to the zoom - I
thought perhaps it was the zoom because of the earlier excel problem a
few years back. *It occurs on my netbook and an old Sony Viao that I
inherited. *It is not consistent, but I can duplicate it.

Yeah, I figured that's where this issue manifests. Seems like you've
addressed the crashing issue only to reveal there's still more
contributing things to clean up yet.

The oldest machine I have here (circa 2002) has 1GB memory and so I
doubt it will manifest there unless I load resources with other stuff.

Problem is your project resource intensive (has tonnes of buttons and
tonnes of formatting). I've done dozens of projects with 4 to 5 times
as many sheets, all with tonnes of formatting, and your file is more
than double the file size. Most of that is due to the controls and
numerous images. BTW, where can I get an original of the wizard image?



Feel free to contact me directly - you have my e-
mail. *I feel a bit guilty tying up the group with my one problem.

Actually, you're better off to continue here since there's others here
(no doubt there) who can shed light on lots more stuff than I'm able
to. I do agree that the other things we're doing should happen outside
the forums.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi

Well, I have rewritten the code for the contest data page and replace
about 90% of the buttons with color-filled cells. I did not
understand the 'before click' thing, but when researching it, I found
the SelectionChange(ByFVal Target as Range) command, and it seemed to
work just fine, so I used it. I rewrote the contest data page - using
the following code for the cells that are masquerading as buttons in
the worksheet module and it seems to be working fine. I can hide or
display the line without worrying about the buttons going off the page
on their own. The following is typical for one button - as you know,
there are perhaps 100 buttons on the page - the code is in the
worksheet module.

Since one sub often calls another, I deposit the cursor in cell D2 -
then added the exit sub statement to prevent the code from re-
triggering a second series of actions;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
' === ESCAPE IF 2ND SUBROUTINE WOULD CANCEL 1ST ===
'
If Target.Address = "$D$2" Then Exit Sub ' To prevent looping

' I drew buttons using perhaps four adjacent cells with one border
around them, and filled them with a color and a descriptive text so
they looked like Command Buttons. I found I could not merge them and
still have the code work, so I just did the following, which seemed to
work fine.

If Target.Address = "$BD$75" Or Target.Address = "$BE$75" Or
Target.Address = "$BF$75" Or Target.Address = "$BG$75" Then Call
FMBC02_OpenWizard_02

The worksheet module contains a similar statement for each group of
cells - perhaps 50 to 75 such statements in the same sub. I left some
of the buttons as Command Buttons, because I grey-out the text and I
did not want to cause some other kind of problem that I did not
understand if I tried to grey-out the text in the cells that were
triggering the macro(s). So in a regular module, I repaint and
relocate each Command Button every time I open the section of the
worksheet that contains the button or buttons (an example follows),
using the code Jim Rech was kind enough to supply above. This brings
the Command Buttons(s) back where they belong if they jumped to the
edge of the sheet, as they were prone to do before. The following
code is word-for-word from Jim's recommendation, with my button number
and subroutine call substituted;

With Worksheets("Contest Data").Buttons("Button 71")
.Top = Worksheets("Contest Data").Range("BI153").Top
.Left = Worksheets("Contest Data").Range("BI153").Left
.Width = 35
.Height = 18
End With

The code Jim provided works very well and locates the Command Buttons
at the left top edge of the cell. I played with the code to see if I
could locate the button perhaps indented from the edge, or located off
the right or bottom edge, but could not figure out a way to do it.
Since it was not critical, and my time was limited, I just went with
it as Jim provided. I had to change the Protect command for this
sheet by adding a statement allowing the user to access both protected
and unprotected cells in order for the code to work - so I did not
want to use this system on all the pages, as on at least one of the
pages (Enter Scores, specifically), it is not helpful for the user to
be able to access protected cells - it is far better for them to just
access the cells where they enter the scores. Otherwise they would be
constantly getting the 'protected cell' message, so I stayed with the
Command Buttons on that page and did not change over to the cell
system. It also seems to be working OK so far.

In response to your question, about the Wizard - I used a royalty-free
clip-art for the Wizard and then using the really great free shareware
from PaintNet, pasted in the trumpet from another clip art. This is
the only 'imported' image used. I use circles as buttons for the HELP
files, and now I am using cells as buttons on many sheets, and command
buttons as buttons on others.

Just as you indicate, the program does appear to 'eat up' a lot of
computing power. Some users have found that they cannot print the
results because the print won't format - even manually. I think this
is due to lack of resources in their computer, but that is just my
guess. Each time they have been able to make it all work, though, by
closing the program and re-opening it. I am guessing that is due to
the program using all the available resources, but I am not certain at
all, and I do not have the slightest idea how to reduce the demand on
resources, but I wish I could do that so the program would be more
'bullet-proof.' Today is a Saturday, and the program is being used
all around the states of Florida and Louisiana. I only received one
call, and that was related to the printing problem mentioned, and the
problem resolved when she closed Excel and re-opened it.

Garry, I do all of this for no charge for a non-profit organization
and for the kids, and I deeply appreciate the help. Thank you a
thousand times over.