Macro to get a shape to move around the Sheet so it is always on show.

[tonywatsonhelp]

Hi Everyone,

I have a sheet Names "Test1"

In it I have lots of data.

Column C Is Blank and 30 Wide.

I have a shape "Square1" that is the same width as Column C and 10 Rows Height.

The first 5 rows are fixed using freeze rows.

The Square sits in Row 10 of column C.

The problem is as I scroll down the page the shape is scrolled out of view.

I was wondering if there is a macro that can make this shape stay visiable on the page?

Please help if you can.

Thanks

Tony

 

[Jaafar Tribak]

Re: Macro to get a shap to move arond the Sheet so it is always on show.

Have you considered using a modeless userform for this instead of a shape ?

 

[My Aswer Is This]

Re: Macro to get a shap to move arond the Sheet so it is always on show.

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

This script will run when you select any cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  8/20/2018  10:47:20 AM  EDT
Shapes("Square1").Top = ActiveCell.Top
Shapes("Square1").Left = ActiveCell.Offset(, 3).Left
End Sub

 

[tonywatsonhelp]

Re: Macro to get a shap to move arond the Sheet so it is always on show.

HI The Answer is, This is a good Idea, Think I can make it work, thanks Tony

 

[My Aswer Is This]

Re: Macro to get a shap to move arond the Sheet so it is always on show.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

HI The Answer is, This is a good Idea, Think I can make it work, thanks Tony