Floating Picture

[altbrooks]

Please help me if you can. I recently found an interesting post on how to have a picture float as you scroll a worksheet. The original ideas was posted on another forum at http://www.mrexcel.com/forum/excel-questions/181552-floating-picture-stationary-scroll-moves-out-way.html.

But there are 2 problems I have with the code below:

1. I need the code to be generic (i.e. a function) so that I can call execute it for ALL worksheets.
2. As the worksheet scrolls, you can see the image move.

I understand most of this code, but the bit I don't understand is the Subroutine Declaration "Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)". I am not sure how to turn this into a generic function call.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  'Disable Screen Updating.
  Application.ScreenUpdating = False

  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 - MyPicture1
  '------------------------------------------------------------
  Set MyPicture = ActiveSheet.Pictures("MyPicture1")
  'MyTop = BottomRightCell.Top - MyPicture.Height - 5
  MyTop = 5 'Force to top of page.
  MyLeft = BottomRightCell.Left - MyPicture.Width + 10

  With MyPicture
  .Top = MyTop
  .Left = MyLeft
  End With

  '------------------------------------------------------------
  ' position picture - MyPicture2
  '------------------------------------------------------------
  Set MyPicture = ActiveSheet.Pictures("MyPicture2")
  MyTop = 70 'Force to top of page.
  MyLeft = BottomRightCell.Left - MyPicture.Width + 5

  With MyPicture
  .Top = MyTop
  .Left = MyLeft
  End With

  'Enable Screen Updating.
  Application.ScreenUpdating = True
End Sub

 

[KenU]

@altbrooks,

I'm afraid that you cannot make it fully "generic" in the sense that it relies on an event that must be captured at the worksheet level. The "Worksheet_SelectionChange" represents an event which triggers execution of the code. Although portions of the code could be moved to a code module, each worksheet for which you want to execute this would have to have the "Worksheet_SelectionChange" event within its own sheet module (which in turn would call the other routine). Additional edits may also be necessary.

However, depending on what you are trying to achieve, there may be another alternative. You might consider placing the desired figure, chart, etc. within a particular row or column which can be frozen to allow it to continually be displayed. Of course, this may present some other inconveniences, but would certainly have the advantage in terms of performance as a change event triggered by every selection change on each sheet could quickly become overwhelming.

Hope that helps.

Regards,
Ken