Move Command Button above header text (of a column)

Hello forum, I need to move several command buttons into position relative to some text in a cell. The text is generally in row one (header text of the column). The problem is, the starting location of the command button is random. Just a thought, if there is a way to measure the relative location of the command button in relationship to some header text (horz and vert) then these values (horz. and vert) could be used to plug into

Please Login or Register  to view this content.

.

I have prepared a workbook that shows the problem in detail.

Thanks for your time and effort in helping me to solve this problem.

Hi there,

Take a look at the attached version of your workbook and see if it does what you need.

I've created three new buttons using the Forms toolbar - the buttons on your worksheet are ActiveX controls created from the Control Toolbox toolbar. In general, Forms controls are less flexible than ActiveX controls, but they're more user-friendly.

The three new buttons are all of different sizes, but this is just so you can see how the VBA code works.

Essentially there are two functions, one to calculate the position of the left-hand edge of the button, and the other to calculate the position of the top edge. The arguments of the Left function are the button whose position is to be calculated and the column in which the button should be positioned. For the Top function the arguments are the button whose position is to be calculated and the row in which the button should be positioned.

In each case, the button will be positioned centrally in the cell at the intersection of the specified row and column.

The code makes no reference to your Home Position marker.

I hope this helps - please let me know how you get on.

Regards,

Greg M

Wow Greg, you nailed it. It does exactly what I need. The trick now is for me to understand it better. I get the basic idea, have run the code a few times. I don't typically use functions but they work like subroutines so no problem. So you set .Shapes("BtnA").Left to equal some value derived by the dbLeft function (and you feed this functuion a column and a shape. So once the function evaluates a move takes place, first on horz then on vert.

It's very nice but the syntax is a bit scary. I have never used dbLeft; rColumn; shpButton before. Is there a reference guide for VBA?

I am curious to learn a bit more about the Active-X buttons you used. My only comment is they do not simulate a button as well as a command button. In the past I used autoshapes and assigned macro's to them, they looked cool (rounded corners) but some users complained that they lacked the visual of a button depressing (can you believe that!). Anyway, if it's not too much trouble can we finish this off with a quick intro to Active-X buttons. And thanks for the super fine solution. I will adapt it to work with command buttons and I'll let you know how it goes.

I'll check this forum on Saturday and Sunday