Macro-linked buttons that know their place!

[SaintAugustine]

Hi all,

I made a mock-up spreadsheet so people here could track some data...strictly internal.

The way it's set up is, names on the top, categories on the left:

....|.Dog.|.Cat.|Gecko|
Type|Mamml|Mamml|Lizrd|
Fur |..Y..|..Y..|..N..|
Legs|..4..|..4..|..4..|
Beak|..N..|..N..|..N..|
....|..*..|..*..|..*..|..*..|

The user adds the "animal type" on the top (so maybe the next entry he needs would be Horse), and then pushes the button on the bottom (the *). The button (again, in theory), copies a template tab based on the Type category and renames it, based on the Type (so, in our example, it would make a copy of the Type template tab and call it "Type - Horse". The user would then input more detailed information about the horse there).

My problem is that in this theoretical design I was relying on the buttons "knowing" which column they were in, so that they would be able to jump up 4 rows and run an if-then based on the Type. But a friend pointed out to me yesterday that buttons are free-floating.

Can anyone think of a way around this?

Could I do something like ... have the macro just take the right-most type, since that will always be the newest entry?

As always, thanks for your help!

 

[rmikesmith]

SaintAugustine,

Why not have a single command button that runs a single procedure (macro), which determines what information to add and actions to carry out based on the column containing the active cell; e.g.

ActiveCell.Column

? Knowing which column is being operated on by the user will allow your code to access the value assigned to animal Type, etc.

HTH
M. Smith

 

[SaintAugustine]

Ah, nice solution! Since the user has to type in some of the info anyway! Tnx