Action Button-goto another Worksheet

[gtonkin]

This may need to go to Tips & Tricks but before then would like to know if there is a better/easier way to do the following:
I have an ActiveForm with Employees on Rows, the refresh pastes values over formulae as users sort etc. When the user clicks the Action Button, I need to transfer them to a template within Applications but pass the value of the current employee i.e. the value in column B based on the row related to my selection (the id/name I have selected generally)

Under Advanced Options for the Action Button I have selected Source Type as Value and Source Object then becomes the issue as this is based on the selected Cell or column B for the row I am currently busy with.

What I did find is that the code behind the Action Button does various evaluations so you can actually insert VBA references.
To this end I did the following:

Created a function in a module per:

Code: Select all

Function SelectedEmployee()
SelectedEmployee = Cells(Selection.Row, 2).Text
End Function

I then set the Source Object to

Code: Select all

=SelectedEmployee()

Clicking on the Action Button now evaluates the function and sends the result of the function to my workbook for use in the report required.

p.s. Setting the Source Object to the obvious like

Code: Select all

=Selection
=ActiveCell
=ActiveCell.Value
...

Evaluated but with some weird results, mostly pointing to references in the workbook I was trying to open.

If there is a smarter or simpler way to do this, I would love to know - remember, I am using an Active Form where rows contain the driver and all values in the TM1RPTDATARNG are pasted as values.

[lotsaram]

You should be able to do this with the advanced properties of the action button. The "type" should be DBRW, the context of the dimension elements will then be picked up when navigating. (but it does only work if a DBRW cell is selected)