• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to display Selected Cell's value as a custom function

Brett Scott

New Member
Hello.

I am new to the forum but not new to the website. I have been using many of the excel dashboards as bases for a lot of my projects. One piece i need help with is on selecting a cell, it will display the activecell.value to the formula =selectedCell

I have a named range( B23:C261) named AGENTSELECTED. here is the formula im trying to use so when i type in =selectedcell in the document it will display the activecell.value.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Application.Intersect(Target, Range("agentselected")) Is Nothing Then
  [selectedCell] = ActiveCell.Value
  End If
End Sub

i get a runtime error: 1004:
Method 'range' of object'_Worksheet' failed

Can you see what im doing wrong?
 
Hi Brett

I am a bit confused - When you type in =selectedcell the selected cell is the cell you are in?

Take care

Smallman
 
lol now im confused on your question. I am wanting to basically create the function selectedCell.. so when i type in =selectedCell it will have the activecell.value displayed
 
Brett

In Excel, cell A1 type;

=

A1 is now your selected cell.

Can you see the problem you have?

I think I know what you are asking I was trying to get you to articulate it. This is what I think you mean.

When I type =Selectedcell I want the value of the last cell that was activated before I activated the current cell where ie A1.

Take care

Smallman
 
actually i want it based off the mouseclick.. so i can click any cell within the range AGENTSELECTED and that activecell.value will be displayed in the cell with the formula =selectedCell

That may make a little more sense..
 
Yes that makes more sense. You can do this with a Hyperlink. I will post an example for you. The custom function is pretty straight forward.

Actually I will do it now.

Code:
Public Function Roll(rng As Range)
  [A1] = rng
End Function

now in B2 put this formula.

=IFERROR(HYPERLINK(Roll(D2),D2),D2)

D2 is where your list is.

Here is a sample to make it clearer. The mouse rolling over the cell is what makes the change. I have samples and tutorials if you need to see more.

Take care

Smallman
 

Attachments

  • Roll.xlsm
    14.8 KB · Views: 9
wanted to say thank you. this method is different than what i was originally asking, but it actually works better and adds a little "flash" to the dashboard.

This is awesome. THANK YOU!!
 
Hi Brett

Thanks for posting back.

You are most welcome. All the best with your project.

Take care

Smallman
 
Back
Top