0

In Excel 2013, I want to center an image, horizontally, on the visible area of the screen regardless of where the mouse or cell cursors are also irrespective of what range of cells are visible.

I've got this, so far:

ActiveSheet.Shapes("Picture 1").Top = 13 'Fixed position vertically
ActiveSheet.Shapes("Picture 1").Left = (ActiveWindow.VisibleRange.Width / 2) -_
    (ActiveSheet.Shapes("Picture 1").Width / 2) 'Centered horizontally
ActiveSheet.Shapes("Picture 1").Visible = True

The first and last lines work. The middle bit doesn't.

Vertically it's where I want it, horizontally it positions the image, seemingly, at random; perhaps based on cell cursor?

Community
  • 1
  • 1
Gnerffed
  • 13
  • 3
  • well you didn't really say how off it was but my Guess is that you are not accounting for the scrollbar's width. See: http://stackoverflow.com/questions/37491755/find-width-of-excel-scroll-bar – Sorceri Oct 19 '16 at 20:52
  • It seems positioned inline with the cell cursor. – Gnerffed Oct 19 '16 at 21:07
  • 1
    From the [Shape.Left documentation](https://msdn.microsoft.com/en-us/library/office/ff197987.aspx) (my emphasis): "Returns or sets a Single value that represents the distance, in points, from the left edge of the object ***to the left edge of column A***" From the [Window.VisibleRange documentation](https://msdn.microsoft.com/en-us/library/office/ff198311.aspx) (my emphasis): "Returns a Range object that represents ***the range of cells that are visible in the window or pane.***". You need to calculate and add the distance from column A to the left-most column of the `VisibleRange`. – Comintern Oct 19 '16 at 22:58
  • So in light of Comintern's comment, your second line should look something like this: `ActiveSheet.Shapes("Picture 1").Left = ActiveWindow.VisibleRange(1, 1).Left + (ActiveWindow.VisibleRange.Width / 2) - (ActiveSheet.Shapes("Picture 1").Width / 2)` – DaveU Oct 20 '16 at 01:50
  • Thanks Comintrn & DaveU, but it's still not working (updated to Dave's code). Some more details about the sheet: We have a five column freeze pane at the left and the sheet is actually 300-ish columns wide. We commonly re-center about 20 columns on the right of that pane (visible area). Once we do, we click a button on the freeze pain that performs a few operations, and then trys to re-center this image. It seems to position the image, somehow, relative to which ever cell has focus. I don't get this, any other thoughts, guys? – Gnerffed Oct 20 '16 at 13:44

2 Answers2

0

old post but here is something that wrks exceptionally well for me... I have columns A1:T1 that I need my picture to be centered across. I am dynamically hiding some of those cells depending upon user selections. This code is placed after the code to hide those columns

ActiveSheet.Shapes("Image1").Left = ActiveSheet.Range("A1:T1").Left
ActiveSheet.Shapes("Image1").Width = ActiveSheet.Range("A1:T1").Width
CoSpringsGuy
  • 1,615
  • 1
  • 13
  • 16
-1
Private Sub Workbook_Open()
Worksheets("Munka1").Image1.Top = (ActiveWindow.VisibleRange.Height / 2) - Worksheets("Munka1").Image1.Height / 2
Worksheets("Munka1").Image1.Left = (ActiveWindow.VisibleRange.Width / 2) - Worksheets("Munka1").Image1.Width / 2
Worksheets("Munka1").Image1.Visible = True
' Put your code here

End Sub
Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
Sámuel
  • 1
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 25 '22 at 11:44