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 bill@mrexcel.com 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 bill@mrexcel.com 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: [ Ссылка ]

You can help my channel by clicking Like or commenting below: [ Ссылка ]


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 [ Ссылка ]

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.

свернуть