Chart pop-up
 

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).

Chart pop-up
 

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 "FlipFlop" 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("Sheet1").Range("A1")
Set rngCell = Sheets("Sheet1").Range("A2")

If ActiveSheet.Name < "Chart1" Then
rngSht.Value = ActiveSheet.Name
rngCell.Value = ActiveCell.Address
Sheets("Chart1").Activate
Else
Sheets(rngSht.Value).Activate
Range(rngCell.Value).Activate
End If

End Sub

Chart pop-up
 

John,

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



"John Michl" wrote:

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 "FlipFlop" 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("Sheet1").Range("A1")
Set rngCell = Sheets("Sheet1").Range("A2")

If ActiveSheet.Name < "Chart1" Then
rngSht.Value = ActiveSheet.Name
rngCell.Value = ActiveCell.Address
Sheets("Chart1").Activate
Else
Sheets(rngSht.Value).Activate
Range(rngCell.Value).Activate
End If

End Sub

Chart pop-up
 

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 "capture" the starting cell and can "hard code" the locations.
Assuming the button is on "Sheet1" and also on "Chart1" try...

Sub FlipFlopRevised()

If ActiveSheet.Name < "Chart1" Then
Sheets("Chart1").Activate
Else
Sheets("Sheet1").Activate
End If


End Sub

Chart pop-up
 

John,

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