Hello,
Currently I have a worksheet with around 50 charts one below the other and each chart is around 16 rows in height. I am in the process of creating a macro that scrolls through each active chart where it will perform some calculations and make adjustments to the chart. I have figured out a way to loop through the charts and perform these adjustments - however, I want to implement another code where the user can see the chart update in real time. Currently when I run the loop, the screen stays in one location.
I found the following code on this website:
This Code only works for a cell range that a user specifies.
How would you implement a part where the code automatically assigns the every 8th range of column C and sends it to the code above so that the screen centers at that range. So range C8 would be sent and the screen would be centered at C8, then range C16 would be centered, then C24 would be centered etc.
Thank you
Currently I have a worksheet with around 50 charts one below the other and each chart is around 16 rows in height. I am in the process of creating a macro that scrolls through each active chart where it will perform some calculations and make adjustments to the chart. I have figured out a way to loop through the charts and perform these adjustments - however, I want to implement another code where the user can see the chart update in real time. Currently when I run the loop, the screen stays in one location.
I found the following code on this website:
HTML:
http://www.cpearson.com/excel/zoom.htm
Sub CenterOnCell(OnCell As Range)
Dim VisRows As Integer
Dim VisCols As Integer
Application.ScreenUpdating = False
'
' Switch over to the OnCell's workbook and worksheet.
'
OnCell.Parent.Parent.Activate
OnCell.Parent.Activate
'
' Get the number of visible rows and columns for the active window.
'
With ActiveWindow.VisibleRange
VisRows = .Rows.Count
VisCols = .Columns.Count
End With
'
' Now, determine what cell we need to GOTO. The GOTO method will
' place that cell reference in the upper left corner of the screen,
' so that reference needs to be VisRows/2 above and VisCols/2 columns
' to the left of the cell we want to center on. Use the MAX function
' to ensure we're not trying to GOTO a cell in row <=0 or column <=0.
'
With Application
.Goto reference:=OnCell.Parent.Cells( _
.WorksheetFunction.Max(1, OnCell.Row + _
(OnCell.Rows.Count / 2) - (VisRows / 2)), _
.WorksheetFunction.Max(1, OnCell.Column + _
(OnCell.Columns.Count / 2) - _
.WorksheetFunction.RoundDown((VisCols / 2), 0))), _
scroll:=True
End With
OnCell.Select
Application.ScreenUpdating = True
End Sub
You can then call this procedure to center the screen on a cell. For example to center the screen on S50, use
CenterOnCell Range("S50")
If you pass in a range containing more than one cell, the entire range will be centered on the screen.
This Code only works for a cell range that a user specifies.
How would you implement a part where the code automatically assigns the every 8th range of column C and sends it to the code above so that the screen centers at that range. So range C8 would be sent and the screen would be centered at C8, then range C16 would be centered, then C24 would be centered etc.
Thank you