Function for cursor location

S

sandyboy

Is there such an excel function that detects the cursor location?

Ie: If cursor is in A1, the contents of B1 should be "Cursor is in A1"

Thanks
 
G

Greg Wilson

There is no such function but there is now. You must paste it to a standard
module. However, it will only update when the worksheet is calculated:

Function CPos() As String
Application.Volatile
CPos = "Cursor is in " & ActiveCell.Address(False, False)
End Function

If you need it to update when the cell selection is changed (cursor position
changes) then you need to also paste this to the worksheet's module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Calculate
End Sub

To get it to work, enter this into the desired cell:
=CPos()

Regards,
Greg
 
M

Mike

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets("Sheet1").Range("B2").Formula = CPosition()
Me.Calculate
End Sub
 
G

Greg Wilson

Mike,

We don't need to add the formula to the cell each time a cell selection is
made. By making the function volatile, it updates every time a sheet
calculation occurs, which the selection change code makes happen every time
cell selection changes. Not only is this not necessary, but hard coding it
into the event code defeats the purpose of the function - e.g. if you want to
change it to C1 then you also have to change the code. The way I have set it
up, just enter "=CPos()" in cell C1 and clear the formula in B1. Also, you
can enter the formula into multiple cells if you want.

I tested it. It works.

Regards,
Greg
 
M

Mike

Greg your right but alot of times ok most of the time ok maybe sometimes
some people worry about =CPos() getting removed
 
S

sandyboy

I appreciate all this, but problem is, i don't know how to make a module.. :c
I'd be happy if anyone can help me about this and can refer me to a site to
know more about this.
 
M

Mike

Hi,

To create a standard module hit ALT + F11 to open VB editor and then right
click on the workbook project, insert - insert module and paste the code you
have been given in there.

Mike
 
D

Dave F

Sandyboy, if you want more information on what Greg Wilson is proposing, see
here: http://www.ozgrid.com/VBA/Functions.htm#UDFIntro

Since Excel doesn't offer a built in function to do what you want to do, he
created a custom function, called a user defined function. That gets
inserted into a module.

The link above explains this in more detail.

Dave
 
J

JLatham

Mike has given you the quick way to get into VB Editor to add a new module or
edit an existing one: [Alt]+[F11] the first time you do that in a workbook
that does not have any modules, you'll have to follow up with an Insert |
Module from the VBE menu. You can put several Sub or Functions into a single
module, you do not have to have a separate module for each Sub or Function.

If you need to ever put code into a worksheet (such as the
Worksheet_SelectionChange suggestion) - right-click on the sheet's tab and
choose [View Code] from the list presented.
 
Top