PDA

View Full Version : Solved: Which button is Chosen



vzachin
10-02-2008, 11:11 AM
hi,

i hope i can phrase what i'm asking for correctly.

i have 3 macros with buttons associated with a file name which basically perform the same function (query a mainframe based on a file name).
instead of writing 3 separate macros, is there a way i can determine which button is clicked on?
that way,if button 1 is clicked, then open file xxx, if button 2 is clicked, open file yyy, and if button 3 is clicked, open file zzz.
...etc


hope this makes sense
thanks
zach

Kenneth Hobs
10-02-2008, 11:52 AM
Since you know what to send, you can just send it and pass a string. You could just do, ShowMsg "Oval 1". You can see the name of the Shape in the Names drop down box when you right click it.

Sub but1()
Dim s As String
s = ActiveSheet.Shapes(1).Name
ShowMsg s
End Sub

Sub ShowMsg(msg As String)
MsgBox "You clicked: " & msg
End Sub
With this method, it will pass the name of the shape that was clicked to run it.
ShowMsg ActiveSheet.Shapes(Application.Caller).Name

Since your shape numbers are the same as the door numbers, you don't even need to pass a string.
Sub but1()
MyDoor
End Sub
Sub but2()
MyDoor
End Sub
Sub but3()
MyDoor
End Sub

Sub MyDoor()
Dim Door As Integer, str As String
str = ActiveSheet.Shapes(Application.Caller).Name
Door = CInt(Right(str, Len(str) - InStrRev(str, " ")))
MsgBox "You selected door: " & "Door " & Door
End Sub

Paul_Hossler
10-02-2008, 12:14 PM
A little different -- I just picked up the caption from the shape after putting the same macro on every shape



Option Explicit
Sub ButtonPush()
Dim shpButton As Shape

Set shpButton = ActiveSheet.Shapes(Application.Caller)

MsgBox "You have selected " & shpButton.DrawingObject.Caption
End Sub


Paul

vzachin
10-02-2008, 05:42 PM
hi kenneth & paul,

thanks for the coding; works exactly the way i need it.
:beerchug:

zach