Hi all,

Is there any way in VBA it make the value of any cell that is clicked
on available to be used in the code. For example, if you had a range of
numbers and the user clicked on any one of the cells; that value they
clicked on would then be available to be used in VBA, such as in a
function. Below is what I'm trying to achieve.

The Sub and function below are in a module. The idea is that the cell
that the user clicks on is placed into memory and becomes available to
be used any where.

---------------------
Sub PutMemoryTestNumber(NewMemoryTestNumber As Single)
sngMemoryTestNumber = NewMemoryTestNumber
End Sub

Function fnMemoryTestNumber() As Single
fnMemoryTestNumber = sngMemoryTestNumber
End Function
--------------------

The subroutine below is in the form where the user clicks on the cell.

--------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
PutMemoryTestNumber THIS IS WHERE I WOULD LIKE TO ADD THE VALUE OF THE
CELL THAT THE USER HAS CLICKED ON
End Sub
-------------------

The cell that the user has clicked on would be placed into the
subroutine PutMemoryTestNumber which would then be available to the
function.

Does this make sense?

Any help would be greatly appreciated.

Best Regards,

Aaron


--
Aaron1978
------------------------------------------------------------------------
Aaron1978's Profile: http://www.excelforum.com/member.php...o&userid=31201
View this thread: http://www.excelforum.com/showthread...hreadid=548156

Hello Aaron1978,

In one of your VBA modules, delare MemoryTestNumber as Public. This
makes the variable global, i.e. avaibable to all routines in VBA.

Public MemoryTestNumber As Single

This macro looks sets the test range to be A2:K50, you can change this
to whatever you are using. If the clicked cell is within this range, it
then sets the global variable MemoryTestNumber equal to the cell's
value. If the cell is not a number, you will get a type mismatch error.



Code:
--------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim TestRange As Range
'Define your test range
Set TestRange = ThisWorksheet.Range("A2:K50")
'Test that the cell selected in within the range you want
Set TestRange = Application.Intersect(Target, MyRange)
'Test if clicked cell is within the given range
If Not TestRange Is Nothing Then MemoryTestNumber = TestRange.Value
End Sub

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=548156

Thanks man. However, when I click on a cell I get the following error:

Runtime error '424':
Object Required

And it takes me to the lineL


Code:
--------------------

Set TestRange = ThisWorksheet.Range("F13:F18")

--------------------


Any ideas what I'm doing wrong?


Code:
--------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim TestRange As Range
Dim MemoryTestNumber As Single
'Define your test range
Set TestRange = ThisWorksheet.Range("F13:F18")
'Test that the cell selected in within the range you want
Set TestRange = Application.Intersect(Target, MyRange)
'Test if clicked cell is within the given range
If Not TestRange Is Nothing Then MemoryTestNumber = TestRange.Value

PutMemoryTestNumber MemoryTestNumber

End Sub

--------------------


Thanks again,

Aaron


--
Aaron1978
------------------------------------------------------------------------
Aaron1978's Profile: http://www.excelforum.com/member.php...o&userid=31201
View this thread: http://www.excelforum.com/showthread...hreadid=548156

Hi Aaron,

Try changing

Set TestRange = ThisWorksheet.Range("F13:F18")

to

Set TestRange = Activesheet.Range("F13:F18")


---
Regards,
Norman



"Aaron1978" wrote
in message ...

Thanks man. However, when I click on a cell I get the following error:

Runtime error '424':
Object Required

And it takes me to the lineL


Code:
--------------------

Set TestRange = ThisWorksheet.Range("F13:F18")

--------------------


Any ideas what I'm doing wrong?


Code:
--------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim TestRange As Range
Dim MemoryTestNumber As Single
'Define your test range
Set TestRange = ThisWorksheet.Range("F13:F18")
'Test that the cell selected in within the range you want
Set TestRange = Application.Intersect(Target, MyRange)
'Test if clicked cell is within the given range
If Not TestRange Is Nothing Then MemoryTestNumber = TestRange.Value

PutMemoryTestNumber MemoryTestNumber

End Sub

--------------------


Thanks again,

Aaron


--
Aaron1978
------------------------------------------------------------------------
Aaron1978's Profile:
http://www.excelforum.com/member.php...o&userid=31201
View this thread: http://www.excelforum.com/showthread...hreadid=548156

Excellent, that worked great. Thanks.

I'm now getting the same error message but for the line:


Code:
--------------------

Set TestRange = Application.Intersect(Target, MyRange)

--------------------


I'm not sure what MyRange is doing.

Any ideas?


Code:
--------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim TestRange As Range
Dim MemoryTestNumber As Single
'Define your test range
Set TestRange = ActiveSheet.Range("F13:F18")
'Test that the cell selected in within the range you want
Set TestRange = Application.Intersect(Target, MyRange)
'Test if clicked cell is within the given range
If Not TestRange Is Nothing Then MemoryTestNumber = TestRange.Value

PutMemoryTestNumber MemoryTestNumber

End Sub

--------------------


--
Aaron1978
------------------------------------------------------------------------
Aaron1978's Profile: http://www.excelforum.com/member.php...o&userid=31201
View this thread: http://www.excelforum.com/showthread...hreadid=548156

Hi Aaron,

Try changing:

Set TestRange = Application.Intersect(Target, MyRange)


to

Set TestRange = Application.Intersect(Target, TestRange)


---
Regards,
Norman



"Aaron1978" wrote
in message ...

Excellent, that worked great. Thanks.

I'm now getting the same error message but for the line:


Code:
--------------------

Set TestRange = Application.Intersect(Target, MyRange)

--------------------


I'm not sure what MyRange is doing.

Any ideas?


Code:
--------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim TestRange As Range
Dim MemoryTestNumber As Single
'Define your test range
Set TestRange = ActiveSheet.Range("F13:F18")
'Test that the cell selected in within the range you want
Set TestRange = Application.Intersect(Target, MyRange)
'Test if clicked cell is within the given range
If Not TestRange Is Nothing Then MemoryTestNumber = TestRange.Value

PutMemoryTestNumber MemoryTestNumber

End Sub

--------------------


--
Aaron1978
------------------------------------------------------------------------
Aaron1978's Profile:
http://www.excelforum.com/member.php...o&userid=31201
View this thread: http://www.excelforum.com/showthread...hreadid=548156

Thanks again.


--
Aaron1978
------------------------------------------------------------------------
Aaron1978's Profile: http://www.excelforum.com/member.php...o&userid=31201
View this thread: http://www.excelforum.com/showthread...hreadid=548156