PDA

View Full Version : Solved: Function/Sub with Parameters assigned to a Button



mailman
07-10-2008, 07:24 AM
I have to assume this issue has come up before (so apologies for my poor searching abilities). All I've found that Subs (with parameters) and Functions don't show up in the macro list, but I haven't found out why or how to execute them.

I've got a simple function that finds a value and returns the found value (active cell) to the top left corner of the Excel document.

Public Sub FindValueGoHome(searchValue As String)
Dim RangeObj As Range

Set RangeObj = Cells.Find(What:=searchValue, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

If Not RangeObj Is Nothing Then
RangeObj.Select
Application.Goto ActiveCell, True
End If
End Sub

Now, I'd like to set up several macro buttons to execute my Sub but pass in the appropriate string for each button.

I've also found
Application.WorksheetFunction.FindValueGoHome("blah")
but this isn't working either.

And last but not least, my current solution is I've created X number of Subs that have no params, but call my Sub above with the appropriate string, but this seems pretty rediculous to me.

I'm running this out of Excel 2003. Any helps is appreciated. Thanks! :think:

figment
07-10-2008, 08:07 AM
i recall the button tag value being usable to pass parameters to a sub, but i don't recall how to do it.

TomSchreiner
07-10-2008, 10:30 AM
Enter into the macro dialog.
'FindValueGoHome "StringArg"'

(Hyphen)FindValueGoHome(Space)(Quote)StringArg(Quote)(Hyphen)

You can also use Application.Caller...

Public Sub FindValueGoHome(searchValue As String)
Dim RangeObj As Range

Select Case Application.Caller
Case "Button 1"

Case "Button 2"

End Select

Set RangeObj = Cells.Find(What:=searchValue, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

If Not RangeObj Is Nothing Then
RangeObj.Select
Application.Goto ActiveCell, True
End If
End Sub

mailman
07-10-2008, 11:35 AM
Thanks to both of you for your input. I ended up making the mistake of not explicity pointing it to PERSONAL.XLS. The end result in the Macro dialogue was:

PERSONAL.XLS!'FindValueGoHome "StringArg"'

:thumb