跳到主要内容

在Excel中滚动时如何始终保持图表在视图中?

如果在工作表中插入了图表,则在向下滚动工作表以查看数据时,无法与下面显示的屏幕截图同时查看该图表,该屏幕截图必须是讨厌的。 在本文中,我介绍了一个VBA代码,即使向下或向上滚动工作表也可以始终查看图表。

doc保持图表在视图1
doc向下箭头
doc保持图表在视图2

始终查看图表


箭头蓝色右气泡 始终查看图表

要在滚动工作表时将图表保留在视图中,可以应用以下VBA代码进行解决。

1.右键单击要保持图表可见的工作表选项卡,然后单击 查看代码 形成上下文菜单。 看截图:
doc保持图表在视图3

2.在弹出 Microsoft Visual Basic应用程序 窗口,将下面的代码粘贴到空白脚本。

VBA:始终查看图表

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'UpdatebyExtendoffice20161111
    Dim CPos As Double
    Application.ScreenUpdating = False
    CPos = ActiveWindow.ScrollRow * ActiveCell.RowHeight
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveSheet.Shapes("Chart 2").Top = CPos
    ActiveWindow.Visible = False
    Application.ScreenUpdating = True
End Sub

doc保持图表在视图5

3.保存并关闭对话框,然后在单击任何单元格时,图表将上下移动。
doc保持图表在视图6

笔记:

(1)在VBA代码中,“图表2”是您要保留的图表名称,您可以根据需要进行更改。

(2)此VBA不能始终查看一组图表。

最佳办公生产力工具

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (15)
Rated 4.75 out of 5 · 2 ratings
This comment was minimized by the moderator on the site
A mí me pasa que la siguiente vez que abro el archivo, el script ya no funciona. Tengo que copiarlo, borrarlo, cerrar el archivo tras guardarlo, volver a abrir el archivo y volver a pegar el script en VBA. ¿Alguna solución?
Rated 4.5 out of 5
This comment was minimized by the moderator on the site
Hi, save the workbook as excel macro enable workbook, the vba will be work when open next time.https://cdn.extendoffice.com/images/stories/comments/sun-comment/doc-macro-enable.png
This comment was minimized by the moderator on the site
I encountered a problem with unselecting the cell and needed two charts, So I made some changes and it seems to work now.
To use it put the right Chart Name.
*Note: This one is for two charts


Private Sub worksheet_selectionchange(ByVal target As Range)
Dim chartposition As Double

chartposition = ActiveWindow.ScrollRow * ActiveCell.RowHeight
ActiveSheet.Shapes("Chart 2").Top = chartposition

ActiveSheet.ChartObjects("Chart 3").Top = chartposition + 250

End Sub
This comment was minimized by the moderator on the site
Can any one assist. when i follow theses steps


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'UpdatebyExtendoffice20161111
Dim CPos As Double
Application.ScreenUpdating = False
CPos = ActiveWindow.ScrollRow * ActiveCell.RowHeight
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveSheet.Shapes("Chart 2").Top = CPos
ActiveWindow.Visible = False
Application.ScreenUpdating = True
End Sub


i get the following err

Run-Time error '-2147024809 (80070057)':
The item with the specified name was not found

when i debug
ActiveSheet.ChartObjects("Chart 2").Activate
This comment was minimized by the moderator on the site
Hi, Christo, you need to change the chart name "Chart 2" to your chart really name in the script. You can click at the chart and see its name in the name box. See screenshot:
https://www.extendoffice.com/images/stories/comments/sun-comment/do_chart_name.png
This comment was minimized by the moderator on the site
yes thanks. after a made the question i realised my chart name was wrong
thanks

ps.
do you perhaps know how i can make the chart kept in view as i scroll without clicking where it must follow
This comment was minimized by the moderator on the site
can I use Kutools to automatically use that VBA for my charts??
This comment was minimized by the moderator on the site
Sorry, Wangnuli, Kutools has not support this so far.
This comment was minimized by the moderator on the site
Hola muchas gracias por el código, utilizando este código ¿Hay alguna manera de limitar qué tan alto en la hoja se reubicará el gráfico? por ejemplo, no quiero que se coloque encima de la fila 9. Ayuda por favor.
Rated 5 out of 5
This comment was minimized by the moderator on the site
Is there a way to limit how high up on the sheet the chart will relocate to? I don't want it to affix above row 8
This comment was minimized by the moderator on the site
Is there a similar formula that can be built for Google Sheets?
This comment was minimized by the moderator on the site
This macro did exactly what I wanted. However it created another problem that I wondered if you might have a solution to.

While this macro is active I cannot select cells for other purposes such as formatting or merging them. Click and drag, shift nor ctrl work to select a group of cells. I can only select the one cell I clicked on. I frequently want to change formatting (background, fill down a formula, etc.) The only way I have been able to do this is to delete the macro, save, make my formatting changes, paste the macro back in and save.

Is there a simpler way to do this? Perhaps:

1. (preferred) A simple keystroke that would temporarily disable the macro and then re-enable it.

2. Some code added to the macro to allow selection of a group of cells.

Dennis
This comment was minimized by the moderator on the site
This was good but I wish there was a way that it would just move with the scroll wheel, like heading lines. Also if I want to select a cell it takes two clicks. The first click moves the chart but also selects the chart so I have to click again to select the cell.
This comment was minimized by the moderator on the site
I added "activecell.select" on the last line of this code and it fixed the double clicking issue. It will automatically select the last active cell, which will be the one you clicked on to move the chart. Hope this helps.
This comment was minimized by the moderator on the site
THIS IS WHAT I NEEDED! GOLDEN!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations