Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Activating a Chart object | Charts and Charting in Excel | |||
Pie chart on a pie chart (exploded)? | Excel Discussion (Misc queries) | |||
Urgent Chart Assistance | Charts and Charting in Excel | |||
Urgent Chart Questions | Excel Discussion (Misc queries) | |||
Urgent Chart Assistance Requested | Excel Discussion (Misc queries) |