Placing a button programmatically on a worksheet

[highlystrung]

Hi,

I have an expanding worksheet and would like to move a button so that it is always near the bottom of the worksheet data. When the button is pressed it reworks the worksheet and adds in some additional rows (up to 10 max). I'd like it so that the procedure also moves the button down keeping it in line with approximately the last row of data but not sure how to do this with vba... Any help / indicators greatly appreciated, thanks, Neil

[buran]

Hi,
It depends if your code is in a normal module or in a sheet module. Also it depends if it is ActiveX control or Form Control.

For ActiveX control in a sheet module you can use any of the following to reference the control named CommandButton1 and it's the only on the sheet (i.e. index is 1):

Please Login or Register  to view this content.

If the code is in a normal module, replace Me. with reference to the sheet e.g. Worksheet(1). In this case you are not able to use Worksheet(1).CommandButton1. Use OLEobjects ot Shapes

If it is a Form control named "Button 1" and the code is in a sheet module:

Please Login or Register  to view this content.

If the code is in a normal module, replace Me. with reference to the sheet e.g. Worksheet(1)


Use Top and Left property to align the control with the desired cell e.g.

Please Login or Register  to view this content.

it will align the top left corner of the button with the top left corner of cell C5

[highlystrung]

Hi buran , thanks for the reply. I'm using ActiveX buttons on worksheets referencing code in normal modules. I normally use the code

Please Login or Register  to view this content.

to find the last row so I assume I can use the reference to this to set the range as in your code above...

Please Login or Register  to view this content.

? thanks, Neil

[buran]

yep, it should work

Please Login or Register  to view this content.

[highlystrung]

Thanks, I'll give it a go & see how I fare! Many thanks for your help, Neil