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

[Brett Scott]

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.

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?

 

[Smallman]

Hi Brett

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

Take care

Smallman

 

[Brett Scott]

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

 

[Smallman]

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

 

[Brett Scott]

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..

 

[Smallman]

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.

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

 

[Brett Scott]

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!!

 

[Smallman]

Hi Brett

Thanks for posting back.

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

Take care

Smallman