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.

"sandyboy" wrote:

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.

--
sandyboy


"Mike" wrote:

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

"Greg Wilson" wrote:

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


"Mike" wrote:


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



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