Pass Worksheet Button Caption or Identifier to Sub

[RyanH]

I have a worksheet with several buttons on it. When each button is clicked
the associated custom userform is shown for the user to enter data. Is there
a way to pass the buttons caption or something to the procedure below when
the button is clicked so that the button can be identified thus calling the
appropriate userform?

Can this be done with a Forms Button and/or ActiveX Button? Thanks!

' in a standard module
Sub ShowUserforms(btnName As String)

On Error GoTo ErrorHandler

Select Case btnName
Case "Plastic Faces": frmPF.Show
Case "Aluminum Faces": frmAF.Show
Case "Flex Faces & Other Graphics": frmDigitalPrints.Show
Case "Tri Face Graphics": frmTriGraphics.Show
End Select

Exit Sub

'***********************************
ErrorHandler:

strPrompt = "An error has occured. Please make a note of what sequence
of clicks you made and contact Adminstrator."
strPrompt = strPrompt & vbNewLine & vbNewLine & "Location: " & SubName
& vbNewLine
strPrompt = strPrompt & Err.Number & "; " & Err.Description

MsgBox strPrompt, vbCritical, "Problem"

End Sub

 

[Chip Pearson]

If you are using the command buttons from the Forms toolbar, you can
assign the same procedure to all buttons and then use
Application.Caller to get the name of the button that was clicked.
Once you have that, you can get properties like Caption and Name.
Application.Caller returns a String that is the name of the clicked
button.


For example,

Sub ClickProc()
Dim TheButton As Excel.Button
Set TheButton = ActiveSheet.Buttons(Application.Caller)
MsgBox "Button Clicked:" & vbCrLf & _
"Name: " & TheButton.Name & vbCrLf & _
"Caption: " & TheButton.Caption
End Sub

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Jim Thomlinson]

Take a look at
application.caller

 

[RyanH]

It works for me! You're the man! Thanks!

 

[RyanH]

That's what I needed! Thanks Chip!
Love your website!

 

[RyanH]

In my immediate window I got a count of all the buttons on the activesheet.

Activesheet.Buttons.Count = 29, I physically counted 21, any ideas why?

 

[RyanH]

Sorry stupid question. For some reason I could not see the buttons, so I
just used code to delete them. Thanks for the responses.

 

[Dave Peterson]

Maybe you have hidden (or very small) buttons on that worksheet??

Can you loop through them and show some info:

dim BTN as Button
for each BTN in activesheet.buttons
with btn
.visible = true
msgbox .name & vblf & .topleftcell.address(0,0)
end with
next btn

 

[RyanH]

Thanks for the response. I did loop through them and found the captions of
them and then deleted them using the immediate window.

I couldn't see anything on Buttons or Forms Controls in the object browser,
do you know where any info maybe on them?

Do you know some code that would list all controls in the workbook so I can
ensure there are no other hidden controls?

 

[Dave Peterson]

Open the VBE
Show the Object browser (hit F2)
Rightclick on the righthand window and choose "Show Hidden Members"

You could loop through all the shapes on the worksheet.

Ron de Bruin has lots of tips here:
http://www.rondebruin.nl/controlsobjectsworksheet.htm

Be careful. You may have some shapes that you don't want deleted -- like
autofilter arrows.