Move Cell to Top/Left Position on Screen

  • Thread starter Thread starter Phil Hageman
  • Start date Start date
P

Phil Hageman

I am using variations (different chart names) of the
following code to size and position a series of imbedded
charts (in one worksheet) on screen such that they display
exactly on top of each other. This code is attached to a
forms command button in another worksheet to execute the
code. Works great. Now I need to do a similar thing
within a different worksheet, but instead of a chart, I
want to use three merged cell ranges: B1:BA1, B33:BA33,
and B65:BA65 as the point to be located at, say, Top = 10,
Left = 150. I do not want to resize, just have the cell
go to 10/150. How this should work: user clicks the
button and cell range B33 moves to 10/150, for example.
In this B33 example, I would call the macro DisplayB33.

Seems the code should be simple and straightforward, but I
can't figure out how to do it. Tried the macro generator
to no avail. Can someone help with the code?

Sub GoToMetric6()
Application.ScreenUpdating = False
Sheets("Metrics").Select
ActiveWindow.Zoom = 57
Range("A1").Select
With ActiveSheet.ChartObjects("Chart 56")
.Height = 660
.Width = 780
.Top = 10
.Left = 150
.BringToFront
End With
Application.ScreenUpdating = True
End Sub
 
Objects can have their position changed, ranges cannot.

What you could do though is change what the active window is looking at.
By using ActiveWindow.SmallScroll you can make the window show the cells
close to the position you want.

Let me know what your options are?
 
Easiest would be just to put it in the window

Sub B33Size()
Range(B33:BA33).Select
ActiveWindow.Zoom = True
Range("B33").Select
End Sub

if you want to move it down a bit

Sub B33Size()
Range(B33:BA33).Select
ActiveWindow.Zoom = True
Range("B25").Select
End Sub
 
Back
Top