Novice VBA question...
 

Hello

I am 3 weeks into learning VBA so knowledge is low.
Still, how do I get VBA to return the cell reference of
an activecell?

If I click on, say, cell B10 how can I get VBA to
register that? The reason is, say I click on cell B10, I
want that to trigger VBA to bring up a MsgBox. But I
can't find a way for VBA to recognise that cell B10 has
been 'clicked upon'!

I just can't find away in which VBA will recognise cell
references.

Suppose, cell B10 has the value 5. If I type...

MsgBox Range("B10").Value

Then I get a MsgBox showing 5. But suppose I wanted VBA
to tell me which cell in a worksheet contained the value
5 (assuming there is only one).

Any thoughts out there?

Novice VBA question...
 

Try referencing the address property of the range or cell
object. For example, try the following code:

If ActiveCell.Value = 5 Then MsgBox ActiveCell.Address

OR

If Range("B10").Value = 5 Then MsgBox Range("B10").Address

Hope that helps.

-----Original Message-----
Hello

I am 3 weeks into learning VBA so knowledge is low.
Still, how do I get VBA to return the cell reference of
an activecell?

If I click on, say, cell B10 how can I get VBA to
register that? The reason is, say I click on cell B10, I
want that to trigger VBA to bring up a MsgBox. But I
can't find a way for VBA to recognise that cell B10 has
been 'clicked upon'!

I just can't find away in which VBA will recognise cell
references.

Suppose, cell B10 has the value 5. If I type...

MsgBox Range("B10").Value

Then I get a MsgBox showing 5. But suppose I wanted VBA
to tell me which cell in a worksheet contained the value
5 (assuming there is only one).

Any thoughts out there?
.

Novice VBA question...
 

If I click on, say, cell B10 how can I get VBA to register that?

clicking a cell in itself is not an "event", but selecting a cell/range is
(by mouse or by keyboard).

Right click on a worksheet tab and pick View Code. This will open the
worksheet's code module. In the left drop down at the top of the module,
select worksheet. In the right drop down will appear all the "events" that
Excel will call your code for.

SelectionChange is the event that appears in the module by default. Add
code so it looks like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.Address
End Sub

Now return to Excel and select some ranges.

--
Jim Rech
Excel MVP
"Alex" wrote in message
...
| Hello
|
| I am 3 weeks into learning VBA so knowledge is low.
| Still, how do I get VBA to return the cell reference of
| an activecell?
|
| If I click on, say, cell B10 how can I get VBA to
| register that? The reason is, say I click on cell B10, I
| want that to trigger VBA to bring up a MsgBox. But I
| can't find a way for VBA to recognise that cell B10 has
| been 'clicked upon'!
|
| I just can't find away in which VBA will recognise cell
| references.
|
| Suppose, cell B10 has the value 5. If I type...
|
| MsgBox Range("B10").Value
|
| Then I get a MsgBox showing 5. But suppose I wanted VBA
| to tell me which cell in a worksheet contained the value
| 5 (assuming there is only one).
|
| Any thoughts out there?