| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button Question
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
|
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Command Button Question
On Tuesday, February 18, 2014 6:21:03 PM UTC-6, ron wrote:
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Command Button Question
On Tuesday, February 18, 2014 6:57:09 PM UTC-7, wrote:
On Tuesday, February 18, 2014 6:21:03 PM UTC-6, ron wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Command Button Question
On Tuesday, February 18, 2014 8:14:04 PM UTC-6, wrote:
On Tuesday, February 18, 2014 6:57:09 PM UTC-7, wrote: On Tuesday, February 18, 2014 6:21:03 PM UTC-6, ron wrote: 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) do this: Dim button_name As String: button_name = Application.Caller And aside: ... don't ever hide this button |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Command Button Question
On Tuesday, February 18, 2014 8:34:28 PM UTC-6, wrote:
On Tuesday, February 18, 2014 8:14:04 PM UTC-6, wrote: On Tuesday, February 18, 2014 6:57:09 PM UTC-7, wrote: On Tuesday, February 18, 2014 6:21:03 PM UTC-6, ron wrote: 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) do this: 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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Command Button Question
On Tuesday, February 18, 2014 7:36:39 PM UTC-7, wrote:
On Tuesday, February 18, 2014 8:34:28 PM UTC-6, wrote: On Tuesday, February 18, 2014 8:14:04 PM UTC-6, wrote: On Tuesday, February 18, 2014 6:57:09 PM UTC-7, wrote: On Tuesday, February 18, 2014 6:21:03 PM UTC-6, ron wrote: 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) do this: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Command Button Question
On Tuesday, February 18, 2014 8:42:38 PM UTC-6, wrote:
On Tuesday, February 18, 2014 7:36:39 PM UTC-7, wrote: On Tuesday, February 18, 2014 8:34:28 PM UTC-6, wrote: On Tuesday, February 18, 2014 8:14:04 PM UTC-6, wrote: On Tuesday, February 18, 2014 6:57:09 PM UTC-7, wrote: On Tuesday, February 18, 2014 6:21:03 PM UTC-6, ron wrote: 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) do this: 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 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Command Button Question
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Command Button Question
On Tuesday, February 18, 2014 9:40:58 PM UTC-7, GS wrote:
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 Guys...Thanks, I appreciate your help...Ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Command Button question | Excel Programming | |||
| Command Button Question | Excel Programming | |||
| Command Button question | Excel Programming | |||
Linear Mode
