Identify what command button was clicked 1

[LeanneGodney]

Hi there,

I'm pulling my hair out regarding my latest problem. I am creating a spreadsheet through Access VBA that formats the spreadsheet correctly, and then places command buttons on each cell in a particular column.

The button (when clicked) needs to launch my userform, let them select something off it, and when the form closes it I want the cell UNDERNEATH the original command button to have a value placed on it.

Trouble is this:

I'm creating MULTIPLE buttons (the amount will change each time). I don't want to put different code behind each button cos there could be hundreds of them. I want ONE bit of code to run that will do teh following:

1. Identify WHICH command button was clicked so I can use the BottomRightCell property to identify where to place the resulting value.

I know the activesheet.commandbutton1.topleftcell command, but commandbutton1 changes! I'd like to use a variable here, but can't for the life of me work out how to grab the NAME of the commandbutton that the user clicks.

Can anyone please help me??

Thanks...

Leanne

 

[RamziSaab]

look at control arrays: check out htis site it might help

http://www.vbusers.com/downloads/download.asp#item13

 

[taupirho]

VBA doesn't have control arrays as far as I'm aware. Try this code out for size in your button(s) click code:-

Dim ctlCurrentControl As Control
Dim strControlName As String
Set ctlCurrentControl = Screen.ActiveControl
strControlName = ctlCurrentControl.Name

MsgBox strControlName

 

[DrBowes]

Try

application.caller

 

[combo]

Hi,
you can use excel forms buttons for this task, name them and assign common macro. Next use caller to identify clicked button.
(Note that the old excel button is hidden in excel object model. To use it with intellisense, open object browser in VBA editor, right-click 'members' window and tick 'Show hidden members'. Now all available peoperties and methods will be seen in code module, the hidden ones will be dimmed.)

The sample code:

Sub Makro1()
Dim cbt As Excel.Button
Set cbt = ActiveSheet.Buttons.Add(250, 100, 100, 30)
With cbt
    .Name = "CustomName"
    .OnAction = "CommonMacro"
End With
End Sub

Sub CommonMacro()
MsgBox Application.Caller
End Sub

combo