Selecting Button Just Activated (help!)

[jpizzle]

Hey everyone! I was wondering if there is code that will make excel
select a button that has just been activated. So, in other words, when
you click the button to activate it, part of the code in the macro will
select the button that was just activated. I hope thats clear to
everyone! I appreciate your help.

 

[mangesh_yadav]

I guess you are looking for something like:
CommandButton1.Select

Mangesh

 

[jpizzle]

well, I was looking to be able to select the button that was just
clicked without referencing its name.

 

[mangesh_yadav]

Well, I don;t know if thats possible. But when you click on a particular
button, you know beforehand what button it is. Maybe if you give your
real scenarion, someone could help.

Mangesh

 

[jpizzle]

I am making a chart that allows the user to input a list of features,
and for each of those features, any number of options are allowed. So,
the chart should begin two columns, and just one row with column
headings Features, and Options. The idea is that the user first inputs
the feature name on the left then adds options on the right. Under the
Features collumn is a button, "Add New Feature", which allows the user
to add an additional feature to the bottom of the chart. For each
feature there is a button, "Add Option", in the Options column that
inserts a row above the button to add another option. Each time the
"Add New Feature" button is clicked, it inserts two rows above the
"Add New Feature" button and also creates an "Add Option" button under
the Options column.

The problem I have is that I don't know to get excel to select the "Add
Option" button after it has been clicked so that the cell the button is
located over may be selected using, Selection.TopLeftCell.Select
After that cell is selected, the macro would add rows above the cell to
add more options. My only problem is selecting that button after being
clicked. Hope that clears it up, but i know its kind of confusing.

 

[jpizzle]

But since the program will create multiple "Add Option" buttons in
different locations, I don't think I can reference it by its name, as
each button is unique only in its location. So the macro for each "Add
Option" button must:

1. Select the button that was just activated
2. Select the cell corresponding to the button
(Selection.TopLeftCell.Select)
3. Insert a row above that cell selection

I know how to do 2 and 3, its 1 that is giving me the problems

 

[mangesh_yadav]

Maybe you could name the button with the row number it is in. And then
call the button by this name... just a suggestion.

By the way, what buttons are you using, from the forms menu or the
control toolbox. Also can you post your code which creates the
buttons.

Mangesh

 

[Tom Ogilvy]

Assuming buttons from the Forms Menu (since buttons from the control toolbox
toolbar would only trigger their respective events and you would know what
button).

Sub Btn_Click()
Dim sName as String
Dim rng as Range
sName = Application.Caller
set rng = activesheet.Buttons(sName).TopLeftCell
' rest of your code using rng

End sub

 

[jpizzle]

Thank you Tom! I bow down to your superior VBA knowledge. You hav
officially allowed me to have a productive da