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