Command Button Question

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
 
O

oldyork90

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
 
O

oitbso

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 wasclicked?..Ron
 
O

oldyork90

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

Dim button_name As String: button_name = Application.Caller

And aside: ... don't ever hide this button
 
O

oldyork90

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.
 
O

oitbso

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
 
O

oldyork90

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

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
 
G

GS

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

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
 
O

oitbso

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

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

Ask a Question

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.

Ask a Question

Top