Action Button-goto another Worksheet
Posted: Tue Feb 03, 2015 7:38 am
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:
I then set the Source Object to
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
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.
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
Code: Select all
=SelectedEmployee()p.s. Setting the Source Object to the obvious like
Code: Select all
=Selection
=ActiveCell
=ActiveCell.Value
...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.