Reading a cell that a user has clicked on

[Aaron1978]

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

[Leith Ross]

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.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Aaron1978]

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

Please Login or Register  to view this content.

Any ideas what I'm doing wrong?

Please Login or Register  to view this content.

Thanks again,

Aaron

[Norman Jones]

Hi Aaron,

Try changing

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

to

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


---
Regards,
Norman



"Aaron1978" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Aaron1978]

Excellent, that worked great. Thanks.

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

Please Login or Register  to view this content.

I'm not sure what MyRange is doing.

Any ideas?

Please Login or Register  to view this content.

[Norman Jones]

Hi Aaron,

Try changing:

> Set TestRange = Application.Intersect(Target, MyRange)


to

Set TestRange = Application.Intersect(Target, TestRange)


---
Regards,
Norman



"Aaron1978" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Aaron1978]

Thanks again.