Excel - Keep SmartArt in View: Episode 1366
773 views · Published 22 March 2011 · 3:38 · Indexed 1 November 2026
Channel: MrExcel.com · 2011 · Education
Microsoft Excel Tutorial: Keep SmartArt in One Place while Scrolling | Excel Tips & Tricks | MrExcel Podcast. Welcome to another episode of the MrExcel Podcast. In today's episode, we will be discussing how to keep SmartArt in one place while scrolling through a worksheet. This question was sent in by Wyatt, and I have a solution, but I'm hoping someone out there has a better one. So, if you have a better solution, please email me at [email protected] and you may be featured on a future podcast. First, let's take a look at the SmartArt in question. When selected, we can see that the name of the SmartArt is Diagram1. To begin our solution, we will need to access the code for the sheet. To do this, press Alt+F11 and select the worksheet from the left drop-down menu. Unfortunately, there is no event handler for scrolling, so we will have to use the SelectionChange event. I have already written a macro for this and did some testing. Using ActiveSheet.Shapes("Diagram1"), we can control the left and top positions of the SmartArt. However, the problem is that we don't know where to keep the SmartArt at all times. This is where a cool trick comes in. I found this trick on the MrExcel message board, thanks to a post by Andrew Paulson. By using ActiveWindow.VisibleRange, we can determine the top row and column of the visible scroller. From there, we can specify the exact row and column we want the SmartArt to appear in. Now, let's take a look at the results. As you can see, every time I select a cell, the SmartArt is automatically placed in the specified location. However, there are a few drawbacks to this solution. The boundary box around the SmartArt is quite large, so even though it is in the correct row and column, it may not be in the exact position we want. Additionally, if we use the mouse to scroll, the macro will not fire until we click on a cell. This may not be ideal for some users. So, is there a better solution? Maybe, but it's not perfect. I would love to see if anyone out there has a better solution. If you do, please email your macro code to [email protected] and you may be featured on a future podcast. Thank you for tuning in to this episode of the MrExcel Podcast. We'll see you next time for another informative netcast. Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Keeping SmartArt in One Place (00:37) Accessing Code for the Sheet (00:47) Looking for Event Handler for Scrolling (01:08) Using SelectionChange as Event Handler (01:18) Controlling SmartArt Position with Macro (01:28) Using ActiveWindow.VisibleRange to Determine Position (01:49) Setting Specific Row and Column for SmartArt (02:12) Note on SmartArt Boundary Box Size (02:25) Considerations for Mouse Users (03:05) Potential Solution for Column Width Changes (03:22) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: ActiveSheet.Shapes in VBA ActiveWindow.VisibleRange in VBA Controlling SmartArt position in Excel Excel scrolling issue Excel SmartArt Keep SmartArt in view Seeking better solution for SmartArt position SelectionChange event in Excel VBA SmartArt movement on column width change SmartArt position in Excel Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153046/ Wyatt asks if there is a way to have SmartArt float above a worksheet and always stay in view. Today, in Episode #1366, Bill shows us how to create a short macro to solve the problem, although not perfectly. ...Learn Excel 97-2007 from MrExcel.
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920