I want to write a macro that documents the address of the
cell that is clicked, NOT the active cell.

Example:
With A5 active, you invoke the macro.
The macro asks "What cell do you want to document?"
The user then clicks, say, A1.
The macro then enters this in A5: =cell("address",a1)&cell
("filename",a1)

Thanks!

Terry,

Look at Application.InputBox (with a Type of 8) in Help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Terry" wrote in message
...
I want to write a macro that documents the address of the
cell that is clicked, NOT the active cell.

Example:
With A5 active, you invoke the macro.
The macro asks "What cell do you want to document?"
The user then clicks, say, A1.
The macro then enters this in A5: =cell("address",a1)&cell
("filename",a1)

Thanks!

Terry,

Use something like

ActiveCell.Formula = "=CELL(""address""," & MyCell.Address & ")"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Terry" wrote in message
...
OK, now I've got:

Set MyCell = Application.InputBox(prompt:="Select cell
to document", Type:=8)

But how do I use this variable? If I use:

ActiveCell.Formula = "=cell(""address"",MyCell)"

... my result is #NAME? because "MyCell" isn't a valid
rangename in the worksheet.



-----Original Message-----
Terry,

Look at Application.InputBox (with a Type of 8) in Help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Terry" wrote in message
...
I want to write a macro that documents the address of
the
cell that is clicked, NOT the active cell.

Example:
With A5 active, you invoke the macro.
The macro asks "What cell do you want to document?"
The user then clicks, say, A1.
The macro then enters this in A5: =cell("address",a1)
&cell
("filename",a1)

Thanks!


.

That did it. Many thanks!
-----Original Message-----
Terry,

Use something like

ActiveCell.Formula = "=CELL(""address""," &
MyCell.Address & ")"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Terry" wrote in
message
...
OK, now I've got:

Set MyCell = Application.InputBox(prompt:="Select cell
to document", Type:=8)

But how do I use this variable? If I use:

ActiveCell.Formula = "=cell(""address"",MyCell)"

... my result is #NAME? because "MyCell" isn't a valid
rangename in the worksheet.



-----Original Message-----
Terry,

Look at Application.InputBox (with a Type of 8) in
Help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"Terry" wrote in message
...
I want to write a macro that documents the address of
the
cell that is clicked, NOT the active cell.

Example:
With A5 active, you invoke the macro.
The macro asks "What cell do you want to document?"
The user then clicks, say, A1.
The macro then enters this in A5: =cell("address",a1)
&cell
("filename",a1)

Thanks!


.



.

"Terry" wrote in message
...
OK, now I've got:

Set MyCell = Application.InputBox(prompt:="Select cell
to document", Type:=8)

But how do I use this variable? If I use:

ActiveCell.Formula = "=cell(""address"",MyCell)"

... my result is #NAME? because "MyCell" isn't a valid
rangename in the worksheet.


Set MyCell = Application.InputBox(prompt:="Select cell to document",
Type:=8)

ActiveCell.Formula = "=cell(""address""," & MyCell.Address & ")"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Sub AA()
Dim MyRange As Object
Set MyRange = Application.InputBox(prompt:= _
"What cell do you want to document?", Title:="Select Cell",
Type:=8)
ActiveCell.Value = MyRange.Address
End Sub

This will put in your active cell (A5 in your example) the string
"$A$1". I suppose if you want it in the way you described, you can
try this:

Sub AAA()
Dim MyCell As String
Dim MyRange As Object
Set MyRange = Application.InputBox(prompt:= _
"What cell do you want to document?", Title:="Select Cell",
Type:=8)
MyCell = MyRange.Address
ActiveCell.Value = "=Cell(""address""," _
& MyCell & ") & Cell(""filename"", " & MyCell & ")"
End Sub

Regards, John




"Terry" wrote in message ...
I want to write a macro that documents the address of the
cell that is clicked, NOT the active cell.

Example:
With A5 active, you invoke the macro.
The macro asks "What cell do you want to document?"
The user then clicks, say, A1.
The macro then enters this in A5: =cell("address",a1)&cell
("filename",a1)

Thanks!