macro button sending its location to the macro?

[ethan1701]

I want to write a macro that would delete a certain row of the spreadsheet, based upon a value in the field under the button. for example, range("U1017&quot contains the number "6". the maro I place on it should read this value, and remove row #6. This is important, because I want to have up to 70 of these buttons, each calling to the same macro, but telling it to deleta a diferent line, based on their contents. Is this even possible?!

-Ethan

 

[RobBroekhuis]

Not easily. If you use a button from the "forms" toolbar, it is not a selectable object, so the macro has no way of knowing which button triggered it. If it is one created using the "control toolbox", then it is its own object which needs its own event handler. Either way, you'll have to write 60 one-liner macros, each simply calling your main macro with the appropriate argument(s).
Rob

 

[ethan1701]

okay, so lets try a diferent approach:
can I put an href link in the excel, that will call the macro and send along the relevant arguments?

-Ethan

 

[Zathras]

It might help if you told us what you are wanting to accomplish. I.e. tell us the problem you are trying to solve, not the soulution you are trying to implement. There may be a better and/or easier solution.

For example you can program responses to a double-click or a right-click directly in a cell. U1017 contains a "6". Double click on it and do whatever you need to do.

 

[ethan1701]

I'm building a phone book like BD. sheet1 allows the user to add new entries, which are copied to the next free feild on sheet2.
I allowed 1000 free reos for the DB. as of row 1001 of sheet2, I have a shearch area, where the user can enter the details for which he's looking (currnetly limited to him entering the first name...), and displaying the results under that.
The buttons I asked about, are for the user to be able to delete entries from the existing DB.

Any help or referal to an existing application would be appreciated.

 

[Zathras]

Have you tried a simple worksheet using Data/Form from the menu? Crude but effective.

Otherwise, it sounds like you need to use a form to guide the user thru the process. Create a new sheet to display the results of a search.

Keep looking. I would be surprised if there is not something already out there for a phone book app.

HTH

 

[acron]

You can identify what button invoked a macro, provided it is a button from the Forms toolbar.

In the macro include something like :

    Dim ButtonName as String
    ButtonName = Application.Caller

If you are clever in the way you name the buttons, you can make things a lot - all from the one macro, eg. if you use a naming convention that includes the number of the row to delete you could just read the buttonname and extract the row value.

e.g if you need to delete row six with the click of a button, that button could be named Button0006, and the use

[tab]Rows(Right(Application.Caller, 4)).Delete

Just make sure all the numbers have the same number of characters, 4 in the above example.

A.C.

 

[RobBroekhuis]

I think this is the second time this forum has reminded me of the .caller property - I keep forgetting it refers to the form object that triggered the macro. Thanks Acron. This, by the way, does not appear in the VBA(xl2000) help topic for application.caller (although it is a logical extension of the info given there). Rob