passing a parameter from a button invoked VBA macro?

[Kevin_Waite]

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

 

[Jim Cone]

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 USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)



"Kevin_Waite" <[email protected]>
wrote in message
news:fdbb597a-d41d-4c3d-bfe8-77cf829cfa47@q12g2000prb.googlegroups.com...

 

[Kevin_Waite]

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" <[email protected]>
wrote in message

























- 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
===================================================================