Excel 2003: Double clicking a cell to change another cell's content in another worksheet

Question

All I'm trying to do is figure out how to change the cell in one worksheet to that of another when the one in the other is double clicked.

In my second worksheet, I have the following code set up:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)



End Sub

My question would be what's the code to change a cell?

I imagine it'd look something like this:

Sheet1!CellA1!Value = Target.Value

But clearly that's not right. So what is right?

Answer 1

Try

Worksheet("Sheet1").Range("A1").Value = Target.Value

Or, make the cell you want to change a Named Range, and just use

Range("MyName").Value = Target.Value

Answer 2

If you look in the Project Explorer in the VBA editor, there are actually two sheet names. The name on the left is how the sheet is seen within VBA, the name on the right is the name given on the tab strip at the bottom when the user is using Excel. (By default they're both "Sheet1", that can be confusing.)

I find it easiest to use the first name; it removes the need to say Worksheet("Whatever"). If the name hasn't been changed, just use that.

Sheet1.Range("A1").Value = Target.Value

edit: As a note, the only way to change the name on the left is to make sure the "Properties" window is visible, click on the sheet, and then rename. I find it to be best practice to rename all my sheets right away, to avoid Sheet1, Sheet2, Sheet3. wsTotals, wsCoverForm, and wsConfigForm can make code much more readable.

another edit: The reason the other method isn't working is because it would have to follow this structure:

ActiveWorkbook.Worksheets("Sheet1").Range("A1").Value = Target.Value

That's the only way I could get it to work, I've had both work for me testing it out just now. It's a little less cumbersome to use the first way.