Spen82
03-14-2008, 01:11 PM
Hi, I want to write code which allows the user to slect a range in any worksheet. The cell will then be used in a loop which writes values and recalcs.
The problem is my code only works when the user selects a cell IN THE CURRENT WORKSHEET!! if they click on another worksheet tab, then click on a cell, then despite the full address of the cell appearing correctly in the dialogue box, (Sheet2!C16), the value assigned to my range variable is 'Nothing' and so the code exits the program.
please could someone help? Thank you! Code below.
' select correl cell
Dim CorrelCell As Range
On Error Resume Next
Set CorrelCell = Application.InputBox("Please click on the Correlation Input Cell to vary (Correlation level, not bump).", _
, Selection.Address, , , , , 8)
If CorrelCell Is Nothing Then
MsgBox "Correlation sensitivity graphing cancelled."
Else
MsgBox "Correlation input cell is: " & CorrelCell.Address(external:=True)
End If
The problem is my code only works when the user selects a cell IN THE CURRENT WORKSHEET!! if they click on another worksheet tab, then click on a cell, then despite the full address of the cell appearing correctly in the dialogue box, (Sheet2!C16), the value assigned to my range variable is 'Nothing' and so the code exits the program.
please could someone help? Thank you! Code below.
' select correl cell
Dim CorrelCell As Range
On Error Resume Next
Set CorrelCell = Application.InputBox("Please click on the Correlation Input Cell to vary (Correlation level, not bump).", _
, Selection.Address, , , , , 8)
If CorrelCell Is Nothing Then
MsgBox "Correlation sensitivity graphing cancelled."
Else
MsgBox "Correlation input cell is: " & CorrelCell.Address(external:=True)
End If