Keep chart stationary on worksheet when scrolling

[Eruantano]

I tried searching for this both here and through google without any luck. I'm not sure if I'm using the correct search terms, but here is the gist of the problem (if the title isn't clear enough!). =)

I'm hoping to be able to keep a chart stationary while scrolling through a worksheet entering data. A few points:

1) 'Freeze pane' wouldn't work for me since the chart is too large and I'd prefer to have the data sheet unfrozen for easy perusal.
2) I saw mention of opening a separate window with the chart in it, while having the workbook window aligned next to it. I don't know if that can work, but I'd also prefer to just have the chart on the worksheet and stationary. Either way, I didn't see any sample code listed for this option and would appreciate help with that if this is the only viable option.

Excel gurus... please help!

Thanks a lot.

 

[Tom Urtis]

Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
With ActiveWindow.VisibleRange
ActiveSheet.ChartObjects(1).Top = .Top + 5
ActiveSheet.ChartObjects(1).Left = .Left + .Width - _
ActiveSheet.ChartObjects(1).Width - 45
End With
End Sub

 

[Eruantano]

Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
With ActiveWindow.VisibleRange
ActiveSheet.ChartObjects(1).Top = .Top + 5
ActiveSheet.ChartObjects(1).Left = .Left + .Width - _
ActiveSheet.ChartObjects(1).Width - 45
End With
End Sub

Thanks very much Tom, made a few adjustments to the numbers to position it more to the center, and also had to add a 'protect-unprotect' sequence in order to avoid an error. Otherwise, works perfectly.

Eru.

 

[whopkins85]

Thanks very much Tom, made a few adjustments to the numbers to position it more to the center, and also had to add a 'protect-unprotect' sequence in order to avoid an error. Otherwise, works perfectly.

Eru.

Care to share the adjustments that you made in order to get it to work?

 

[frankdshaw]

Tom,

I used your code and it works great. I have to charts on the page and would like to have them both float in different places. How can I modify the code below to have it pick up both chart windows and place them where I want them.

Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
With ActiveWindow.VisibleRange
ActiveSheet.ChartObjects(1).Top = .Top + 5
ActiveSheet.ChartObjects(1).Left = .Left + .Width - _
ActiveSheet.ChartObjects(1).Width - 45
End With
End Sub

 

[Tom Urtis]

Well, you can add a small block of code to deal with the second chart object, to float it below the first chart object as in this example. Change the number 300 (if you need to) to avoid overlap.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
With ActiveWindow.VisibleRange

ActiveSheet.ChartObjects(1).Top = .Top + 5
ActiveSheet.ChartObjects(1).Left = .Left + .Width - _
ActiveSheet.ChartObjects(1).Width - 45

ActiveSheet.ChartObjects(2).Top = .Top + 300
ActiveSheet.ChartObjects(2).Left = .Left + .Width - _
ActiveSheet.ChartObjects(2).Width - 45

End With
End Sub

 

[Victor N]

Well, you can add a small block of code to deal with the second chart object, to float it below the first chart object as in this example. Change the number 300 (if you need to) to avoid overlap.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
With ActiveWindow.VisibleRange

ActiveSheet.ChartObjects(1).Top = .Top + 5
ActiveSheet.ChartObjects(1).Left = .Left + .Width - _
ActiveSheet.ChartObjects(1).Width - 45

ActiveSheet.ChartObjects(2).Top = .Top + 300
ActiveSheet.ChartObjects(2).Left = .Left + .Width - _
ActiveSheet.ChartObjects(2).Width - 45

End With
End Sub

Tom, I added the code but there must be something I am missing to define the chart I already have on the worksheet. To be correct I am trying to apply this to a smart art list. Could the smart art be my problem? Either way it seems like I need to define what I am trying to apply this code to via some selection or definition within the code.

 

[Tom Urtis]

Not exactly sure what you are working with, but for starters, put your worksheet into Design mode, then select this object you are talking about, and post back with its name that you can see in the name box. It might be an embedded shape of some kind instead of a chart.

 

[VikingOy]

Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

I tried this on Excel 2016 but it had no effect what so ever except it moved my graph a few cells up and to the right.
The graph still scrolls with the sheet and eventually off screen.

What am I not understanding here - or doing wrong?

 

[Tom Urtis]

The key word in your message that jumps out to me as a possible reason is "scrolls", when you wrote "The graph still scrolls with the sheet...".

The action (event) being monitored by the code is the selection of a cell, wherever that cell may be. For example, if you hit the PgUp or PgDn keys to move up or down a full screen, or you hit the Alt+PgUp or Alt+PgDn keys to move right or left a full screen, or whatever method you use that somehow directly selects a cell, then the code works as advertised; I just now tested it again in 2016.

What might be happening is:
• You are scrolling by using the horizontal or vertical scroll bar which means you are not selecting a cell, or
• You have the code in a module other than the module of worksheet that holds the chart, or
• Your chart object is not index chart #1 (I doubt this is the reason).