Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

excel - vba button - find which was clicked

I have assigned macro to few buttons.

How can I find out inside macro which button was clicked?

I am doing as user form, where he can input peoples from family:

name1:
surname1:

name2:
surname2:
|add next member|

I wish button to appear always in last row of the last added person. For simplicity I think it is better to have like 100 empty forms in the sheet but all invisible at the begining.
Then when user clicks add next member I simply make next rows visible, and move button to next person. But to do that I need to know my current position.

Similar with deletion I would make rows invisible when remove button is clicked.

name1:
surname1:
[remove]

name2:
surname2:
[remove]

name3:
surname3:
|add next member|

I need to know which remove button was clicked.

EDIT: Found in web - what do you think, seems to be best /way

Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Select
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

I always write wrappers for each button that then call the macro in question.

Like so:

Public Sub StoreButton_Click()

  Call StoreTransValues(ActiveSheet)

End Sub

If you have only one button for any one page, you can just get the ActiveSheet property, and it will be the button on that page.


Edit:

Here's the code to get and use the name of the calling button:

Dim ButtonText As String

ButtonText = Application.Caller

ActiveSheet.Shapes(ButtonText).Delete

You would use the .Move method to move the button.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...