Microsoft Office Tutorials and References
In Depth Information
The Forms Toolbar
figurE 13-5
using application.Caller with Forms Controls
One of the cool things about Forms controls is that you can apply a single macro to all of them and
gain information about which control was clicked. Once you know which button was clicked, you
can take specific action relating to that button.
Expanding on the previous example, suppose you want to place a button on each row of data, so
that when you click a button, the cells will be cleared in columns C:F of the row where the button
resides. It’s obvious that the original macro will apply only to the first button in the Rent row, so
here are the steps to have one macro serve many controls:
1.
Modify the DeleteData macro as follows. For the button that was clicked, the cell holding
that button’s top-left corner is identified. The macro can now be a customization tool for
each individual button to which it is attached.
Sub DeleteData()
Dim myRow As Long
myRow = _
ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row
Range(Cells(myRow, 3), Cells(myRow, 6)).Clear
End Sub
2.
Recall that the original macro name is still attached to that button. Return to your worksheet
and right-click the button. Select Copy because you are copying the button and the macro to
which it is attached.
3.
Select cell B5 and press the Ctrl+V keys. Repeat that step for cells B6, B7, B8, and B9. Your
worksheet will resemble Figure 13-6.
Search JabSto ::




Custom Search