Make a Cell Act like a Button

[Ken Loomis]

I am building a worksheet that builds four database queries that will be
copied and pasted into the DBMS for now. About 80% of each of these queries
never changes, so I use 4 sheets to hold those static elements. The first
sheet of the worksheet contains a zip code and a varying number of streets.
That number of streets determines how long each query string will be. With
10 streets, the queries are 35, 44, 56 and 107 lines long.

Each query has a title cell as the first line that is just there to label
the query. And there are several empty rows separating each query.
Everything in the query is, including the title, in column A.

I want to make a button that will select and copy the query. I have recorded
a macro that will work just fine. I just need to know what to do to turn a
cell into a button, since the macro should do the rest.

What event do I need to trap? And what object to I put that event handler
in?

Also, once I figure that out, I need to know how to add that event handler
to the sheet as I build the queries.

My thinking is:

Add the query label
move over three cells and Add the button
move back three cells and down one row and Build the query

Then do the same thing again for the next three queries.

I know I got kind of wordy here, but I hope someone can point me in the
right direction.

Thanks,
Ken Loomis

 

[Nacho Nachev]

Hello Ken,

Excel buttons are part of the Shapes collection which belongs to the
worksheet as a separate layer but not as range (cell) property. Here's a
prototype of a meta code that is supposed to do exactly what you need.


dim shp as Shape
shp = ws.Shapes.AddFormControl(XlFormControl.xlButtonControl,
rng.Left,
rng.Top,
rng.Width,
rng.Height)


shp.OnAction = "MyMacroSub"
shp.TextFrame ... here set the button title
shp.Select

Note that rng represent the cell boundaries over which the button will
appear and ws is your button worksheet.

The details related to your application you will be able to tackle with
yourself.

HTH,
Nacho

 

[Harald Staff]

Hi Ken

You can use almost anything but a cell for this.
Display the "Drawing" toolbar (View > Toolbars menu). Put one object from it
onto your sheet, say an Autoshape or a Textbox.
Rightclick it, choose "Assign macro" and assign your macro.

Now your mousepointer turns to a hand cursor over this object, and clicking
it rund the macro. Make the object look good (text, color, ... )

You can also use a button from the Form toolbar if you want something more
button-like. Same procedure as above.

HTH. best wishes Harald

 

[Ken Loomis]

Betweeen your two replies, I got it to work and learned a lot in the
process.

Thanks,
Ken Loomis

 

[ross]

you can use buttons from the controls tool box (left click on a tool
bar to bring up a list and tickm it),

Draw the button on the sheet, and then dobble click it,

between the code line wite the name of the macro you want to run

ie.

sub Command1_click
My_Macro
end sub

you can also draw a shap (from the drawing tool bar) on to a sheet and
asign a macro to this, left click and pick the "Assign macro" option
from the short cut menu.
here a tip, if you are doing this then you can protect the sheet, and
you will get a nice looking hand icon over the picture!

good luck
ross