Getting Name Box data at run time.

[Dave]

I have an Excel 2000 problem that I've tried for 2 days to get the
answer to and can't find any information on it. (maybe there is no
answer... but here goes)

I have an Excel 2000 Spreadsheet with about 500 commandbuttons on it.
I wrote a macro, in Visual Basic 6.0 that comes with Excel 2000, that
will work with all 500 buttons at run time, but I just need one thing:

I need to be able to assign the text in the Name Box, (That little
white box on a spreadsheet just above the A1 Cell), to a string called
CurrentButton.

For instance. If I click on the 6'th button in the worksheet, and in
the Name Box it says 'Button 6', I need to have CurrentButton =
"Button 6".

That's it. It's either something very obvious that I've overlooked,
or it can't be done with Excel 2000/Visual Basic 6.0.

Thank you in advance for anyone that puts any effort into this problem
for me.

 

[Bob Phillips]

Hi Dave,

If you have right-clicked it, it will be selected, and so you can simply use

CurrentButton = Selection.Name

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Greg Wilson]

Try:

Dim CurrentButton As String
CurrentButton = Application.Caller
MsgBox CurrentButton

Regards,
Greg

 

[Dave]

If you have right-clicked it, it will be selected, and so you can simply use

CurrentButton = Selection.Name

Thanks for the quick response, Bob!

Unfortunately, a right-click didn't execute my macro. So I have 3
options:

1. Find a way for a right-click to execute my macro. (is that
possible? I've never seen, in an Excel spreadsheet, an Object that
worked when you right-clicked on it.) I'm going to do some help
searches to see if I can find a way to make this option work.

2. (as asked in my original post) Find a way that when I click a
button, the name in the Name Box is stored in a String called
CurrentButton.

3. If there is another unique identification besides the name in the
Name Box for 'Button x', to store that unique ID in my CurrentButton
string, after I've left-clicked the button. (is there another ID for
an object besides the name listed in the Name Box?)

Thanks again in advance for anyone's help to my problem.

 

[Dave]

Try:

Dim CurrentButton As String
CurrentButton = Application.Caller
MsgBox CurrentButton

Regards,
Greg

Thank you Greg!

That worked EXACTLY as I needed it to!

(Please ignore my previous post about having 3 options. I posted it
before Greg's reponse was posted.)