Command Button Question

  • Thread starter Thread starter ron
  • Start date Start date
R

ron

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
 
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
Click to expand...

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
 
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
Click to expand...

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 wasclicked?..Ron
 
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
Click to expand...

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) do this:

Dim button_name As String: button_name = Application.Caller

And aside: ... don't ever hide this button
 
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
Click to expand...

Oops... that won't give you the caption. Hummm.... Ignore that.
 
Oops... that won't give you the caption. Hummm.... Ignore that.
Click to expand...

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
 
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
Click to expand...

I have a form button placed on a sheet and assigned to macro xyz. Here is how I change the caption:

Public Sub xyz()

Dim s As String: s = Application.Caller
Dim ws As Worksheet: Set ws = Application.ActiveSheet
MsgBox "Here is the name of the button" & s

ws.Buttons("Button 3").Caption = "off"

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
Click to expand...

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
 
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
Click to expand...

Guys...Thanks, I appreciate your help...Ron
 
Back
Top