use button name/caption in macro

[ajm]

hi all, i can't seem to find anything on passing the name or caption of a form control button into a macro. I am setting up a divisional report that uses the same base data. Users are to select the button with their division name on it, and this will then export the filtered contents to a new workbook. Is there a better way of doing this?

 

[dmt32]

hi all, i can't seem to find anything on passing the name or caption of a form control button into a macro. I am setting up a divisional report that uses the same base data. Users are to select the button with their division name on it, and this will then export the filtered contents to a new workbook. Is there a better way of doing this?

Hi,
to do what you want try

Sub Button1_Click()
    MyMacro Sheet1.Buttons(1).Caption
End Sub




Sub MyMacro(ByVal Caption As String)
    MsgBox Caption
End Sub

If there are not too many users then your button approach probably ok but alternative approaches would be to populate either a combobox or listbox with users names or if your users open the workbook on a corporate network then maybe use their network username to run the reports automatically.

Dave