On Mar 23, 7:48*am, "Jim Cone" wrote:
Passing a value using the OnAction *method should be avoided.
It's implementation is not consistent across the various versions of Excel.
You can use Application.Caller to identify which button was clicked.
Using buttons from the Excel Forms toolbar (non active x), it returns a string containing the button
name.
As an illustration only...
'---
If Application.Caller = "Button 1" then
* *x = 2
ElseIf Application.Caller = "Button 2" Then...
* *x = 22
End If
'---
Also...
Declaring all variables is good coding practice.
Dividing by zero causes an error.
--
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)
"Kevin_Waite"
wrote in ...
Hello....
I have found several examples on line for setting the .OnAction member
with something akin to '!FooBar ABC 555"
I can not get that to work -- tried many many permutations of " and '
and spaces and comas *-- no go....
I am using MS VB 6.0 and Excel 2000.
I'd like to get rid of the four stem macros that are assigned to 4
buttons -- and just call srt_otot with the correct parm via the button
setup etc. . . . thanks in advance. . . . sincerely, Kevin Waite
Public Sub srt_otot_1()
* *Call srt_otot(1)
End Sub
Public Sub srt_otot_2()
* *Call srt_otot(2)
End Sub
Public Sub srt_otot_3()
* *Call srt_otot(3)
End Sub
Public Sub srt_otot_4()
* *Call srt_otot(4)
End Sub
Sub srt_otot(x As Integer)
* *Application.ScreenUpdating = False
* *cur_loc = ActiveCell.Address
* *If x < 1 And x < 2 And x < 3 And x < 4 Then
* * * *y = 1 / 0
* *End If
* *Range("B3").Select
* *top_rw = ActiveCell.Row
* *Range("B65536").End(xlUp).Select
* *bot_rw = ActiveCell.Row
* *If (bot_rw - top_rw) 250 Then y = 1 / 0
* *Rows(top_rw & ":" & bot_rw).Select
* *If srtot_flg(x) < 1 And srtot_flg(x) < 2 Then
* * * *srtot_flg(x) = 1
* *End If
* *srt_dir = xlDescending
* *If srtot_flg(x) = 1 Then
* * * *srt_dir = xlAscending
* * * *srtot_flg(x) = 2
* *Else
* * * *srtot_flg(x) = 1
* *End If
* *srt_col = Switch(x = 1, "B3", x = 2, "C3", x = 3, "H3", x = 4,
"Q3")
* *Selection.Sort Key1:=Range(srt_col), Order1:=srt_dir,
Header:=xlGuess, _
* * * OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
* *Range(cur_loc).Select
* *Application.ScreenUpdating = True
End Sub- Hide quoted text -
- Show quoted text -
Thanks greatly Jim! Exactly what I was looking for, and works great!
Working code example with your solution below.
Btw, dividing by zero does cause an error and thus wakes up
VB Editor
as I want/like, simple way to code on-the-fly break point for
conditions that shouldn't come to pass, or other reasons......
all the best,
Kevin
======================== works great =================================
Sub srt_otot()
Dim x As Integer
Dim cur_loc As String
Dim b_nm As String
Dim bot_rw As Integer
Dim top_rw As Integer
Dim srt_dir As Integer
Application.ScreenUpdating = False
cur_loc = ActiveCell.Address
b_nm = Application.Caller
x = Switch(b_nm = "srt_abc", 1, b_nm = "srt_val", 2, b_nm =
"srt_chg", 3, b_nm = "srt_dur", 4, True, 0)
If x < 1 And x < 2 And x < 3 And x < 4 Then
y = 1 / 0 '' halt, fire up debugger
End If
Range("B3").Select
top_rw = ActiveCell.Row
Range("B65536").End(xlUp).Select
bot_rw = ActiveCell.Row
If (bot_rw - top_rw) 250 Then y = 1 / 0 '' halt, fire up
debugger
Rows(top_rw & ":" & bot_rw).Select
If srtot_flg(x) < 1 And srtot_flg(x) < 2 Then
srtot_flg(x) = 1
End If
srt_dir = xlDescending
If srtot_flg(x) = 1 Then
srt_dir = xlAscending
srtot_flg(x) = 2
Else
srtot_flg(x) = 1
End If
srt_col = Switch(x = 1, "B3", x = 2, "C3", x = 3, "H3", x = 4,
"Q3")
Selection.Sort Key1:=Range(srt_col), Order1:=srt_dir,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Range(cur_loc).Select
Application.ScreenUpdating = True
End Sub
================================================== =================