URGENT help needed for a macro to place and size command button

  • Thread starter Thread starter RJQMAN
  • Start date Start date
R

RJQMAN

I am struggling with excel command buttons that move around my
worksheet when rows and columns are hidden. I need for them to move
with the cell and resize, as I need them to be hidden at certain
times. My program is used to keep scores in the fall season and I am
1/3 through the season.

Is there a way that I can write a macro to place (or actually in this
case replace) the rogue command buttons back in their intended
specific location and reset the size, if necessary to its original
size? This would solve my problem quickly, but I do not know if it is
possible to write such a macro.

I have experimented with the macro writer, but it seems to want to
relocate by scaling from where the button is. Since I do not know
where the darn button is, that does not work for me.

As an example, I would need a macro to do the following...

1) Locate the existing command button (Button 1) in cell E26 so the
left edge and top of the button are at the cell left edge and top of
the cell (the button has moved to the top edge of the page)
2) Make the command button (Button 1) 1" wide and .25" high (not sure
if I need to do this or not, because I am not sure if the command
button stayed the same size when it moved - I will have to trial and
error to find out when I figure out how to move the button).

I would really appreciate some help. I am self-taught and I am
partially blind. My users are unskilled moms using the program every
week, and on some oftheir computers the buttons move, and on others
they do not. (talk about the blind leading the blind :-) - just
kidding).

I suspect it is an available memory issue, but am unsure, as older
computers and netbooks seem to have the problem...

I need a quick fix, and could place a similar macro in front of the
call for each button (There are close to 100 buttons on the sheet -
only a couple are supposed to appear at a time).

Is this possible? Thank you.
 
Assuming you are using an activex command button:

With Worksheets("Sheet1").OLEObjects("Button1") ''Change sheet/btn names
as needed
.Top = Worksheets("Sheet1").Range("E26").Top
.Left = Worksheets("Sheet1").Range("E26").Left
.Width = 72
.Height = 18
End With

If it's a actually a Forms toolbar button change the first line to:

With Worksheets("Sheet1").Buttons("Button 1")

It would be better to assign a range name to E26 and use that in the code so
you could e.g. insert a row if needed later and not have to revise your
code.
 
Robert, have you considered Don's suggestion to get rid of the buttons
and just have users double-click a cell. Test for this in the
BeforeDoubleClick event and run a macro based on cell address using
Select Case.

FYI, my copy of your workbook doesn't behave the way you describe so is
this consistent with all users or just some? It's hard to remedy a
situation that I can't duplicate! I tried all kinds of zooms and every
button is always exactly where it should be.
 
Robert, have you considered Don's suggestion to get rid of the buttons
and just have users double-click a cell. Test for this in the
BeforeDoubleClick event and run a macro based on cell address using
Select Case.

FYI, my copy of your workbook doesn't behave the way you describe so is
this consistent with all users or just some? It's hard to remedy a
situation that I can't duplicate! I tried all kinds of zooms and every
button is always exactly where it should be.

--
Garry

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

Hello Garry...

I do not know how to do the 'before double click event' on a cell. As
I say I am self taught, which means my teacher did not know any more
that I did. I am recovering from eye surgery today, but I am able to
see a little better (except for dozens of 'floaters' so perhaps this
time I will be able to read your solution without someone to help
me.

It would be another and probably better solution. I have, today, gone
over every one of the hundreds of buttons and locked them in by
positioning them every time that portion of the sheet or sheets is
viewed. After doing this, the problem appeared at least at first
glance pretty well solved. But not 100%. I was only able to
duplicate the problem when I was playing with the worksheet, unhiding
the calculation columns on the enter scores page. And when I rehid
the columns and went back to opening the sections from the buttons, it
all worked again. Whew.

Could you give me an example as to how to do the before double click
event? I just do not have the background that I wish I had - I can
figure out 95% of the things by studying the recommendations on this
group and reading John Walkenbach's book for dummies, which has been
very helpful, but it is mostly trial and error. Garry, I am not
secure with the fix I have implemented, and the fix you suggest sounds
a great deal more secure.

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.

The problem does not occur on my new computers. It did occur this
weekend on one school-owned laptop in Inverness Florida. They had
several instances of Excel running as well as some other incidental
programs. They rebooted and only ran the scoring program and had no
further problem. Feel free to contact me directly - you have my e-
mail. I feel a bit guilty tying up the group with my one problem.
 
After serious thinking RJQMAN wrote :
Could you give me an example as to how to do the before double click
event?
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.
 
After serious thinking RJQMAN wrote :




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!




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?




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
Click to expand...

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.
 
Robert,
It's good to know your users haven't had much problem to date. I can
appreciate all the extra work you did to eliminate the buttons. Jim's
code is very helpful for relocating any that might get moved. Seems,
though, that for the remaining buttons it would require a lot of
coding. Perhaps a loop would be more efficient and easier to maintain.
I'm thinking you could store the button names and their cell addresses
in a string constant as delimited value pairs...

Sub ResetButtons()
Dim n As Long, v As Variant, iPos As Integer
Const sButtonData As String = _
"Button 71:BI153,Button 72:BI154,Button 73:BI155" 'edit to suit

v = Split(sButtonData, ",")
For n = LBound(v) To UBound(v)
iPos = InStr(1, v, ":")
With Sheets("Contest Data").Buttons(Mid$(v, 1, iPos - 1))
.Top = Sheets("Contest Data").Range(Mid$(v, iPos + 1)).Top
.Left = Sheets("Contest Data").Range(Mid$(v, iPos + 1)).Left
.Width = 35: .Height = 18
End With
Next 'n
End Sub

I'm making progress on the userform version of the contest setup
wizard. Can you email me the image file of the wizard?
 
GS expressed precisely :
Oops! I made a mistake. Revise as follows...

Sub ResetButtons()
Dim n As Long, v As Variant, iPos As Integer
Const sButtonData As String = _
"Button 71:BI153,Button 72:BI154,Button 73:BI155" 'edit to suit

v = Split(sButtonData, ",")
For n = LBound(v) To UBound(v)
Click to expand...
iPos = InStr(1, v(n), ":")
With Sheets("Contest Data").Buttons(Mid$(v(n), 1, iPos - 1))
.Top = Sheets("Contest Data").Range(Mid$(v(n), iPos + 1)).Top
.Left = Sheets("Contest Data").Range(Mid$(v(n), iPos + 1)).Left
.Width = 35: .Height = 18
End With
Next 'n
End Sub
Click to expand...

Sorry about that!
 
Back
Top