Using VBA buttons from Excel ...

  Heefie 17:20 19 Jan 05
Locked

Possibly a silly question, I'm not sure ...

If I create, on an Excel sheet, a Button that executes a VBA macro, it's basically a free floating button that I can drag anywhere ...

Is it possible to force / link this Button to a Cell ? I suppose I am asking if you can make a Cell into a Button !!!

This is not something that makes or breaks my project, but it would be damn helpful in tidying up making things look neat !!!

  VoG II 17:27 19 Jan 05

Right click, Format Control, Properties, Move and size with cells may do what you want.

My approach to this is to use Window/Freeze panes to create an "unmovable", always visible, area at the top of the sheet and to place the buttons there.

  Heefie 00:02 20 Jan 05

If I have Buttons created using the "Control Toolbox", yes, I can open Properties and specify TOP, WIDTH, HEIGHT, LEFT and loads more ... great for lining up Buttons in a single sheet ... in fact, I can select ALL my buttons for Copying in this style ...

However, when I have Buttons created using "Forms", which allow me to assign Macros to them & I want these on multiple sheets IN THE SAME POSITION ... this is where it seems to all fall flat, the Format options are totally different and although they allow you to specify the size of the button, there doesn't appear to be a way to align it on the sheet, so you have to fiddle for ages ... once you get it acceptable on 1 sheet you don't seem to be able to select these buttons for copy & paste apart from individually, and then you've got to start all the fiddly alignment crap again !!! Am I missing something obvious here ?!?!?

  The PC Doctor 00:29 20 Jan 05

Why not use the Activate routine to set the position of your button?

  Heefie 00:45 20 Jan 05

I'm a novice at all this VBA stuff ... could you please explain ... fully ?!?!?!?

  VoG II 21:16 20 Jan 05

Why not record a macro when positioning the first button, then run the macro on each sheet that you want to add a button to.

Sub Macro1()

ActiveSheet.Buttons.Add(96, 38.25, 48, 12.75).Select

End Sub

You can then right click, Assign Macro.

  Heefie 22:40 20 Jan 05

By using the ALT key, I can align buttons with the Cells & by using the Drawing Toolbox I was able to select a number of buttons, so once I had got the layout & alignment correct on one workaheet, I could just copy & paste it into all the others ...

Thanks for your help and suggestions though, they all help me to learn, albeit slowly !!!

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

The Pantone Colour of the Year 2017 is Green

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…