Macros to change a cell value by clicking and to take you to another sheet

**jhelliar** · 11-09-2011, 09:15 AM

Hi,

I need to create two very simple macros and hoped that somebody might be able to help.

The first one needs to change the value in one specified cell to the value in another range of cells when one of them is clicked on.

e.g. if Cell B2 is the specified Cell and the range of cells is A1 to A100. When you click on Cell A2 the value in cell B2 changes to the value in A2.

The second is just that when you click on a cell in a range of cells it takes to one specific chart or worksheet. So if the range was A1 to A100 and you clicked on any of those cells you would be taken to a sheet called template.

Many thanks and any help on either issue greatfully received.

Jack

**JBeaucaire** · 11-09-2011, 09:35 AM

The Worksheet_SelectionChange event macro can do both of these for you.

Since you specified BOTH actions occur when the same range of cells is clicked then this event would update the value in B2 and then take you to the "Template" sheet automatically.

1) Right-click the sheet tab and select View Code to open the sheet module
2) Paste in this event macro:

Please Login or Register  to view this content.

If you don't like the "selection" method, you could replace it with a "double click" event, you would have to double click a cell in the range A1:A100 to get this event to trigger:

Please Login or Register  to view this content.

**jhelliar** · 11-09-2011, 09:38 AM

That is excellent, the only very minor change I would like is can the cell that is changed not be B2 but A1 on the template sheet?

Many thanks

Jack

**MarvinP** · 11-09-2011, 09:41 AM

Hi Jack,

Here is the code and the attachment to do what you want.

Please Login or Register  to view this content.

**JBeaucaire** · 11-09-2011, 10:03 AM

YOu don't need to actually "GO" to the template page to put a value into it. Using the "doubleclick" method, still:

Please Login or Register  to view this content.

You can remove the underlined code if you want to stay on your original sheet.

**JBeaucaire** · 11-09-2011, 10:05 AM

@Marvin, be careful where/if you put in the Cancel = True code, in your suggested spot it would effectively disable doubleclick access to any cell on the entire sheet. Place it inside your IF results to only disable it when the doubleclick occurred inside one of the watched ranges.