Macro Help

[HunterS93]

I am using a Macro that keeps a image locked up in the Top Left corner of the worksheet when you scroll down. I was wondering if it was possible to have the image sit in the Top Right corner of the worksheet. Here is the Macro that I am using:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myShape As Shape

Set myShape = Me.Shapes("putthenamehere")

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
myShape.Top = .Top
myShape.Left = .Left
End With

End Sub

The help is greatly appreciated.

 

[chicagocomputerclasses]

Try this

[COLOR=#333333]Option Explicit[/COLOR]
[COLOR=#333333]Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/COLOR]

[COLOR=#333333]Dim myShape As Shape[/COLOR]

[COLOR=#333333]Set myShape = Me.Shapes("putthenamehere")[/COLOR]

[COLOR=#333333]With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)[/COLOR]
[COLOR=#333333]myShape.Top = 0[/COLOR]
[COLOR=#333333]myShape.Left = [/COLOR]Application.ActiveWindow.ActivePane.VisibleRange.Width - [COLOR=#333333]myShape[/COLOR].Width
[COLOR=#333333]End With[/COLOR]

[COLOR=#333333]End Sub[/COLOR]

 

[chicagocomputerclasses]

You will probably need to add visible range left to it too.

So just change this line only

myShape.Left = Application.ActiveWindow.VisibleRange.Width + Application.ActiveWindow.VisibleRange.Left - myShape.Width

You may need to deduct a little bit more to adjust for the scrollbar.

 

[HunterS93]

Thank you for the help it moved the picture to the right corner but I would still like it to stay in the right corner and move as I scroll down. Would you know what to do to the macro to make that work?

 

[Eric W]

There is not an event for ScrollChange, so you can't trigger any code when you scroll. I have seen some routines that actually have hooks into the Windows OS, that monitors when Windows does the scrolling, but they are complicated and prone to crashing.

Best suggestion would be to make row 1 tall enough to hold the picture. Then put the picture where you want it on the right, go to the View tab and click on Freeze Panes > Freeze Top Row.