PDA

View Full Version : Can User Select a cell on Different Worksheet?



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

Bob Phillips
03-14-2008, 02:30 PM
So what happens next, that is probably where the problem is.

BreakfastGuy
03-14-2008, 03:01 PM
Probably because you need bracket the result like this:

[Sheet2!C16].Value
or [CorrelCell] but to tell you the truth you haven't given us enough to go on!

Spen82
03-25-2008, 05:33 AM
Hi, let me know if you need some specific information to help solve this. The code I posted leads to a dialogue box. The user then wants to click on the appropriate cell, (i.e. he clicks on tab of desired worksheet then clicks on cell, then clicks OK)

I've stepped through to see what happens: when he chooses a cell on the starting worksheet it's fine. But when he selects a cell on another worksheet, the value 'Nothing' is stored in the variable CorrelCell, which leads the code to terminating.
I can't understand what the problem is. I don't understand how the brackets suggested above can be used. In the Code (i.e [CorrelCell]) they have no effect, and in the dialogue box they are invalid input.

Thank you for your time!