Using VBA buttons from Excel ...

  Heefie 17:20 19 Jan 2005
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 2005

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 2005

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 2005

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

  Heefie 00:45 20 Jan 2005

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

  VoG II 21:16 20 Jan 2005

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 2005

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.

Elsewhere on IDG sites

iPhone X review

How to find a font: Discover the name of a typeface with these apps

The best iPhone for 2017

Les meilleurs logiciels de montage vidéo gratuits (en 2017)