I have a Command Button that I created using the Controls Toolbox. The Command Button is placed on a worksheet. What code do I need in the "Private Sub CommandButton1_Click()" macro to capture the name and caption of the Command Button after it has been clicked?..TIA, Ron
Building a toggle? If it was actX, something like this:
Private Sub CommandButton1_Click()
Dim s As String: s = CommandButton1.Caption
MsgBox s
End Sub
My bad. I should have pointed out that I have 12 command buttons (commandbutton1 through commandbutton12) and 12 modules, so it is not always commandbutton1. Once I click on the command button and I'm in whichever module,how then can I determine the name and caption of the command button that was clicked?..Ron
I take it its a form button. As I understand it actX parts each have an event. The form button has a "macro" assigned to it. In the same (sub) dothis:
Dim button_name As String: button_name = Application.Caller
And aside: ... don't ever hide this button
Oops... that won't give you the caption. Hummm.... Ignore that.
no, it's not a form button, it's a command button from the control toolbox; application.caller doesn't work for such a button
(commandbutton1 through commandbutton12) and 12 modules, so it is not
always commandbutton1. Once I click on the command button and I'm in
whichever module, how then can I determine the name and caption of
the command button that was clicked?..Ron
Uh.., if all 12 buttons are on the same sheet then you only have 1
module. Perhaps you mean you have 12 '_Click' event definitions, 1 for
each button.
-OR-
do you mean you have 1 button on 12 separate sheets?
In either case, I use an identifier in each procedure where another
procedure needs to know who called it. For example...
Type udtAppModes
Events As Boolean
CalcMode As XlCalculation
Display As Boolean
CallerID As String
End Type
Public AppMode As udtAppModes
Sub EnableFastCode(Caller$, Optional SetFast As Boolean = True)
'The following will make sure only the Caller has control,
'and allows any Caller to take control when not in use.
If AppMode.CallerID <> Caller Then _
If AppMode.CallerID <> "" Then Exit Sub
With Application
If SetFast Then
AppMode.Display = .ScreenUpdating
.ScreenUpdating = False
AppMode.CalcMode = .Calculation
.Calculation = xlCalculationManual
AppMode.Events = .EnableEvents
.EnableEvents = False
AppMode.CallerID = Caller
Else
.ScreenUpdating = AppMode.Display
.Calculation = AppMode.CalcMode
.EnableEvents = AppMode.Events
AppMode.CallerID = ""
End If
End With
End Sub 'EnableFastCode
..which would be used as follows:
Sub MySub()
Const sSource$ = "MySub"
EnableFastCode sSource '//turn it on
'...code follows
EnableFastCode sSource, False ''//turn it off
End Sub 'MySub
..where sSource is the ID tag for the calling procedure. In your
case...
Private Sub CommandButton1_Click()
Const sSource$ = "btn1"
Call SomeProcedure(sSource)
End Sub
-OR-
Private Sub CommandButton1_Click()
Call SomeProcedure(Me.CommandButton1.Caption)
End Sub
..where SomeProcedure accepts a string arg...
Sub SomeProcedure(Caller$)
Select Case Caller
Case "btn1" '(or caption)
'code...
Case "btn2" '(or caption)
'code...
Case "btn3" '(or caption)
'code...
Case "btn4" '(or caption)
'code...
Case "btn5" '(or caption)
'code...
Case "btn6" '(or caption)
'code...
Case "btn7" '(or caption)
'code...
Case "btn8" '(or caption)
'code...
Case "btn9" '(or caption)
'code...
Case "btn10" '(or caption)
'code...
Case "btn11" '(or caption)
'code...
Case "btn12" '(or caption)
'code...
End Select
End Sub
--
Garry
Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.