software

I am looking for a way to make a chart pop-up in a worksheet no matter the
location I am working in the sheet at that moment. And to make it disappear
again.

If this is not possible, then I would be very happy to be able to return to
the place where I left my worksheet by command button. E.g. my cursor is at
cell SY2377 in worksheet A and I use a command button to jump to sheet B. How
then do I return to SY2377 in sheet A?

Reason : It's a very large workseet in which I am changing data at several
places. It is not possible to have data and chart at the same screen (too
big).
You could try this code. Put it in a VBA module. You'll need to
change the sheet references to match your sheet names. This requires a
sheet (I called it Sheet1) to capture and save the current active cell
and sheet addresses. You can fire this macro by pressing Alt-F8 then
running quot;FlipFlopquot; or assign it to a short cut key combination. Hope
that helps.

- John

Sub FlipFlop()

Dim rngSht As Range
Dim rngCell As Range
Dim sht As Sheets

Set rngSht = Sheets(quot;Sheet1quot;).Range(quot;A1quot;)
Set rngCell = Sheets(quot;Sheet1quot;).Range(quot;A2quot;)

If ActiveSheet.Name lt;gt; quot;Chart1quot; Then
rngSht.Value = ActiveSheet.Name
rngCell.Value = ActiveCell.Address
Sheets(quot;Chart1quot;).Activate
Else
Sheets(rngSht.Value).Activate
Range(rngCell.Value).Activate
End If

End SubJohn,

Thanks for your prompt answer. It works perfectly when I use Alt-F8, but I
want to assign it to a command-button. So, click a command button to go from
sheet to graph and click a command button to return. When I create this
situation in a little workbook for testing, everything works except returning
ot the last cell address. I get teh message - Runtime error '1004' : Activate
method of Range class failed.

Hope you can help me.

tHENKs
quot;John Michlquot; wrote:

gt; You could try this code. Put it in a VBA module. You'll need to
gt; change the sheet references to match your sheet names. This requires a
gt; sheet (I called it Sheet1) to capture and save the current active cell
gt; and sheet addresses. You can fire this macro by pressing Alt-F8 then
gt; running quot;FlipFlopquot; or assign it to a short cut key combination. Hope
gt; that helps.
gt;
gt; - John
gt;
gt; Sub FlipFlop()
gt;
gt; Dim rngSht As Range
gt; Dim rngCell As Range
gt; Dim sht As Sheets
gt;
gt; Set rngSht = Sheets(quot;Sheet1quot;).Range(quot;A1quot;)
gt; Set rngCell = Sheets(quot;Sheet1quot;).Range(quot;A2quot;)
gt;
gt; If ActiveSheet.Name lt;gt; quot;Chart1quot; Then
gt; rngSht.Value = ActiveSheet.Name
gt; rngCell.Value = ActiveCell.Address
gt; Sheets(quot;Chart1quot;).Activate
gt; Else
gt; Sheets(rngSht.Value).Activate
gt; Range(rngCell.Value).Activate
gt; End If
gt;
gt; End Sub
gt;
gt;

If you are using a command button that would imply that you are always
starting and ending at the same place. If that is the case, you don't
need to quot;capturequot; the starting cell and can quot;hard codequot; the locations.
Assuming the button is on quot;Sheet1quot; and also on quot;Chart1quot; try...

Sub FlipFlopRevised()

If ActiveSheet.Name lt;gt; quot;Chart1quot; Then
Sheets(quot;Chart1quot;).Activate
Else
Sheets(quot;Sheet1quot;).Activate
End IfEnd SubJohn,

The trick is that I use frozen windows, so the command buttons are alway up
in the upper left corner of the screen. Down-right I am working with my data.

I always used Sheets.Select to change from the one sheet to the other, but
then you always return to cell A1. In your FlipFlop routine, you used
Sheets.Activate and I noticed that you than return to the sheet in the same
way as you left it.

Simple as that! In other words : It works perfectly.

Many thanks for helping me out.

Regards,

Henk