Button location problem

[Greg Snidow]

Greetings all. Need to be able to programatically set a command button's
position. On one sheet there is a button to run a macro that needs to always
be just above a certain row. The problem is that the certain row varies,
depending on the macro that created the sheet. I tried recording a macro to
put it where I want it, but in only increments it from its current position.
Is there a way to do this? Thank you.

Greg

 

[GB]

What does the code look like that "moves" the button? (Assuming that's what
you recorded). With that, you could do a number of things:

1) Locate the row that needs to proceed after your button, determine the
difference between it's current position and the expected final position, and
use that calculation to duplicate the recorded move.
2) Create the button at the location needed (Try recording the full process
of creating the button at a location). Again would need to find the row and
then duplicate the creation attribute. (I think this creation process would
only be necessary if you are able to determine that the button has been wiped
from the sheet.)
3) Create "blank" worksheets (programatically very hidden) that mimic the
end product of each of the macros that create the worksheet such that the you
can copy the worksheet to a new worksheet, make it visible and then perform
the operation consistent with the expected macro. (Of course this would be a
very rigid fix to the problem because it would 1) require an additional very
hidden sheet for each type of operation, and 2) it assumes that the button
can only be located at the position(s) identified by each worksheet.

But by posting the code generated by the move, maybe some more help can be
given beyond my "discussion".

 

[Rick Rothstein]

You don't give us much detail to go on. Assuming you know what row is your
"certain" row is, this code will move the CommandButton (from the Control
Toolbox Toolbar, right?) so that it rest on top of the row...

With Worksheets("Sheet3").OLEObjects("CommandButton1")
.Top = Range("A10").Top - .Height
End With

In the code above, I have assumed the button is named CommandButton1, that
it is located on Sheet3 and that the "certain row" is Row 10... change these
to match your actual conditions.

 

[Greg Snidow]

This is what I recorded to move the button up 1 row...

ActiveSheet.Shapes("Button 5").Select
Selection.ShapeRange.IncrementTop -20.25

If I know the row above which the button needs to go, how can I determine
the position of that row?

 

[Rick Rothstein]

Okay, so it is not an ActiveX control (by the way, ActiveX buttons are
called CommandButtons, the ones from the Forms toolbar are just called
Buttons)... try this variation of the code I posted earlier then...

With Worksheets("Sheet3").Shapes("Button 2")
.Top = Rows(16).Top - .Height
End With

where I am explicitly specifying the worksheet (Sheet3 in my example above,
change it as needed) rather than using ActiveSheet; that way, you don't have
to make the worksheet the button is on active to run the code. Also, I have
assumed your "certain row" is Row 10 (again, change this as needed).