Passing the position of a button to a calling sub

Question

I'm not sure how to ask this question and I'm relatively new to the community.

I am trying to get the position of a button, which I have the code for, and it works.

How can I call the Sub below from another Sub and get the integer value of the row position?

Sub ButtonRow()
     ' Mainlineup Macro
    Dim b As Object, RowNumber As Integer
    Set b = ActiveSheet.Buttons(Application.Caller)
    With b.TopLeftCell
        RowNumber = .Row
    End With
    'MsgBox "Row Number " & RowNumber
End Sub

Possible duplicate of [How to Return a result from a VBA Function](https://stackoverflow.com/questions/2781689/how-to-return-a-result-from-a-vba-function) — Alan, Jun 25 '18 at 20:11

Alan · Answer 1 · 2018-06-25T21:23:39.683

There are two ways to pass variables around in VBA.

You can either use Public variables outside of the function, which will have scope outside of their sub, or use a Function, which is designed to return output.

Note that global public variables have certain security implications.

Functions must be named as the variable you intend to return - in this case Function RowNumber(). There are limitations on what you can do with Functions - IIRC functions cannot change any cell, worksheet, or workbook properties.

Sub WhatPosition()
    MsgBox RowNumber
End Sub

Public Function RowNumber() As Integer
    Dim b As Object
    Set b = ActiveSheet.Buttons(Application.Caller) 
    With b.TopLeftCell 
        RowNumber = .Row 
    End With 
End Function

or:

Public RowNumber As Integer

Sub WhatPosition()
    ButtonRow
    MsgBox RowNumber
End Sub

Sub ButtonRow() 
    Dim b As Object 
    Set b = ActiveSheet.Buttons(Application.Caller) 
    With b.TopLeftCell 
        RowNumber = .Row 
    End With
End Sub

Thank You so much! i tried the code, but ran into a problem. When i called the function, and tested to see if the position row was passed; the variable was empty. What if I made the variable Global to be accessed by other subs. Would that work too? — Mike, Jun 25 '18 at 20:50
Not sure if you can use `ActiveSheet` within a function. They have a lot of structural limitation. You might have to use the second example I posted. Or pass it the name of the current worksheet. — Alan, Jun 25 '18 at 20:59

Passing the position of a button to a calling sub

1 Answers1