Solved: Using cmdbutton.caption in msgbox

[jdub12280]

Hello experts,

:help

I have a userform that includes a 4 page multipage. Each page has ~ 10 buttons. Each button is captioned within the userform initialize event. Each button serves basically the same purpose on different ranges. I have a msg box ask the user if they really want to do what they clicked... So there are about 40 cmdClick subs... i want the msg to to use the "buttonclicked".caption (me.caption wasn't scoped to the button but to the form)

I would like to replace the "me.cmdHorn1.caption" with something like me.caption where "me" refers to the button actually clicked within the multi page. (4 multipage pages aprox 10+ buttons each page)

Sample button sub with msgbox below:
(cmdHorn1_Click is a 1 example of 40 different buttons... all have the same naming convention)



Private Sub cmdHorn1_Click()
answer = MsgBox(msgP1 & Me.cmdHorn1.Caption & msgP2, vbOKCancel, "Click OK to Update")
If answer = vbCancel Then Exit Sub ' the macro ends if the user selects the CANCEL-button

Unload Me
Application.WindowState = xlMinimized
WB_SettingsOFF

On Error GoTo ResetWorkbookSettings:
If ActiveSheet.Name <> "LINKS" Then Sheets("LINKS").Select
Set LinkTable = ActiveSheet.Range("L5:U5")

For Each Rng In LinkTable
Rng.Select
Application.Run "UpdateLinks"
Next Rng

Sheets("LINKS").Range("A1").Select

WB_SettingsON
Application.WindowState = xlMaximized
MsgBox msgP3 & Me.cmdHorn1.Caption & msgP4, vbInformation
Exit Sub
ResetWorkbookSettings:
'Err.Clear
WB_SettingsON
Application.WindowState = xlMaximized
End Sub


Any help or direction... as always... is greatly appreciated :)

Thanks - Justin

[mikerickson]

You might try
MsgBox Me.ActiveControl.Caption

Also if you only need 10 actions, would it be possible to put those 10 buttons outside the Multi-page, decreasing the number of buttons by a factor of 4?

[p45cal]

You might also consider completely eliminating all those individual event handlers and have just one. See:
http://j-walk.com/ss/excel/tips/tip44.htm
and
http://spreadsheetpage.com/index.php/tip/handle_multiple_userform_buttons_with_one_subroutine/

[jdub12280]

Thanks for both suggestions... i will try the activecontrol method as it will be the quickest for me to implement... but will look at reducing the event handling down to one as well :) The fewer the better. Thanks to the both of you for your feedback.

:beerchug:

-Justin

[jdub12280]

Apologies, i assumed me.activecontrol.caption would work, however i get run time error '438' object doesn't support this property or method. Any ideas?

This is a command button on a multipage on a userform.

Thanks p45Cal and mikeerickson... i think we are in the right direction... maybe just another little nudge will do the trick...

-Justin

[mikerickson]

I forgot that it was inside a Multi-page.
If you put this in the UF code module, it should work.

Public WithEvents clickedButton As msforms.CommandButton

Private Sub clickedButton_Click()
Rem common code
MsgBox clickedButton.Caption
End Sub

Private Sub CommandButton1_Enter()
Set clickedButton = CommandButton1
End Sub

Private Sub CommandButton2_Enter()
Set clickedButton = CommandButton2
End Sub

'...

Private Sub CommandButton30_Enter()
Set clickedButton = CommandButton30
End Sub