Floating picture - stationary with scroll, moves out of way

[Rod684]

Hi All,

I'm a newbie here so please be gentle.

I am attempting to insert a picture (text box is acceptable) that remains within the active worksheet view whilst scrolling, but can also move away from active cells when tabbing through the sheet. The picture is a legend and I would like to have it always within view no matter where you are on the spreadsheet.

Thanks in advance.

 

[BrianB]

Hopefully this will get you started. Goes into the code module via rightclick sheet tab/view code

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim MyPicture As Object
    Dim MyTop As Double
    Dim MyLeft As Double
    Dim BottomRightCell As Range
    '-----------------------------------------------------------
    '- bottom right cell
    With ActiveWindow.VisibleRange
        r = .Rows.Count
        c = .Columns.Count
        Set BottomRightCell = .Cells(r, c)
    End With
    '------------------------------------------------------------
    '- position picture
    Set MyPicture = ActiveSheet.Pictures(1)
    MyTop = BottomRightCell.Top - MyPicture.Height - 5
    MyLeft = BottomRightCell.Left - MyPicture.Width - 5
    With MyPicture
        .Top = MyTop
        .Left = MyLeft
    End With
End Sub

 

[Rod684]

Absolutely magnificent. Does pretty much what I have been trying to do. A little tweaking and it'll be right. I deal with Access most of the time. Until finding this forum I had no idea how pliable Excel was.

Greatly appreciated BrianB.

 

[Dude]

Great Code.
I am trying to modify it to keep a chart in a specific location as the sheet scrolls but keep getting the error "Object doesn't support property or method". I have tried using it as a procedure and call the procedure from the sheet event and it almost works. I only get a thin black line that moves to the prescribed location upon a new selection. It only works when I use the Shapes object but not the ChartObject or Charts. Could the thin black line be the chart, but the size is screwy? If so, why does the original chart still remain? Any help, This seem like it will be a cool feature if i can get it to work.

Thanks

 

[slipperyjim]

Alright, how do you make the picture scroll on its own, with this code you have to keep clicking it to keep it in view, i don't or can't use frozen panes and i need it to remain in the top right corner. i prefer a simple auto shape (rounded rectangle) as you can change the appearance more than command buttons and such. did that make sense? just like the web, as you scroll down or up, the picture stays always in view

 

[aprilnparker]

Anyway to make this code float the picture on the top right corner? (inside a freezed row panes?) Otherwise this code does exactly what I need but it places the image to the bottom right, I need it on the top right.

Thanks
April

 

[aprilnparker]

Hopefully this will get you started. Goes into the code module via rightclick sheet tab/view code

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim MyPicture As Object
    Dim MyTop As Double
    Dim MyLeft As Double
    Dim BottomRightCell As Range
    '-----------------------------------------------------------
    '- bottom right cell
    With ActiveWindow.VisibleRange
        r = .Rows.Count
        c = .Columns.Count
        Set BottomRightCell = .Cells(r, c)
    End With
    '------------------------------------------------------------
    '- position picture
    Set MyPicture = ActiveSheet.Pictures(1)
    MyTop = BottomRightCell.Top - MyPicture.Height - 5
    MyLeft = BottomRightCell.Left - MyPicture.Width - 5
    With MyPicture
        .Top = MyTop
        .Left = MyLeft
    End With
End Sub

Anyway to make this code float the picture on the top right corner? (inside a freezed row panes?) Otherwise this code does exactly what I need but it places the image to the bottom right, I need it on the top right.

Thanks
April

 

[superlav]

Anyway to make this code float the picture on the top right corner? (inside a freezed row panes?) Otherwise this code does exactly what I need but it places the image to the bottom right, I need it on the top right.

Thanks
April

Thanks BrianB for this bit of code, it's exactly what I was looking for.
Since a few people have asked about making the floating picture locate to the top right here is the slightly modified code for that.

[SIZE=2][FONT=courier new]Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim MyPicture As Object
    Dim MyTop As Double
    Dim MyLeft As Double
    Dim TopRightCell As Range
    '-----------------------------------------------------------
    '- top right cell
    With ActiveWindow.VisibleRange[COLOR=#ff0000]
        r = 1
[/COLOR]        c = .Columns.Count
        Set TopRightCell = .Cells(r, c)
    End With
    '------------------------------------------------------------
    '- position picture
    Set MyPicture = ActiveSheet.Pictures(1)
    MyTop = [COLOR=#ff0000]TopRightCell.Top + 5[/COLOR]
    MyLeft = TopRightCell.Left - MyPicture.Width - 5
    With MyPicture
        .Top = MyTop
        .Left = MyLeft
    End With
End Sub
[/FONT][/SIZE]

 

[GSlow]

Plugging in the code above was great! However, the key issue I experienced was that once I used a floating picture, I was unable to Undo and Redo any actions in excel. Is there a viable workaround for this?

 

[terryg]

Thanks BrianB for this bit of code, it's exactly what I was looking for.
Since a few people have asked about making the floating picture locate to the top right here is the slightly modified code for that.

Hi, could you write a similar code to freeze a small graph to the top-right?